PostgreSQL 12 - List sequences that do not match their related table name

Question

I have a database with plenty of tables.

I want to tidy up relations that do not fit namewise anymore, due to name changes of the tables.

I was able to fix the constraints, but I am not able to put the lines together to list the sequences and the related columns. As pgAdmin shows under dependencies the column a sequence is connected to, it should be possible to create a SELECT to show sequences and their related column.

Answer 1

Try this:

SELECT a.attrelid::regclass AS table_name,
       a.attname AS column_name,
       pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) AS sequence_name
FROM pg_attribute AS a
   JOIN pg_class AS t ON a.attrelid = t.oid
WHERE t.relkind IN ('r', 'P')
  AND NOT a.attisdropped
  AND pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) IS NOT NULL;