How to get the sequence name for a serial column

As far as I can tell, this query should show the expression for the new value of a serial column:

select d.adsrc
from (
 SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname
 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
where x.relname = 'table_name' and x.nspname = 'schema_name' and x.attname = 'column_name'
;

It works most of the time, but if I rename the sequence, the new name is not reflected in the result of the query - it continues to show the original name of the sequence. Any ideas why?

In the comments, a_horse_with_no_name said:


Are you using Greenplum or Postgres? In Postgres you can just use pg_get_serial_sequence

You can find the docs here.


pg_get_serial_sequence returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. The first input parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter is potentially a schema and table, it is not treated as a double-quoted identifier, meaning it is lower cased by default, while the second parameter, being just a column name, is treated as double-quoted and has its case preserved. The function returns a value suitably formatted for passing to sequence functions (see Section 9.16). This association can be modified or removed with ALTER SEQUENCE OWNED BY. (The function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it's typically used with serial or bigserial columns.)

StackExchange Database Administrators Q#167889, answer score: 7