Re: ownership of sequences by tables in pg_dumps?

On Thursday, January 12, 2012 6:53:03 pm Andrew Hammond wrote:
> Where foo is a number of different tables, I'm calling
>
> pg_dump --format=custom --compress=9 --no-password
> --file=public.foo.pgdump --table=public.foo --schema-only my_database
>
> When I check the contents of that dump using
>
> pg_restore -l public.foo.pgdump
>
> in some cases it includes the foo_id_seq object and in others it does
> not. How does pg_dump decide if an sequence is associated with a given
> table or not?

It depends how the sequence was created. If the sequence was created by using
the serial type in CREATE TABLE then the dependency between the table and
sequence is automatically set up and the sequence is dumped with the table. In
newer versions of Postgres you can do this without using the serial type. See
the OWNED BY clause in the commands below:

http://www.postgresql.org/docs/9.0/interactive/sql-createsequence.html
http://www.postgresql.org/docs/9.0/interactive/sql-altersequence.html

Pretty sure the dependency is tracked in the pg_depend system catalog, just not
sure how to pull it out.

>
> Andrew

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com