how to match column to sequence via system catalog

malc_p · #1 (**permalink**) 02-06-12, 12:17

Hi
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).
Thanks
Malc

shammat · #2 (**permalink**) 02-06-12, 12:40

This should do it:

Code:

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

malc_p · #3 (**permalink**) 02-06-12, 12:47

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.

Malc

Internet Services

Web Development

Digital Marketing

Consumer Tech