Thread: ownership of sequences by tables in pg_dumps?
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? Andrew
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.klaver@gmail.com