BUG #15422: add column statement with if not exists creates a newforeign key even if the column already exists

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15422: add column statement with if not exists creates a newforeign key even if the column already exists
Дата
Msg-id 15422-1f1ee97a6a8c57df@postgresql.org
обсуждение исходный текст
Список pgsql-bugs
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)

```


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Wim Tuitman
Дата:
Сообщение: No error or completion message dureing csv uload in pgadmin
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15423: A typo in the pg_basebackup