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(
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).
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';
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".