Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: how to match column to sequence via system catalog

    Apologies in advance, I'm a *very* new PostgreSQL user so if this is obvious to you, it's not to me...

    I can't seem to work out how I query in the system catalog tables to find a sequence related to a table's column.
    For example, if I
    CREATE TABLE tab1(col1 SERIAL)

    pg_dump then shows:
    CREATE TABLE tab1(
    col1 INTEGER
    CREATE SEQUENCE tab1_col1_seq
    START WITH ! [...] etc
    ALTER SEQUENCE tab1_col1_seq OWNED BYtab1.col1;

    So this shows it's worked.

    What query can I run against the system catalog to show that tab2_col1_seq is owned by tab2.col1? The OID for pg_class.relname 'tab1' and the OID for pg_class.relname 'tab1_col1_seq' are different (obviously) but I can't find anything in pg_attribute, pg_type and so on that I can use to make a logical connection between them...
    (This is related to converting a 500-table Informix database with SERIALs to PostgreSQL and our programs subsequently needing to be able to identify the SERIAL columns for a given table by querying the system catalog).

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    This should do it:
    SELECT seq.relname as sequence_name,  
           n.nspname as sequence_schema, 
           tab.relname as related_table,  
           col.attname as related_column 
    FROM pg_class seq  
      JOIN pg_depend d ON d.objid = seq.oid  
      JOIN pg_class tab ON d.objid = seq.oid AND d.refobjid = tab.oid  
      JOIN pg_attribute col ON (d.refobjid, d.refobjsubid) = (col.attrelid, col.attnum) 
      JOIN pg_namespace n ON n.oid = seq.relnamespace  
    WHERE seq.relname = 'tab1_col1_seq';

  3. #3
    Join Date
    Feb 2012
    Shammat, thanks, I'll give it a try - finding my way around an unfamiliar system catalog is a little daunting to say the least; I didn't think to try using pg_depend!
    I'm at least helped by the fact that the serial col is always column 1 (by design) in our source (informix) database; and by Postgres always naming a sequence "<tabname>_<colname>_seq".

    Appreciate the speedy and informative response.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts