Re: derive the sequence name of a column

On Thu, 30 Oct 2003, Mel Jamero wrote:

> Thank for the reply Bruno but I need more. =)
>
> Sorry, I have to send this again because I haven't figured out how to
> solve this.
>
> Can anyone please tell me exactly how the name of a sequence a field is
> using (manually created or generated by a serial) could be derived
> programmatically (using libpq or through SQL)?
>
> Thus:
>
> CREATE SEQUENCE an_unknown_sequence_name;
> CREATE TABLE test (
>   test_id   integer default nextval('an_unknown_sequence_name'),
>   useless_redundant_test_id serial
> );
>
> How do I programmatically extract that column 'test_id' in table 'test'
> is using 'an_unknown_sequence_name'

I'd suggest looking in pg_attrdef.

Something like:

select pg_attrdef.* from pg_attrdef, pg_namespace, pg_class, pg_attribute
where pg_namespace.nspname='public' and
pg_class.relnamespace=pg_namespace.oid and pg_class.relname='test' and
pg_attribute.attrelid=pg_class.oid and pg_attribute.attname='test_id' and
pg_attrdef.adrelid=pg_class.oid and pg_attrdef.adnum=pg_attribute.attnum;

(filling in the schema, table name and column name for the constants).