On Friday 07 January 2011 7:46:31 am gvim wrote:
> PostgreSQL 9.0.1/pgAdminIII 1.12.1
>
> I want to copy selected tables from one database to another and maintain
> the sequences which I originally setup with:
>
> CREATE SEQUENCE venues_id_seq START WITH 1122;
> ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
>
> ... along with their current values, which have been augmented since the
> database was setup. When I backup via pgAdminIII the sequences are not even
> included. I also can't find anything in:
>
> man pg_dump
>
> ... which specifies sequences.
>
> gvim
Details below. When you dump a specific table using -t it will not
automatically dump dependent objects. -t can be used to dump a sequence because
they are just a special type of table.
From:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
"
-t table
--table=table
Dump only tables (or views or sequences) matching table. Multiple tables can
be selected by writing multiple -t switches. Also, the table parameter is
interpreted as a pattern according to the same rules used by psql's \d commands
(see Patterns), so multiple tables can also be selected by writing wildcard
characters in the pattern. When using wildcards, be careful to quote the
pattern if needed to prevent the shell from expanding the wildcards.
The -n and -N switches have no effect when -t is used, because tables
selected by -t will be dumped regardless of those switches, and non-table
objects will not be dumped.
Note: When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon. Therefore, there
is no guarantee that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
Note: The behavior of the -t switch is not entirely upward compatible
with pre-8.2 PostgreSQL versions. Formerly, writing -t tab would dump all
tables named tab, but now it just dumps whichever one is visible in your
default search path. To get the old behavior you can write -t '*.tab'. Also,
you must write something like -t sch.tab to select a table in a particular
schema, rather than the old locution of -n sch -t tab. "
--
Adrian Klaver
adrian.klaver@gmail.com