On Tue, Mar 5, 2019 at 11:35 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 15670
> Logged by: Michael Binder
> Email address: michael@mibi.io
> PostgreSQL version: 11.2
> Operating system: Debian 9.8
> Description:
>
> Hi,
>
> I don't know if this is the expected behavior but when I execute this
> script:
>
> create table test1 (
> id serial primary key,
> name text
> );
>
> create table test2 (
> id serial primary key
> );
>
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);
> alter table test2 add column if not exists test1_fk integer not null
> references test1(id);
I think the foreign key constraint creation (references test1(id)
part) is executed independently of add column part, so the latter's
no-op semantics due to the "if not exists" clause doesn't apply to
foreign key creation. You would get duplicate constraints even if you
had instead done the following:
alter table test2 add column if not exists test1_fk integer not null;
alter table test2 add foreign key (test1_fk) references test1(id);
alter table test2 add foreign key (test1_fk) references test1(id);
alter table test2 add foreign key (test1_fk) references test1(id);
\d test2
Table "public.test2"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('test2_id_seq'::regclass)
test1_fk | integer | | not null |
Indexes:
"test2_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"test2_test1_fk_fkey" FOREIGN KEY (test1_fk) REFERENCES test1(id)
"test2_test1_fk_fkey1" FOREIGN KEY (test1_fk) REFERENCES test1(id)
"test2_test1_fk_fkey2" FOREIGN KEY (test1_fk) REFERENCES test1(id)
In fact same thing happens when adding unnamed check constraints (like
I did above when adding the foreign key constraint):
alter table bar add check (a > 0);
alter table bar add check (a > 0);
alter table bar add check (a > 0);
\d bar
Table "public.bar"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Check constraints:
"bar_a_check" CHECK (a > 0)
"bar_a_check1" CHECK (a > 0)
"bar_a_check2" CHECK (a > 0)
Foreign-key constraints:
"bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
"bar_a_fkey1" FOREIGN KEY (a) REFERENCES foo(a)
"bar_a_fkey2" FOREIGN KEY (a) REFERENCES foo(a)
I don't know why Postgres doesn't try to recognize a duplicate
constraint definition. Maybe the thinking is that users won't
deliberately add the same constraint, but the resulting behavior as
seen in the OP's example may surprise some.
Thanks,
Amit
