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
AND a.attnum > 0
AND a.attrelid = c.oid
AND a.atttypid = t.oid
ORDER BY a.attnum;
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"
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')
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 = 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
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'
JOIN pg_class AS rl -- checks that such table exists
ON rl.relname = a.table_name
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
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'