Postgres list sequence dependencies

Question

How to get a list of all columns (and tables) using a sequence in postgres? I am looking for a result that looks something like the dependencies report from pgAdmin 4 shown below.

Thank you.

(in pgAdmin 4 when clicking the sequence object and then the dependency tab).
pgAdmin dependencies report

In `pg_stat_activity` table you can see all queries issued by PGAdmin - the GUI you see is a result of PGAdmin querying system tables for you, so you can inspect or [record](https://stackoverflow.com/a/722236/5298879) and re-use whatever PGAdmin does, the exact way it does it, without having to reverse-engineer it. — Zegarek, Nov 24 '21 at 18:59
@zegarek I tried capturing the log, but it didn't appear there. — smg77, Nov 24 '21 at 20:39
`pg_stat_activity` table is not a log table but a live view, so if you're trying to do something in PGAdmin, then view that activity in there also through PGAdmin, then the query might no longer be there. You can try to "catch" it, viewing the table in another tool, like plain psql, at the same time/right after performing the operation you want to capture in PGAdmin, and only that operation. But the most reliable method is the recording approach I linked in previous comment, that captures and saves everything - although you might need to filter it a bit. — Zegarek, Nov 24 '21 at 21:10

score 0 · Accepted Answer · answered Nov 24 '21 at 16:08

0

To find the table and column that belongs to a sequence s, run

SELECT d.objid::regclass AS sequence_name,
       a.attrelid::regclass AS table_name,
       a.attname AS column_name
FROM pg_depend AS d
   JOIN pg_attribute AS a
      ON d.refobjid = a.attrelid
        AND d.refobjsubid = a.attnum
WHERE d.objid = 's'::regclass
  AND d.refclassid = 'pg_class'::regclass;

answered Nov 24 '21 at 16:08

Laurenz Albe

209,280
17
206
263

Thanks a lot, that really got me close to what I needed, what worked for me is as follows: `SELECT d.refobjid::regclass as sequence_name, def.adrelid::regclass as table_name, attr.attname as col_name From pg_depend d JOIN pg_attrdef def on d.objid = def.oid JOIN pg_attribute attr on def.adnum = attr.attnum AND def.adrelid = attr.attrelid WHERE d.refobjid = 's'::regclass` – smg77 Nov 24 '21 at 18:17

Postgres list sequence dependencies

1 Answers1