How do you view new sequence ownership information in Postgres after using ALTER SEQUENCE?

Question

I'm using "ALTER SEQUENCE sequence OWNED BY table.column" to change sequence association in Postgres. Is there a way to view this new information using \ds or something similar? \ds still shows the table owner as the sequence owner.

Answer 1

SELECT c.relname,u.usename 
  FROM pg_class c, pg_user u
 WHERE c.relowner = u.usesysid and c.relkind = 'S'
   AND relnamespace IN (
                        SELECT oid
                          FROM pg_namespace
                         WHERE nspname NOT LIKE 'pg_%'
                           AND nspname != 'information_schema'
                        );

Answer 2

OWNED BY table.column changes the associated column; OWNER TO newowner changes the role which owns the sequence. The owner role is what is displayed in \ds. I don't know a psql command to see the linked column of a sequence. OWNED BY is primary used to cascade a delete of the associated sequence when the referent column is removed.

You can get visibility into the owned by column by constructing a query against the system catalog tables pg_class, pg_depend, and pg_attribute. I'm not sure of all the exact semantics of pg_depend, but this query should show you a sequence's column dependency:

select tab.relname as tabname, attr.attname as column
from pg_class as seq
join pg_depend as dep on (seq.relfilenode = dep.objid)
join pg_class as tab on (dep.refobjid = tab.relfilenode)
join pg_attribute as attr on (attr.attnum = dep.refobjsubid and attr.attrelid = dep.refobjid)
where seq.relname = 'sequence';