Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2011

    Unanswered: How to list sequences and the columns by SQL

    I need to find full list of the sequences + the table.column_names.

    I know how to find sequences:
    SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

    I know how to find table + columns:
    SELECT c.relname,a.attnum, a.attname AS field, t.typname AS type,
    a.attlen AS length, a.atttypmod AS length_var,
    a.attnotnull AS not_null, a.atthasdef as has_default
    FROM pg_class c, pg_attribute a, pg_type t
    WHERE (1=1)
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
    ORDER BY a.attnum;

    But how to relate them?

    Thanks in advance!

  2. #2
    Join Date
    Mar 2011
    I use this query to show me tonnes of stuff including the default values of columns which include sequence names:

    select x.nspname || '.' || x.relname as "Table", x.attnum as "#", x.attname as "Column", x."Type", case x.attnotnull when true then 'NOT NULL' else '' end as "NULL?"
    , r.conname as "Constraint", r.contype as "C", r.consrc, fn.nspname || '.' || f.relname as "F Key", d.adsrc as "Default"
    from (
    SELECT c.oid, a.attrelid, a.attnum, n.nspname, c.relname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type", a.attnotnull
    FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c
    WHERE a.attnum > 0
    AND NOT a.attisdropped
    AND a.attrelid = c.oid
    and c.relkind not in ('S','v')
    and c.relnamespace = n.oid
    and n.nspname not in ('pg_catalog','pg_toast','information_schema')
    ) x
    left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
    left join pg_constraint r on r.conrelid = x.oid and r.conkey[1] = x.attnum
    left join pg_class f on r.confrelid = f.oid
    left join pg_namespace fn on f.relnamespace = fn.oid
    where x.relname = 'TABLE NAME HERE'
    order by 1,2

  3. #3
    Join Date
    Jun 2011
    Unfortunately it shows not all sequences. Only few of them

  4. #4
    Join Date
    Mar 2011
    It should list any sequences attached to the tables? If you also want sequences that are not linked in any way the catalog knows about, you're going to have a hard time

  5. #5
    Join Date
    Jun 2011
    What do you mean "attached"? I have table and the sequence and nextval is working. Can it be not attached?

  6. #6
    Join Date
    Mar 2011
    Sorry, by attached I mean when you use a serial column and the sequence is automatically used as the default for it, or you explicitly use a sequence as a columns default value.

    If you create a table then a sequence and the sql calls the sequence to get a value, then inserts that value into the table - PostgreSQL knows nothing about the relationship between the two.

  7. #7
    Join Date
    Jun 2011
    ok, thanks a lot. Got it

  8. #8
    Join Date
    Jun 2011
    I know that my solution is very specific, but still it might help somebody. Almost all sequences in my database are named table_name_seq, so I did following:

    SELECT a.table_name AS table_name,
    a.relname AS sequence_name
    WHEN UPPER(c.relname) LIKE E'%\\_ID\\_%' -- for the sequence named table_name_ID_seq
    THEN SUBSTRING(c.relname from 1 for char_length(c.relname) - 7)
    WHEN UPPER(c.relname) LIKE E'%\\_KRKEY\\_%' -- for sequence named table_name_krkey_seq
    THEN SUBSTRING(c.relname from 1 for char_length(c.relname) - 10)
    ELSE SUBSTRING(c.relname from 1 for char_length(c.relname) - 4) -- all other sequences
    END AS table_name,
    FROM pg_class c WHERE c.relkind = 'S'
    ) a
    JOIN pg_class AS rl -- checks that such table exists
    ON rl.relname = a.table_name

  9. #9
    Join Date
    Jun 2004
    Arizona, USA
    The information schema may have a lot of what you need, also. If it does, it is preferable to accessing the system tables, as it is portable to other database vendors as well...
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  10. #10
    Join Date
    Jun 2011
    as 'someidiot' (sounds nice ) stated previously that there is no connection of sequence to actual table if it is not stated as default value in DDL

  11. #11
    Join Date
    May 2008
    One can "assign" a sequence to a table column after the fact by using the OWNED BY clause in your CREATE SEQUENCE statement.

    PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: CREATE SEQUENCE

    Whether or not this relationship can be identified/retrieved from the system metadata, however, I have no idea.

  12. #12
    Join Date
    Nov 2003
    Provided Answers: 8
    This statement lists the table and column that is associated with each sequence:
    SELECT t.relname as related_table, 
           a.attname as related_column,
           s.relname as sequence_name
    FROM pg_class s 
      JOIN pg_depend d ON d.objid = s.oid 
      JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid 
      JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
      JOIN pg_namespace n ON n.oid = s.relnamespace 
    WHERE s.relkind     = 'S' 
      AND n.nspname     = 'public'

Posting Permissions

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