From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | zedaardv(at)drizzle(dot)com |
Subject: | BUG #17720: pg_dump creates a dump with primary key that cannot be restored, when specifying 'using index ...' |
Date: | 2022-12-14 09:07:45 |
Message-ID: | 17720-dab8ee0fa85d316d@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17720
Logged by: reiner peterke
Email address: zedaardv(at)drizzle(dot)com
PostgreSQL version: 15.1
Operating system: openSUSE Leap 15.4
Description:
I have a table
create table hamster(under integer, over text);
I create a unique index on the under column with nulls not distinct
create unique index uq_not_distinct on hamster (under) nulls not distinct;
i now create a primary key using the unique index
alter table hamster add constraint pk_hamster primary key using index
uq_not_distinct;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index
"uq_not_distinct" to "pk_hamster"
table looks good
\d hamster
Table "moon.hamster"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
under | integer | | not null |
over | text | | |
Indexes:
"pk_hamster" PRIMARY KEY, btree (under) NULLS NOT DISTINCT
Do a pg_dump of the database.
the dump creates the code for a primary key that cannot be restored
pg_dump -p 5632 -Of tranquility.sql -d tranquility
on restore, I get the following error
psql:tranquility.sql:90: ERROR: syntax error at or near "NULLS"
LINE 2: ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT...
in the dump itself the create constraint command is
ALTER TABLE ONLY moon.hamster
ADD CONSTRAINT pk_hamster PRIMARY KEY NULLS NOT DISTINCT (under);
which does not work with the NULLS NOT DISTINCT string
From | Date | Subject | |
---|---|---|---|
Next Message | Etsuro Fujita | 2022-12-14 10:02:58 | Re: BUG #17713: Assert with postgres_fdw in v12 |
Previous Message | okijhhyu | 2022-12-14 07:20:21 | Sort bug |