Exploring PostgreSQL metadata...

It seems that some people fancy to talk about metadata. It's perceived either as a difficult task that every hacker has to learn or as some obscure magic formula. True, it is a key piece for an application to run, but there is no magic, voodoo or anything.

A word of caution!

You can effectively damage a database. If you want to take a look, launch a throw-away PostgreSQL server with the following commands in a console:

$ sudo su - postgres -c "initdb -D /tmp/throwme.db -A trust && postgres -D /tmp/throwme.db -p 54321"

And connect to it using:

$ psql -U postgres -p 54321

Now, what is metadata?

Metadata is just the data about the data. I know that larger explanations about it are icky, so I prefer to be picky on word games before you become freaky about it.

Remember that quote "The greatest trick the Devil ever pulled was convincing the World that he didn't exist" by Kevin Spacey? PostgreSQL did the same, so metadata on it is just data that can be queried using SQL.

Let's say I want to list all the extensions for a certain database:


That gives me a list of extensions that are installed in our current database. I can do that using SQL too:

SELECT * FROM pg_catalog.pg_extension;  

That's neat. Once you create an extension, you can perform that query again, reading the results.

How do we handle it now?

We are encouraged to query metadata on PostgreSQL using metacommands. There is a list of those in the PostgreSQL documentation site, that I recommend you to read to have an overview of what you can do with it. The most common ones are \l for a list of databases, \dt to get a list of the tables in the current database, \dn to list available namespaces, and \ds to list available sequences.

What's the magic trick you want to show us?

It's super simple. If you want to explore a bit more, we can use the psql command line tool with the -E modifier. It shows what queries are executed under the hood when we execute any of the metacommands.

If we repeat the previous example with extensions, we will get the following:

demo=# \dx  
*** QUERY ***
SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"  
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass  

                            List of installed extensions
   Name    | Version |   Schema   |                   Description                   
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.0     | public     | generate universally unique identifiers (UUIDs)
(2 rows)

Should I ditch the metacommands and use SQL queries?

Hell no! Metacommands are there for a reason: hidding complexity for us. But it is a good practice to know what is under the hood because it helps us understanding PostgreSQL a bit better.

That's all I got. See you!