Get table and column "owning" a sequence

Question

I can run the following line:

ALTER SEQUENCE seqName OWNED BY table.id;

How can I get the 'owner' set by OWNED BY for a sequence (in this case: table.id)?

Answer 1

You may use following query:

select s.relname as seq, n.nspname as sch, t.relname as tab, a.attname as col
from pg_class s
  join pg_depend d on d.objid=s.oid and d.classid='pg_class'::regclass and d.refclassid='pg_class'::regclass
  join pg_class t on t.oid=d.refobjid
  join pg_namespace n on n.oid=t.relnamespace
  join pg_attribute a on a.attrelid=t.oid and a.attnum=d.refobjsubid
where s.relkind='S' and d.deptype='a'

It returns all sequences with owner information. Just filter them in WHERE clause and that's it.

Answer 2

Get the "owning" table and column

ALTER SEQUENCE seqName OWNED BY table.id;

Your ALTER SEQUENCE statement causes an entry in the system catalog pg_depend with the dependency type (deptype) 'a' and a refobjsubid greater than 0, pointing to the attribute number (attnum) in pg_attribute. With that knowledge you can devise a simple query:

SELECT d.refobjid::regclass, a.attname
FROM   pg_depend    d
JOIN   pg_attribute a ON a.attrelid = d.refobjid
                     AND a.attnum   = d.refobjsubid
WHERE  d.objid = 'public."seqName"'::regclass  -- your sequence here
AND    d.refobjsubid > 0
AND    d.classid = 'pg_class'::regclass;

• Double quotes ("") are only needed for otherwise illegal names (mixed case, reserved words, ...).

• No need to assert that refclassid is of type regclass since the join to pg_attribute does that automatically.
  No need to assert that the sequence is a sequence since schema-qualified object names are unique across the database.
  No need to join to pg_class or pg_namespace at all.

• The schema name is only needed to disambiguate or if it's not in the search_path.
  The same table name (or sequence name for that matter) can be used in multiple schemas. A cast to the object identifier type regclass observes the current search_path to pick the best match if you omit the schema qualification. If the table is not visible, you get an error message.

• What's more, a regclass type is displayed as text to the user automatically. (If not, cast to text.) The schema-name is prepended automatically where necessary to be unambiguous in your session.

Get the actual "owner" (the role)

To get the role owning a specific sequence, as requested:

SELECT c.relname, u.usename 
FROM   pg_class c
JOIN   pg_user  u ON u.usesysid  = c.relowner
WHERE  c.oid = '"seqName"'::regclass;  -- your sequence here

Answer 3

I was able to list the table and corresponding sequence for a particular column using the following SQL statement:

SELECT   table_schema
       , table_name 
       , column_name
       , LTRIM(RTRIM(RTRIM(column_default, '::regclass)'),''''),'nextval(''') AS SEQUENCE_NAME
FROM information_schema.columns
WHERE  column_default like '%nextval%'; 

Answer 4

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 5

I use that query for get all queries.

Change CTE to filter the result

WITH table_with_sequence as (
SELECT
    d.refobjid::regclass::text tablename,
    c.relname::text sequencename,
    np.nspname::text schemaname,
    a.attname::text attname,
    u.usename::text
FROM
    pg_depend d
    INNER JOIN pg_class c ON c.oid = d.objid
        AND c.relkind = 'S'
    INNER JOIN pg_namespace np ON np.oid = c.relnamespace
        AND (np.nspname NOT LIKE 'pg_%'
            AND np.nspname != 'information_schema')
        INNER JOIN pg_user u ON u.usesysid = c.relowner
        INNER JOIN pg_attribute a ON a.attrelid = d.refobjid
            AND a.attnum = d.refobjsubid
)

SELECT
    'ALTER SEQUENCE '|| QUOTE_LITERAL(QUOTE_IDENT(schemaname) || '.' || QUOTE_IDENT(sequencename)) ||' OWNED BY ' || tablename || '.' || QUOTE_IDENT(attname)
FROM table_with_sequence