Bringing psql’s \d to your web browser

Different database systems provide different ways to list or describe the things they hold. For instance, to find a particular table and column in MySQL, you run SHOW TABLES followed by SHOW COLUMNS FROM my_table. In SQLite, you do .tables and then .schema my_table. And in Postgres, the commands are\d (for describe) followed by \d mytable.

When I got access to my first Neon Postgres database, almost the first thing I did was go to the web-based SQL Editor and type \d. I was a little sad when the response I got back was: ERROR: syntax error at or near "\" (SQLSTATE 42601).

It turns out that \d, \l, and their relatives are a psql feature. That is, these introspection commands are found in the C code that implements the psql client, not the C code that implements the Postgres server. Each time you issue one of these commands in psql, you’re running a little local routine that constructs one or more SQL queries, sends them to execute on the server one by one, and stitches the results together into a nice little table.

You may already know that you can see those underlying SQL queries by running psql with the -E or --echo-hidden option. \d issues only one query, but try \d pg_a* and you’ll see dozens. This is a useful way to explore some of Postgres’ internals. It was helpful, for example, in putting together the queries Zapatos uses to fetch type information from your database.

Leave a Comment