diff options
| author | Andrew Dunstan <andrew@dunslane.net> | 2015-07-29 21:30:00 -0400 |
|---|---|---|
| committer | Andrew Dunstan <andrew@dunslane.net> | 2015-07-29 21:30:00 -0400 |
| commit | 2cd40adb85d1d5a7fd448721b326e73c1e318750 (patch) | |
| tree | 31a9ac16f905a9c7774f660ae623239b1c5d84e8 /src/test/regress | |
| parent | 632cd9f892119858bc5b617bb60c0377a8a2ed13 (diff) | |
| download | postgresql-2cd40adb85d1d5a7fd448721b326e73c1e318750.tar.gz | |
Add IF NOT EXISTS processing to ALTER TABLE ADD COLUMN
FabrÃzio de Royes Mello, reviewed by Payal Singh, Alvaro Herrera and
Michael Paquier.
Diffstat (limited to 'src/test/regress')
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 89 | ||||
| -rw-r--r-- | src/test/regress/sql/alter_table.sql | 31 |
2 files changed, 120 insertions, 0 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 8d124961ce..028d6ed85c 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2605,3 +2605,92 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; +-- test ADD COLUMN IF NOT EXISTS +CREATE TABLE test_add_column(c1 integer); +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer; +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer; -- fail because c2 already exists +ERROR: column "c2" of relation "test_add_column" already exists +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN c2 integer, -- fail because c2 already exists + ADD COLUMN c3 integer; +ERROR: column "c2" of relation "test_add_column" already exists +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN c3 integer; -- fail because c3 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +NOTICE: column "c3" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists + ADD COLUMN c4 integer; +NOTICE: column "c2" of relation "test_add_column" already exists, skipping +NOTICE: column "c3" of relation "test_add_column" already exists, skipping +\d test_add_column +Table "public.test_add_column" + Column | Type | Modifiers +--------+---------+----------- + c1 | integer | + c2 | integer | + c3 | integer | + c4 | integer | + +DROP TABLE test_add_column; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 3feed7c232..aefb5c9376 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1724,3 +1724,34 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing DROP TABLE logged3; DROP TABLE logged2; DROP TABLE logged1; + +-- test ADD COLUMN IF NOT EXISTS +CREATE TABLE test_add_column(c1 integer); +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer; +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer; -- fail because c2 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer; -- skipping because c2 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN c2 integer, -- fail because c2 already exists + ADD COLUMN c3 integer; +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN c3 integer; -- fail because c3 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer; -- skipping because c3 already exists +\d test_add_column +ALTER TABLE test_add_column + ADD COLUMN IF NOT EXISTS c2 integer, -- skipping because c2 already exists + ADD COLUMN IF NOT EXISTS c3 integer, -- skipping because c3 already exists + ADD COLUMN c4 integer; +\d test_add_column +DROP TABLE test_add_column; |