The following bug has been logged on the website:
Bug reference: 15422
Logged by: Dale B
Email address: dabrown+pg@goscoutgo.com
PostgreSQL version: 10.5
Operating system: Docker (postgres:latest) on Ubuntu 14.04
Description:
# Bug description
Foreign key is still created for an `alter table ... add column if not
exists ... references ... ;` statement even if the column exists. I would
expect no schema change to occur if the column already exists, but instead a
new foreign key constraint is added every time the query is run.
# Reproduction steps
On a fresh postgres 10.5 install:
```
create table if not exists test (id int primary key);
create table if not exists test2 (test_id int);
\d+ test;
alter table test2 add column if not exists test_id int references
test(id);
\d+ test;
alter table test2 add column if not exists test_id int references
test(id);
\d+ test;
```
This gives me the following output:
```
CREATE TABLE
CREATE TABLE
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
psql:test.sql:4: NOTICE: column "test_id" of relation "test2" already
exists, skipping
ALTER TABLE
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "test2" CONSTRAINT "test2_test_id_fkey" FOREIGN KEY (test_id)
REFERENCES test(id)
psql:test.sql:6: NOTICE: column "test_id" of relation "test2" already
exists, skipping
ALTER TABLE
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "test2" CONSTRAINT "test2_test_id_fkey" FOREIGN KEY (test_id)
REFERENCES test(id)
TABLE "test2" CONSTRAINT "test2_test_id_fkey1" FOREIGN KEY (test_id)
REFERENCES test(id)
```
