0

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.

JBecker
  • 124
  • 7

1 Answers1

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;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks for your code. This is working great. I mostly understood it, just was wondering what the "::" is doing. Can you clarify? – JBecker Nov 10 '20 at 07:33
  • It is a sype cast. `value::type` is synonymous to the SQL standard `CAST (value AS type)`. – Laurenz Albe Nov 10 '20 at 08:29