Re: pg_dump not appending sequence to default values - Mailing list pgsql-admin

Andy Shellam <andy-lists@networkmail.eu> writes:
> When I pg_dump the schema, the resulting SQL is:

> ...
> CREATE SCHEMA product;
> ...
> SET search_path = product, pg_catalog;
> ...
> CREATE SEQUENCE tax_id
>     INCREMENT BY 1
>     NO MAXVALUE
>     NO MINVALUE
>     CACHE 1;
> ...
> CREATE TABLE tax (
>     id smallint DEFAULT nextval('tax_id'::regclass) NOT NULL
> );

> Notice how "product.tax_id" has now become just "tax_id" so when I now
> insert a record into that table, it complains "relation 'tax_id' does
> not exist" and I have to manually edit it.

The reason it's printed as just 'tax_id' is that that relation should be
first in the search_path at this point.  Are you manually editing the
dump in some way that screws that up?

The underlying representation of regclass is an OID, not text, so
once the default expression is created it's not subject to search path
issues.  It's not clear what you did to break it, but your description
of the problem is based on faulty assumptions.

            regards, tom lane