CODEWITHSUNDEEP
postgresqldatabase-design
johnlemon
johnlemon

Reputation: 21499

Get table and column "owning" a sequence

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)?

Upvotes: 14

Views: 14527

Answers (6)

Amr zakaria
Amr zakaria

Reputation: 11

This is my query to get all information i need: SELECT t1.table_schema, t1.TABLE_NAME, t1.COLUMN_NAME, t1.column_default, t2.schemaname AS SCHEMA, t2.sequencename AS SEQUENCE, t2.LAST_VALUE , t2.schemaname FROM information_schema.COLUMNS t1, pg_sequences t2 WHERE t1.table_schema = t2.schemaname AND POSITION(t2.sequencename IN t1.column_default) >0 AND t1.column_default ILIKE'%nextval%' AND t2.LAST_VALUE is not null AND t2.schemaname not in ('tiger');

Upvotes: 0

GeoStoneMarten
GeoStoneMarten

Reputation: 583

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658082

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

Upvotes: 13

Sashank Bhogu
Sashank Bhogu

Reputation: 659

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%';

Upvotes: 4

Yuri Levinsky
Yuri Levinsky

Reputation: 1595

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'
    );

Upvotes: 2

alexius
alexius

Reputation: 2576

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.

Upvotes: 18

Related Questions