So I had this pretty big PostgreSQL table, and I wanted to know how many columns it had. Should be simple enough, right? Remember about using psql to show how some comands are converted to SQL?
Well it didn’t really help me that much. It turns out that a simple \d table_name translates to nine (!) pretty low-level queries. Happily, I learned about information_schema (via this answer). It turns out that this is not even a Postgres-specific feature – it is defined in the SQL standard. It contains a lot of potentially useful information about the database – and getting the number of columns in some table is really easy with it:
select count(*) from information_schema.columns where table_name='your_table_name';
By the way, the reason I needed this was also a bit interesting. Apparently, you cannot insert too many rows at once in PostgreSQL - I found (by experimenting) that the number of values you can insert with a single insert statement is about 2^16, i.e., 65536. (I’d love to see that information in the docs – I couldn’t find it, if you know where it is, please give me a link!) Thus, to find how many rows I can insert at once, I can divide 2^16 by the number of columns in my table. If you need to insert more, you need to split them up into smaller chunks.