Thread: BUG #15422: add column statement with if not exists creates a newforeign key even if the column already exists
BUG #15422: add column statement with if not exists creates a newforeign key even if the column already exists
From
PG Bug reporting form
Date:
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) ```