List PostgreSQL tables using extensions
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
catalog to find the data types provided by installed extensions, and then
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!