Postgres has extensions, and that’s awesome! Of course as the author of CREATE EXTENSION I’m a little biased… just remember that the ability to extend Postgres is way more than just this command. The whole database system has been design from the ground up to allow for extensibility. Parts of the design is to be found in the way you can register new objects at runtime: functions of course, and also data types, operators, index support structures such as operator classes and families, even index access methods!

Today’s article shows a query that you can use to list those tables in your schemas that are using a data type which is provided by an extension.

I came up with the following query, that scans through our pg_depend catalog to find the data types provided by installed extensions, and then scans through pg_depend again to find tables that have attributes depending on those data types.

with etypes as ( select classid::regclass, objid, deptype, e.extname from pg_depend join pg_extension e on refclassid = 'pg_extension' ::regclass and refobjid = e.oid where classid = 'pg_type' ::regclass ) select etypes.extname, etypes.objid::regtype as type , n.nspname as schema , c .relname as table , attname as column from pg_depend join etypes on etypes.classid = pg_depend.refclassid and etypes.objid = pg_depend.refobjid join pg_class c on c .oid = pg_depend.objid join pg_namespace n on n.oid = c .relnamespace join pg_attribute attr on attr.attrelid = pg_depend.objid and attr.attnum = pg_depend.objsubid where pg_depend.classid = 'pg_class' ::regclass;

Here, the result is quite simple:

extname │ type │ schema │ table │ column ─────────┼───────────┼─────────┼─────────┼────────── ip4r │ ipaddress │ tweet │ visitor │ ipaddr ip4r │ ip4r │ geolite │ blocks │ iprange hll │ hll │ tweet │ uniques │ visitors hstore │ hstore │ moma │ audit │ after hstore │ hstore │ moma │ audit │ before (5 rows)

The reason I’m working on that is to provide the readers of my book The Art of PostgreSQL with an easier way to restore the database used throughout the book. In a previous version of it, I tried to be smarter that I should and the result isn’t easy enough to use… I got feedback about that, so let’s try and improve things!