granicus.if.org Git - postgresql/blob - src/test/regress/expected/alter_table.out

   1 --
   2 -- ALTER_TABLE
   3 -- add attribute
   4 --
   5 CREATE TABLE tmp (initial int4);
   6 COMMENT ON TABLE tmp_wrong IS 'table comment';
   7 ERROR:  relation "tmp_wrong" does not exist
   8 COMMENT ON TABLE tmp IS 'table comment';
   9 COMMENT ON TABLE tmp IS NULL;
  10 ALTER TABLE tmp ADD COLUMN xmin integer; -- fails
  11 ERROR:  column name "xmin" conflicts with a system column name
  12 ALTER TABLE tmp ADD COLUMN a int4 default 3;
  13 ALTER TABLE tmp ADD COLUMN b name;
  14 ALTER TABLE tmp ADD COLUMN c text;
  15 ALTER TABLE tmp ADD COLUMN d float8;
  16 ALTER TABLE tmp ADD COLUMN e float4;
  17 ALTER TABLE tmp ADD COLUMN f int2;
  18 ALTER TABLE tmp ADD COLUMN g polygon;
  19 ALTER TABLE tmp ADD COLUMN h abstime;
  20 ALTER TABLE tmp ADD COLUMN i char;
  21 ALTER TABLE tmp ADD COLUMN j abstime[];
  22 ALTER TABLE tmp ADD COLUMN k int4;
  23 ALTER TABLE tmp ADD COLUMN l tid;
  24 ALTER TABLE tmp ADD COLUMN m xid;
  25 ALTER TABLE tmp ADD COLUMN n oidvector;
  26 --ALTER TABLE tmp ADD COLUMN o lock;
  27 ALTER TABLE tmp ADD COLUMN p smgr;
  28 ALTER TABLE tmp ADD COLUMN q point;
  29 ALTER TABLE tmp ADD COLUMN r lseg;
  30 ALTER TABLE tmp ADD COLUMN s path;
  31 ALTER TABLE tmp ADD COLUMN t box;
  32 ALTER TABLE tmp ADD COLUMN u tinterval;
  33 ALTER TABLE tmp ADD COLUMN v timestamp;
  34 ALTER TABLE tmp ADD COLUMN w interval;
  35 ALTER TABLE tmp ADD COLUMN x float8[];
  36 ALTER TABLE tmp ADD COLUMN y float4[];
  37 ALTER TABLE tmp ADD COLUMN z int2[];
  38 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  39         v, w, x, y, z)
  40    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
  41         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
  42         314159, '(1,1)', '512',
  43         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
  44         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
  45         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
  46 SELECT * FROM tmp;
  47  initial | a |  b   |  c   |  d  |  e  | f |           g           |              h               | i |                                               j                                                |   k    |   l   |  m  |        n        |       p       |     q     |           r           |              s              |          t          |                      u                      |            v             |        w         |     x     |     y     |     z
  48 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
  49          | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
  50 (1 row)
  51
  52 DROP TABLE tmp;
  53 -- the wolf bug - schema mods caused inconsistent row descriptors
  54 CREATE TABLE tmp (
  55         initial         int4
  56 );
  57 ALTER TABLE tmp ADD COLUMN a int4;
  58 ALTER TABLE tmp ADD COLUMN b name;
  59 ALTER TABLE tmp ADD COLUMN c text;
  60 ALTER TABLE tmp ADD COLUMN d float8;
  61 ALTER TABLE tmp ADD COLUMN e float4;
  62 ALTER TABLE tmp ADD COLUMN f int2;
  63 ALTER TABLE tmp ADD COLUMN g polygon;
  64 ALTER TABLE tmp ADD COLUMN h abstime;
  65 ALTER TABLE tmp ADD COLUMN i char;
  66 ALTER TABLE tmp ADD COLUMN j abstime[];
  67 ALTER TABLE tmp ADD COLUMN k int4;
  68 ALTER TABLE tmp ADD COLUMN l tid;
  69 ALTER TABLE tmp ADD COLUMN m xid;
  70 ALTER TABLE tmp ADD COLUMN n oidvector;
  71 --ALTER TABLE tmp ADD COLUMN o lock;
  72 ALTER TABLE tmp ADD COLUMN p smgr;
  73 ALTER TABLE tmp ADD COLUMN q point;
  74 ALTER TABLE tmp ADD COLUMN r lseg;
  75 ALTER TABLE tmp ADD COLUMN s path;
  76 ALTER TABLE tmp ADD COLUMN t box;
  77 ALTER TABLE tmp ADD COLUMN u tinterval;
  78 ALTER TABLE tmp ADD COLUMN v timestamp;
  79 ALTER TABLE tmp ADD COLUMN w interval;
  80 ALTER TABLE tmp ADD COLUMN x float8[];
  81 ALTER TABLE tmp ADD COLUMN y float4[];
  82 ALTER TABLE tmp ADD COLUMN z int2[];
  83 INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
  84         v, w, x, y, z)
  85    VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
  86         'Mon May  1 00:30:30 1995', 'c', '{Mon May  1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
  87         314159, '(1,1)', '512',
  88         '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
  89         '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
  90         'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
  91 SELECT * FROM tmp;
  92  initial | a |  b   |  c   |  d  |  e  | f |           g           |              h               | i |                                               j                                                |   k    |   l   |  m  |        n        |       p       |     q     |           r           |              s              |          t          |                      u                      |            v             |        w         |     x     |     y     |     z
  93 ---------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+-----------
  94          | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
  95 (1 row)
  96
  97 DROP TABLE tmp;
  98 --
  99 -- rename - check on both non-temp and temp tables
 100 --
 101 CREATE TABLE tmp (regtable int);
 102 CREATE TEMP TABLE tmp (tmptable int);
 103 ALTER TABLE tmp RENAME TO tmp_new;
 104 SELECT * FROM tmp;
 105  regtable
 106 ----------
 107 (0 rows)
 108
 109 SELECT * FROM tmp_new;
 110  tmptable
 111 ----------
 112 (0 rows)
 113
 114 ALTER TABLE tmp RENAME TO tmp_new2;
 115 SELECT * FROM tmp;              -- should fail
 116 ERROR:  relation "tmp" does not exist
 117 LINE 1: SELECT * FROM tmp;
 118                       ^
 119 SELECT * FROM tmp_new;
 120  tmptable
 121 ----------
 122 (0 rows)
 123
 124 SELECT * FROM tmp_new2;
 125  regtable
 126 ----------
 127 (0 rows)
 128
 129 DROP TABLE tmp_new;
 130 DROP TABLE tmp_new2;
 131 -- ALTER TABLE ... RENAME on non-table relations
 132 -- renaming indexes (FIXME: this should probably test the index's functionality)
 133 ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
 134 NOTICE:  relation "__onek_unique1" does not exist, skipping
 135 ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
 136 NOTICE:  relation "__tmp_onek_unique1" does not exist, skipping
 137 ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
 138 ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
 139 -- renaming views
 140 CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
 141 ALTER TABLE tmp_view RENAME TO tmp_view_new;
 142 -- hack to ensure we get an indexscan here
 143 ANALYZE tenk1;
 144 set enable_seqscan to off;
 145 set enable_bitmapscan to off;
 146 -- 5 values, sorted
 147 SELECT unique1 FROM tenk1 WHERE unique1 < 5;
 148  unique1
 149 ---------
 150        0
 151        1
 152        2
 153        3
 154        4
 155 (5 rows)
 156
 157 reset enable_seqscan;
 158 reset enable_bitmapscan;
 159 DROP VIEW tmp_view_new;
 160 -- toast-like relation name
 161 alter table stud_emp rename to pg_toast_stud_emp;
 162 alter table pg_toast_stud_emp rename to stud_emp;
 163 -- renaming index should rename constraint as well
 164 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
 165 ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
 166 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
 167 -- renaming constraint
 168 ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
 169 ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
 170 ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
 171 -- renaming constraint should rename index as well
 172 ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
 173 DROP INDEX onek_unique1_constraint;  -- to see whether it's there
 174 ERROR:  cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
 175 HINT:  You can drop constraint onek_unique1_constraint on table onek instead.
 176 ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
 177 DROP INDEX onek_unique1_constraint_foo;  -- to see whether it's there
 178 ERROR:  cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
 179 HINT:  You can drop constraint onek_unique1_constraint_foo on table onek instead.
 180 ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
 181 -- renaming constraints vs. inheritance
 182 CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
 183 \d constraint_rename_test
 184 Table "public.constraint_rename_test"
 185  Column |  Type   | Modifiers
 186 --------+---------+-----------
 187  a      | integer |
 188  b      | integer |
 189  c      | integer |
 190 Check constraints:
 191     "con1" CHECK (a > 0)
 192
 193 CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
 194 NOTICE:  merging column "a" with inherited definition
 195 NOTICE:  merging constraint "con1" with inherited definition
 196 \d constraint_rename_test2
 197 Table "public.constraint_rename_test2"
 198  Column |  Type   | Modifiers
 199 --------+---------+-----------
 200  a      | integer |
 201  b      | integer |
 202  c      | integer |
 203  d      | integer |
 204 Check constraints:
 205     "con1" CHECK (a > 0)
 206 Inherits: constraint_rename_test
 207
 208 ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
 209 ERROR:  cannot rename inherited constraint "con1"
 210 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
 211 ERROR:  inherited constraint "con1" must be renamed in child tables too
 212 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
 213 \d constraint_rename_test
 214 Table "public.constraint_rename_test"
 215  Column |  Type   | Modifiers
 216 --------+---------+-----------
 217  a      | integer |
 218  b      | integer |
 219  c      | integer |
 220 Check constraints:
 221     "con1foo" CHECK (a > 0)
 222 Number of child tables: 1 (Use \d+ to list them.)
 223
 224 \d constraint_rename_test2
 225 Table "public.constraint_rename_test2"
 226  Column |  Type   | Modifiers
 227 --------+---------+-----------
 228  a      | integer |
 229  b      | integer |
 230  c      | integer |
 231  d      | integer |
 232 Check constraints:
 233     "con1foo" CHECK (a > 0)
 234 Inherits: constraint_rename_test
 235
 236 ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0) NO INHERIT;
 237 ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
 238 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
 239 \d constraint_rename_test
 240 Table "public.constraint_rename_test"
 241  Column |  Type   | Modifiers
 242 --------+---------+-----------
 243  a      | integer |
 244  b      | integer |
 245  c      | integer |
 246 Check constraints:
 247     "con1foo" CHECK (a > 0)
 248     "con2bar" CHECK (b > 0) NO INHERIT
 249 Number of child tables: 1 (Use \d+ to list them.)
 250
 251 \d constraint_rename_test2
 252 Table "public.constraint_rename_test2"
 253  Column |  Type   | Modifiers
 254 --------+---------+-----------
 255  a      | integer |
 256  b      | integer |
 257  c      | integer |
 258  d      | integer |
 259 Check constraints:
 260     "con1foo" CHECK (a > 0)
 261 Inherits: constraint_rename_test
 262
 263 ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
 264 ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
 265 \d constraint_rename_test
 266 Table "public.constraint_rename_test"
 267  Column |  Type   | Modifiers
 268 --------+---------+-----------
 269  a      | integer | not null
 270  b      | integer |
 271  c      | integer |
 272 Indexes:
 273     "con3foo" PRIMARY KEY, btree (a)
 274 Check constraints:
 275     "con1foo" CHECK (a > 0)
 276     "con2bar" CHECK (b > 0) NO INHERIT
 277 Number of child tables: 1 (Use \d+ to list them.)
 278
 279 \d constraint_rename_test2
 280 Table "public.constraint_rename_test2"
 281  Column |  Type   | Modifiers
 282 --------+---------+-----------
 283  a      | integer |
 284  b      | integer |
 285  c      | integer |
 286  d      | integer |
 287 Check constraints:
 288     "con1foo" CHECK (a > 0)
 289 Inherits: constraint_rename_test
 290
 291 DROP TABLE constraint_rename_test2;
 292 DROP TABLE constraint_rename_test;
 293 ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
 294 NOTICE:  relation "constraint_rename_test" does not exist, skipping
 295 -- FOREIGN KEY CONSTRAINT adding TEST
 296 CREATE TABLE tmp2 (a int primary key);
 297 CREATE TABLE tmp3 (a int, b int);
 298 CREATE TABLE tmp4 (a int, b int, unique(a,b));
 299 CREATE TABLE tmp5 (a int, b int);
 300 -- Insert rows into tmp2 (pktable)
 301 INSERT INTO tmp2 values (1);
 302 INSERT INTO tmp2 values (2);
 303 INSERT INTO tmp2 values (3);
 304 INSERT INTO tmp2 values (4);
 305 -- Insert rows into tmp3
 306 INSERT INTO tmp3 values (1,10);
 307 INSERT INTO tmp3 values (1,20);
 308 INSERT INTO tmp3 values (5,50);
 309 -- Try (and fail) to add constraint due to invalid source columns
 310 ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
 311 ERROR:  column "c" referenced in foreign key constraint does not exist
 312 -- Try (and fail) to add constraint due to invalide destination columns explicitly given
 313 ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
 314 ERROR:  column "b" referenced in foreign key constraint does not exist
 315 -- Try (and fail) to add constraint due to invalid data
 316 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 317 ERROR:  insert or update on table "tmp3" violates foreign key constraint "tmpconstr"
 318 DETAIL:  Key (a)=(5) is not present in table "tmp2".
 319 -- Delete failing row
 320 DELETE FROM tmp3 where a=5;
 321 -- Try (and succeed)
 322 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
 323 ALTER TABLE tmp3 drop constraint tmpconstr;
 324 INSERT INTO tmp3 values (5,50);
 325 -- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
 326 ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full NOT VALID;
 327 ALTER TABLE tmp3 validate constraint tmpconstr;
 328 ERROR:  insert or update on table "tmp3" violates foreign key constraint "tmpconstr"
 329 DETAIL:  Key (a)=(5) is not present in table "tmp2".
 330 -- Delete failing row
 331 DELETE FROM tmp3 where a=5;
 332 -- Try (and succeed) and repeat to show it works on already valid constraint
 333 ALTER TABLE tmp3 validate constraint tmpconstr;
 334 ALTER TABLE tmp3 validate constraint tmpconstr;
 335 -- Try a non-verified CHECK constraint
 336 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
 337 ERROR:  check constraint "b_greater_than_ten" is violated by some row
 338 ALTER TABLE tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
 339 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
 340 ERROR:  check constraint "b_greater_than_ten" is violated by some row
 341 DELETE FROM tmp3 WHERE NOT b > 10;
 342 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
 343 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
 344 -- Test inherited NOT VALID CHECK constraints
 345 select * from tmp3;
 346  a | b
 347 ---+----
 348  1 | 20
 349 (1 row)
 350
 351 CREATE TABLE tmp6 () INHERITS (tmp3);
 352 CREATE TABLE tmp7 () INHERITS (tmp3);
 353 INSERT INTO tmp6 VALUES (6, 30), (7, 16);
 354 ALTER TABLE tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
 355 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20;   -- fails
 356 ERROR:  check constraint "b_le_20" is violated by some row
 357 DELETE FROM tmp6 WHERE b > 20;
 358 ALTER TABLE tmp3 VALIDATE CONSTRAINT b_le_20;   -- succeeds
 359 -- An already validated constraint must not be revalidated
 360 CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
 361 INSERT INTO tmp7 VALUES (8, 18);
 362 ALTER TABLE tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
 363 NOTICE:  boo: 18
 364 ALTER TABLE tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
 365 NOTICE:  merging constraint "identity" with inherited definition
 366 ALTER TABLE tmp3 VALIDATE CONSTRAINT identity;
 367 NOTICE:  boo: 16
 368 NOTICE:  boo: 20
 369 -- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
 370 -- tmp4 is a,b
 371 ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
 372 ERROR:  there is no unique constraint matching given keys for referenced table "tmp4"
 373 DROP TABLE tmp7;
 374 DROP TABLE tmp6;
 375 DROP TABLE tmp5;
 376 DROP TABLE tmp4;
 377 DROP TABLE tmp3;
 378 DROP TABLE tmp2;
 379 -- NOT VALID with plan invalidation -- ensure we don't use a constraint for
 380 -- exclusion until validated
 381 set constraint_exclusion TO 'partition';
 382 create table nv_parent (d date);
 383 create table nv_child_2010 () inherits (nv_parent);
 384 create table nv_child_2011 () inherits (nv_parent);
 385 alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
 386 alter table nv_child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
 387 explain (costs off) select * from nv_parent where d between '2011-08-01' and '2011-08-31';
 388                                 QUERY PLAN
 389 ---------------------------------------------------------------------------
 390  Append
 391    ->  Seq Scan on nv_parent
 392          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 393    ->  Seq Scan on nv_child_2010
 394          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 395    ->  Seq Scan on nv_child_2011
 396          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 397 (7 rows)
 398
 399 create table nv_child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) inherits (nv_parent);
 400 explain (costs off) select * from nv_parent where d between '2011-08-01'::date and '2011-08-31'::date;
 401                                 QUERY PLAN
 402 ---------------------------------------------------------------------------
 403  Append
 404    ->  Seq Scan on nv_parent
 405          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 406    ->  Seq Scan on nv_child_2010
 407          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 408    ->  Seq Scan on nv_child_2011
 409          Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date))
 410 (7 rows)
 411
 412 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
 413                                 QUERY PLAN
 414 ---------------------------------------------------------------------------
 415  Append
 416    ->  Seq Scan on nv_parent
 417          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 418    ->  Seq Scan on nv_child_2010
 419          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 420    ->  Seq Scan on nv_child_2011
 421          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 422    ->  Seq Scan on nv_child_2009
 423          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 424 (9 rows)
 425
 426 -- after validation, the constraint should be used
 427 alter table nv_child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
 428 explain (costs off) select * from nv_parent where d between '2009-08-01'::date and '2009-08-31'::date;
 429                                 QUERY PLAN
 430 ---------------------------------------------------------------------------
 431  Append
 432    ->  Seq Scan on nv_parent
 433          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 434    ->  Seq Scan on nv_child_2010
 435          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 436    ->  Seq Scan on nv_child_2009
 437          Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date))
 438 (7 rows)
 439
 440 -- Foreign key adding test with mixed types
 441 -- Note: these tables are TEMP to avoid name conflicts when this test
 442 -- is run in parallel with foreign_key.sql.
 443 CREATE TEMP TABLE PKTABLE (ptest1 int PRIMARY KEY);
 444 INSERT INTO PKTABLE VALUES(42);
 445 CREATE TEMP TABLE FKTABLE (ftest1 inet);
 446 -- This next should fail, because int=inet does not exist
 447 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 448 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 449 DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
 450 -- This should also fail for the same reason, but here we
 451 -- give the column name
 452 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1);
 453 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 454 DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
 455 DROP TABLE FKTABLE;
 456 -- This should succeed, even though they are different types,
 457 -- because int=int8 exists and is a member of the integer opfamily
 458 CREATE TEMP TABLE FKTABLE (ftest1 int8);
 459 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 460 -- Check it actually works
 461 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 462 INSERT INTO FKTABLE VALUES(43);         -- should fail
 463 ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
 464 DETAIL:  Key (ftest1)=(43) is not present in table "pktable".
 465 DROP TABLE FKTABLE;
 466 -- This should fail, because we'd have to cast numeric to int which is
 467 -- not an implicit coercion (or use numeric=numeric, but that's not part
 468 -- of the integer opfamily)
 469 CREATE TEMP TABLE FKTABLE (ftest1 numeric);
 470 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 471 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 472 DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: numeric and integer.
 473 DROP TABLE FKTABLE;
 474 DROP TABLE PKTABLE;
 475 -- On the other hand, this should work because int implicitly promotes to
 476 -- numeric, and we allow promotion on the FK side
 477 CREATE TEMP TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
 478 INSERT INTO PKTABLE VALUES(42);
 479 CREATE TEMP TABLE FKTABLE (ftest1 int);
 480 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;
 481 -- Check it actually works
 482 INSERT INTO FKTABLE VALUES(42);         -- should succeed
 483 INSERT INTO FKTABLE VALUES(43);         -- should fail
 484 ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_ftest1_fkey"
 485 DETAIL:  Key (ftest1)=(43) is not present in table "pktable".
 486 DROP TABLE FKTABLE;
 487 DROP TABLE PKTABLE;
 488 CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 inet,
 489                            PRIMARY KEY(ptest1, ptest2));
 490 -- This should fail, because we just chose really odd types
 491 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 492 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references pktable;
 493 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 494 DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
 495 DROP TABLE FKTABLE;
 496 -- Again, so should this...
 497 CREATE TEMP TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
 498 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 499      references pktable(ptest1, ptest2);
 500 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 501 DETAIL:  Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
 502 DROP TABLE FKTABLE;
 503 -- This fails because we mixed up the column ordering
 504 CREATE TEMP TABLE FKTABLE (ftest1 int, ftest2 inet);
 505 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
 506      references pktable(ptest2, ptest1);
 507 ERROR:  foreign key constraint "fktable_ftest1_fkey" cannot be implemented
 508 DETAIL:  Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
 509 -- As does this...
 510 ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
 511      references pktable(ptest1, ptest2);
 512 ERROR:  foreign key constraint "fktable_ftest2_fkey" cannot be implemented
 513 DETAIL:  Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
 514 -- temp tables should go away by themselves, need not drop them.
 515 -- test check constraint adding
 516 create table atacc1 ( test int );
 517 -- add a check constraint
 518 alter table atacc1 add constraint atacc_test1 check (test>3);
 519 -- should fail
 520 insert into atacc1 (test) values (2);
 521 ERROR:  new row for relation "atacc1" violates check constraint "atacc_test1"
 522 DETAIL:  Failing row contains (2).
 523 -- should succeed
 524 insert into atacc1 (test) values (4);
 525 drop table atacc1;
 526 -- let's do one where the check fails when added
 527 create table atacc1 ( test int );
 528 -- insert a soon to be failing row
 529 insert into atacc1 (test) values (2);
 530 -- add a check constraint (fails)
 531 alter table atacc1 add constraint atacc_test1 check (test>3);
 532 ERROR:  check constraint "atacc_test1" is violated by some row
 533 insert into atacc1 (test) values (4);
 534 drop table atacc1;
 535 -- let's do one where the check fails because the column doesn't exist
 536 create table atacc1 ( test int );
 537 -- add a check constraint (fails)
 538 alter table atacc1 add constraint atacc_test1 check (test1>3);
 539 ERROR:  column "test1" does not exist
 540 drop table atacc1;
 541 -- something a little more complicated
 542 create table atacc1 ( test int, test2 int, test3 int);
 543 -- add a check constraint (fails)
 544 alter table atacc1 add constraint atacc_test1 check (test+test2<test3*4);
 545 -- should fail
 546 insert into atacc1 (test,test2,test3) values (4,4,2);
 547 ERROR:  new row for relation "atacc1" violates check constraint "atacc_test1"
 548 DETAIL:  Failing row contains (4, 4, 2).
 549 -- should succeed
 550 insert into atacc1 (test,test2,test3) values (4,4,5);
 551 drop table atacc1;
 552 -- lets do some naming tests
 553 create table atacc1 (test int check (test>3), test2 int);
 554 alter table atacc1 add check (test2>test);
 555 -- should fail for $2
 556 insert into atacc1 (test2, test) values (3, 4);
 557 ERROR:  new row for relation "atacc1" violates check constraint "atacc1_check"
 558 DETAIL:  Failing row contains (4, 3).
 559 drop table atacc1;
 560 -- inheritance related tests
 561 create table atacc1 (test int);
 562 create table atacc2 (test2 int);
 563 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 564 alter table atacc2 add constraint foo check (test2>0);
 565 -- fail and then succeed on atacc2
 566 insert into atacc2 (test2) values (-3);
 567 ERROR:  new row for relation "atacc2" violates check constraint "foo"
 568 DETAIL:  Failing row contains (-3).
 569 insert into atacc2 (test2) values (3);
 570 -- fail and then succeed on atacc3
 571 insert into atacc3 (test2) values (-3);
 572 ERROR:  new row for relation "atacc3" violates check constraint "foo"
 573 DETAIL:  Failing row contains (null, -3, null).
 574 insert into atacc3 (test2) values (3);
 575 drop table atacc3;
 576 drop table atacc2;
 577 drop table atacc1;
 578 -- same things with one created with INHERIT
 579 create table atacc1 (test int);
 580 create table atacc2 (test2 int);
 581 create table atacc3 (test3 int) inherits (atacc1, atacc2);
 582 alter table atacc3 no inherit atacc2;
 583 -- fail
 584 alter table atacc3 no inherit atacc2;
 585 ERROR:  relation "atacc2" is not a parent of relation "atacc3"
 586 -- make sure it really isn't a child
 587 insert into atacc3 (test2) values (3);
 588 select test2 from atacc2;
 589  test2
 590 -------
 591 (0 rows)
 592
 593 -- fail due to missing constraint
 594 alter table atacc2 add constraint foo check (test2>0);
 595 alter table atacc3 inherit atacc2;
 596 ERROR:  child table is missing constraint "foo"
 597 -- fail due to missing column
 598 alter table atacc3 rename test2 to testx;
 599 alter table atacc3 inherit atacc2;
 600 ERROR:  child table is missing column "test2"
 601 -- fail due to mismatched data type
 602 alter table atacc3 add test2 bool;
 603 alter table atacc3 inherit atacc2;
 604 ERROR:  child table "atacc3" has different type for column "test2"
 605 alter table atacc3 drop test2;
 606 -- succeed
 607 alter table atacc3 add test2 int;
 608 update atacc3 set test2 = 4 where test2 is null;
 609 alter table atacc3 add constraint foo check (test2>0);
 610 alter table atacc3 inherit atacc2;
 611 -- fail due to duplicates and circular inheritance
 612 alter table atacc3 inherit atacc2;
 613 ERROR:  relation "atacc2" would be inherited from more than once
 614 alter table atacc2 inherit atacc3;
 615 ERROR:  circular inheritance not allowed
 616 DETAIL:  "atacc3" is already a child of "atacc2".
 617 alter table atacc2 inherit atacc2;
 618 ERROR:  circular inheritance not allowed
 619 DETAIL:  "atacc2" is already a child of "atacc2".
 620 -- test that we really are a child now (should see 4 not 3 and cascade should go through)
 621 select test2 from atacc2;
 622  test2
 623 -------
 624      4
 625 (1 row)
 626
 627 drop table atacc2 cascade;
 628 NOTICE:  drop cascades to table atacc3
 629 drop table atacc1;
 630 -- adding only to a parent is allowed as of 9.2
 631 create table atacc1 (test int);
 632 create table atacc2 (test2 int) inherits (atacc1);
 633 -- ok:
 634 alter table atacc1 add constraint foo check (test>0) no inherit;
 635 -- check constraint is not there on child
 636 insert into atacc2 (test) values (-3);
 637 -- check constraint is there on parent
 638 insert into atacc1 (test) values (-3);
 639 ERROR:  new row for relation "atacc1" violates check constraint "foo"
 640 DETAIL:  Failing row contains (-3).
 641 insert into atacc1 (test) values (3);
 642 -- fail, violating row:
 643 alter table atacc2 add constraint foo check (test>0) no inherit;
 644 ERROR:  check constraint "foo" is violated by some row
 645 drop table atacc2;
 646 drop table atacc1;
 647 -- test unique constraint adding
 648 create table atacc1 ( test int ) with oids;
 649 -- add a unique constraint
 650 alter table atacc1 add constraint atacc_test1 unique (test);
 651 -- insert first value
 652 insert into atacc1 (test) values (2);
 653 -- should fail
 654 insert into atacc1 (test) values (2);
 655 ERROR:  duplicate key value violates unique constraint "atacc_test1"
 656 DETAIL:  Key (test)=(2) already exists.
 657 -- should succeed
 658 insert into atacc1 (test) values (4);
 659 -- try adding a unique oid constraint
 660 alter table atacc1 add constraint atacc_oid1 unique(oid);
 661 -- try to create duplicates via alter table using - should fail
 662 alter table atacc1 alter column test type integer using 0;
 663 ERROR:  could not create unique index "atacc_test1"
 664 DETAIL:  Key (test)=(0) is duplicated.
 665 drop table atacc1;
 666 -- let's do one where the unique constraint fails when added
 667 create table atacc1 ( test int );
 668 -- insert soon to be failing rows
 669 insert into atacc1 (test) values (2);
 670 insert into atacc1 (test) values (2);
 671 -- add a unique constraint (fails)
 672 alter table atacc1 add constraint atacc_test1 unique (test);
 673 ERROR:  could not create unique index "atacc_test1"
 674 DETAIL:  Key (test)=(2) is duplicated.
 675 insert into atacc1 (test) values (3);
 676 drop table atacc1;
 677 -- let's do one where the unique constraint fails
 678 -- because the column doesn't exist
 679 create table atacc1 ( test int );
 680 -- add a unique constraint (fails)
 681 alter table atacc1 add constraint atacc_test1 unique (test1);
 682 ERROR:  column "test1" named in key does not exist
 683 drop table atacc1;
 684 -- something a little more complicated
 685 create table atacc1 ( test int, test2 int);
 686 -- add a unique constraint
 687 alter table atacc1 add constraint atacc_test1 unique (test, test2);
 688 -- insert initial value
 689 insert into atacc1 (test,test2) values (4,4);
 690 -- should fail
 691 insert into atacc1 (test,test2) values (4,4);
 692 ERROR:  duplicate key value violates unique constraint "atacc_test1"
 693 DETAIL:  Key (test, test2)=(4, 4) already exists.
 694 -- should all succeed
 695 insert into atacc1 (test,test2) values (4,5);
 696 insert into atacc1 (test,test2) values (5,4);
 697 insert into atacc1 (test,test2) values (5,5);
 698 drop table atacc1;
 699 -- lets do some naming tests
 700 create table atacc1 (test int, test2 int, unique(test));
 701 alter table atacc1 add unique (test2);
 702 -- should fail for @@ second one @@
 703 insert into atacc1 (test2, test) values (3, 3);
 704 insert into atacc1 (test2, test) values (2, 3);
 705 ERROR:  duplicate key value violates unique constraint "atacc1_test_key"
 706 DETAIL:  Key (test)=(3) already exists.
 707 drop table atacc1;
 708 -- test primary key constraint adding
 709 create table atacc1 ( test int ) with oids;
 710 -- add a primary key constraint
 711 alter table atacc1 add constraint atacc_test1 primary key (test);
 712 -- insert first value
 713 insert into atacc1 (test) values (2);
 714 -- should fail
 715 insert into atacc1 (test) values (2);
 716 ERROR:  duplicate key value violates unique constraint "atacc_test1"
 717 DETAIL:  Key (test)=(2) already exists.
 718 -- should succeed
 719 insert into atacc1 (test) values (4);
 720 -- inserting NULL should fail
 721 insert into atacc1 (test) values(NULL);
 722 ERROR:  null value in column "test" violates not-null constraint
 723 DETAIL:  Failing row contains (null).
 724 -- try adding a second primary key (should fail)
 725 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 726 ERROR:  multiple primary keys for table "atacc1" are not allowed
 727 -- drop first primary key constraint
 728 alter table atacc1 drop constraint atacc_test1 restrict;
 729 -- try adding a primary key on oid (should succeed)
 730 alter table atacc1 add constraint atacc_oid1 primary key(oid);
 731 drop table atacc1;
 732 -- let's do one where the primary key constraint fails when added
 733 create table atacc1 ( test int );
 734 -- insert soon to be failing rows
 735 insert into atacc1 (test) values (2);
 736 insert into atacc1 (test) values (2);
 737 -- add a primary key (fails)
 738 alter table atacc1 add constraint atacc_test1 primary key (test);
 739 ERROR:  could not create unique index "atacc_test1"
 740 DETAIL:  Key (test)=(2) is duplicated.
 741 insert into atacc1 (test) values (3);
 742 drop table atacc1;
 743 -- let's do another one where the primary key constraint fails when added
 744 create table atacc1 ( test int );
 745 -- insert soon to be failing row
 746 insert into atacc1 (test) values (NULL);
 747 -- add a primary key (fails)
 748 alter table atacc1 add constraint atacc_test1 primary key (test);
 749 ERROR:  column "test" contains null values
 750 insert into atacc1 (test) values (3);
 751 drop table atacc1;
 752 -- let's do one where the primary key constraint fails
 753 -- because the column doesn't exist
 754 create table atacc1 ( test int );
 755 -- add a primary key constraint (fails)
 756 alter table atacc1 add constraint atacc_test1 primary key (test1);
 757 ERROR:  column "test1" named in key does not exist
 758 drop table atacc1;
 759 -- adding a new column as primary key to a non-empty table.
 760 -- should fail unless the column has a non-null default value.
 761 create table atacc1 ( test int );
 762 insert into atacc1 (test) values (0);
 763 -- add a primary key column without a default (fails).
 764 alter table atacc1 add column test2 int primary key;
 765 ERROR:  column "test2" contains null values
 766 -- now add a primary key column with a default (succeeds).
 767 alter table atacc1 add column test2 int default 0 primary key;
 768 drop table atacc1;
 769 -- something a little more complicated
 770 create table atacc1 ( test int, test2 int);
 771 -- add a primary key constraint
 772 alter table atacc1 add constraint atacc_test1 primary key (test, test2);
 773 -- try adding a second primary key - should fail
 774 alter table atacc1 add constraint atacc_test2 primary key (test);
 775 ERROR:  multiple primary keys for table "atacc1" are not allowed
 776 -- insert initial value
 777 insert into atacc1 (test,test2) values (4,4);
 778 -- should fail
 779 insert into atacc1 (test,test2) values (4,4);
 780 ERROR:  duplicate key value violates unique constraint "atacc_test1"
 781 DETAIL:  Key (test, test2)=(4, 4) already exists.
 782 insert into atacc1 (test,test2) values (NULL,3);
 783 ERROR:  null value in column "test" violates not-null constraint
 784 DETAIL:  Failing row contains (null, 3).
 785 insert into atacc1 (test,test2) values (3, NULL);
 786 ERROR:  null value in column "test2" violates not-null constraint
 787 DETAIL:  Failing row contains (3, null).
 788 insert into atacc1 (test,test2) values (NULL,NULL);
 789 ERROR:  null value in column "test" violates not-null constraint
 790 DETAIL:  Failing row contains (null, null).
 791 -- should all succeed
 792 insert into atacc1 (test,test2) values (4,5);
 793 insert into atacc1 (test,test2) values (5,4);
 794 insert into atacc1 (test,test2) values (5,5);
 795 drop table atacc1;
 796 -- lets do some naming tests
 797 create table atacc1 (test int, test2 int, primary key(test));
 798 -- only first should succeed
 799 insert into atacc1 (test2, test) values (3, 3);
 800 insert into atacc1 (test2, test) values (2, 3);
 801 ERROR:  duplicate key value violates unique constraint "atacc1_pkey"
 802 DETAIL:  Key (test)=(3) already exists.
 803 insert into atacc1 (test2, test) values (1, NULL);
 804 ERROR:  null value in column "test" violates not-null constraint
 805 DETAIL:  Failing row contains (null, 1).
 806 drop table atacc1;
 807 -- alter table / alter column [set/drop] not null tests
 808 -- try altering system catalogs, should fail
 809 alter table pg_class alter column relname drop not null;
 810 ERROR:  permission denied: "pg_class" is a system catalog
 811 alter table pg_class alter relname set not null;
 812 ERROR:  permission denied: "pg_class" is a system catalog
 813 -- try altering non-existent table, should fail
 814 alter table non_existent alter column bar set not null;
 815 ERROR:  relation "non_existent" does not exist
 816 alter table non_existent alter column bar drop not null;
 817 ERROR:  relation "non_existent" does not exist
 818 -- test setting columns to null and not null and vice versa
 819 -- test checking for null values and primary key
 820 create table atacc1 (test int not null) with oids;
 821 alter table atacc1 add constraint "atacc1_pkey" primary key (test);
 822 alter table atacc1 alter column test drop not null;
 823 ERROR:  column "test" is in a primary key
 824 alter table atacc1 drop constraint "atacc1_pkey";
 825 alter table atacc1 alter column test drop not null;
 826 insert into atacc1 values (null);
 827 alter table atacc1 alter test set not null;
 828 ERROR:  column "test" contains null values
 829 delete from atacc1;
 830 alter table atacc1 alter test set not null;
 831 -- try altering a non-existent column, should fail
 832 alter table atacc1 alter bar set not null;
 833 ERROR:  column "bar" of relation "atacc1" does not exist
 834 alter table atacc1 alter bar drop not null;
 835 ERROR:  column "bar" of relation "atacc1" does not exist
 836 -- try altering the oid column, should fail
 837 alter table atacc1 alter oid set not null;
 838 ERROR:  cannot alter system column "oid"
 839 alter table atacc1 alter oid drop not null;
 840 ERROR:  cannot alter system column "oid"
 841 -- try creating a view and altering that, should fail
 842 create view myview as select * from atacc1;
 843 alter table myview alter column test drop not null;
 844 ERROR:  "myview" is not a table or foreign table
 845 alter table myview alter column test set not null;
 846 ERROR:  "myview" is not a table or foreign table
 847 drop view myview;
 848 drop table atacc1;
 849 -- test inheritance
 850 create table parent (a int);
 851 create table child (b varchar(255)) inherits (parent);
 852 alter table parent alter a set not null;
 853 insert into parent values (NULL);
 854 ERROR:  null value in column "a" violates not-null constraint
 855 DETAIL:  Failing row contains (null).
 856 insert into child (a, b) values (NULL, 'foo');
 857 ERROR:  null value in column "a" violates not-null constraint
 858 DETAIL:  Failing row contains (null, foo).
 859 alter table parent alter a drop not null;
 860 insert into parent values (NULL);
 861 insert into child (a, b) values (NULL, 'foo');
 862 alter table only parent alter a set not null;
 863 ERROR:  column "a" contains null values
 864 alter table child alter a set not null;
 865 ERROR:  column "a" contains null values
 866 delete from parent;
 867 alter table only parent alter a set not null;
 868 insert into parent values (NULL);
 869 ERROR:  null value in column "a" violates not-null constraint
 870 DETAIL:  Failing row contains (null).
 871 alter table child alter a set not null;
 872 insert into child (a, b) values (NULL, 'foo');
 873 ERROR:  null value in column "a" violates not-null constraint
 874 DETAIL:  Failing row contains (null, foo).
 875 delete from child;
 876 alter table child alter a set not null;
 877 insert into child (a, b) values (NULL, 'foo');
 878 ERROR:  null value in column "a" violates not-null constraint
 879 DETAIL:  Failing row contains (null, foo).
 880 drop table child;
 881 drop table parent;
 882 -- test setting and removing default values
 883 create table def_test (
 884         c1      int4 default 5,
 885         c2      text default 'initial_default'
 886 );
 887 insert into def_test default values;
 888 alter table def_test alter column c1 drop default;
 889 insert into def_test default values;
 890 alter table def_test alter column c2 drop default;
 891 insert into def_test default values;
 892 alter table def_test alter column c1 set default 10;
 893 alter table def_test alter column c2 set default 'new_default';
 894 insert into def_test default values;
 895 select * from def_test;
 896  c1 |       c2
 897 ----+-----------------
 898   5 | initial_default
 899     | initial_default
 900     |
 901  10 | new_default
 902 (4 rows)
 903
 904 -- set defaults to an incorrect type: this should fail
 905 alter table def_test alter column c1 set default 'wrong_datatype';
 906 ERROR:  invalid input syntax for integer: "wrong_datatype"
 907 alter table def_test alter column c2 set default 20;
 908 -- set defaults on a non-existent column: this should fail
 909 alter table def_test alter column c3 set default 30;
 910 ERROR:  column "c3" of relation "def_test" does not exist
 911 -- set defaults on views: we need to create a view, add a rule
 912 -- to allow insertions into it, and then alter the view to add
 913 -- a default
 914 create view def_view_test as select * from def_test;
 915 create rule def_view_test_ins as
 916         on insert to def_view_test
 917         do instead insert into def_test select new.*;
 918 insert into def_view_test default values;
 919 alter table def_view_test alter column c1 set default 45;
 920 insert into def_view_test default values;
 921 alter table def_view_test alter column c2 set default 'view_default';
 922 insert into def_view_test default values;
 923 select * from def_view_test;
 924  c1 |       c2
 925 ----+-----------------
 926   5 | initial_default
 927     | initial_default
 928     |
 929  10 | new_default
 930     |
 931  45 |
 932  45 | view_default
 933 (7 rows)
 934
 935 drop rule def_view_test_ins on def_view_test;
 936 drop view def_view_test;
 937 drop table def_test;
 938 -- alter table / drop column tests
 939 -- try altering system catalogs, should fail
 940 alter table pg_class drop column relname;
 941 ERROR:  permission denied: "pg_class" is a system catalog
 942 -- try altering non-existent table, should fail
 943 alter table nosuchtable drop column bar;
 944 ERROR:  relation "nosuchtable" does not exist
 945 -- test dropping columns
 946 create table atacc1 (a int4 not null, b int4, c int4 not null, d int4) with oids;
 947 insert into atacc1 values (1, 2, 3, 4);
 948 alter table atacc1 drop a;
 949 alter table atacc1 drop a;
 950 ERROR:  column "a" of relation "atacc1" does not exist
 951 -- SELECTs
 952 select * from atacc1;
 953  b | c | d
 954 ---+---+---
 955  2 | 3 | 4
 956 (1 row)
 957
 958 select * from atacc1 order by a;
 959 ERROR:  column "a" does not exist
 960 LINE 1: select * from atacc1 order by a;
 961                                       ^
 962 select * from atacc1 order by "........pg.dropped.1........";
 963 ERROR:  column "........pg.dropped.1........" does not exist
 964 LINE 1: select * from atacc1 order by "........pg.dropped.1........"...
 965                                       ^
 966 select * from atacc1 group by a;
 967 ERROR:  column "a" does not exist
 968 LINE 1: select * from atacc1 group by a;
 969                                       ^
 970 select * from atacc1 group by "........pg.dropped.1........";
 971 ERROR:  column "........pg.dropped.1........" does not exist
 972 LINE 1: select * from atacc1 group by "........pg.dropped.1........"...
 973                                       ^
 974 select atacc1.* from atacc1;
 975  b | c | d
 976 ---+---+---
 977  2 | 3 | 4
 978 (1 row)
 979
 980 select a from atacc1;
 981 ERROR:  column "a" does not exist
 982 LINE 1: select a from atacc1;
 983                ^
 984 select atacc1.a from atacc1;
 985 ERROR:  column atacc1.a does not exist
 986 LINE 1: select atacc1.a from atacc1;
 987                ^
 988 select b,c,d from atacc1;
 989  b | c | d
 990 ---+---+---
 991  2 | 3 | 4
 992 (1 row)
 993
 994 select a,b,c,d from atacc1;
 995 ERROR:  column "a" does not exist
 996 LINE 1: select a,b,c,d from atacc1;
 997                ^
 998 select * from atacc1 where a = 1;
 999 ERROR:  column "a" does not exist
1000 LINE 1: select * from atacc1 where a = 1;
1001                                    ^
1002 select "........pg.dropped.1........" from atacc1;
1003 ERROR:  column "........pg.dropped.1........" does not exist
1004 LINE 1: select "........pg.dropped.1........" from atacc1;
1005                ^
1006 select atacc1."........pg.dropped.1........" from atacc1;
1007 ERROR:  column atacc1.........pg.dropped.1........ does not exist
1008 LINE 1: select atacc1."........pg.dropped.1........" from atacc1;
1009                ^
1010 select "........pg.dropped.1........",b,c,d from atacc1;
1011 ERROR:  column "........pg.dropped.1........" does not exist
1012 LINE 1: select "........pg.dropped.1........",b,c,d from atacc1;
1013                ^
1014 select * from atacc1 where "........pg.dropped.1........" = 1;
1015 ERROR:  column "........pg.dropped.1........" does not exist
1016 LINE 1: select * from atacc1 where "........pg.dropped.1........" = ...
1017                                    ^
1018 -- UPDATEs
1019 update atacc1 set a = 3;
1020 ERROR:  column "a" of relation "atacc1" does not exist
1021 LINE 1: update atacc1 set a = 3;
1022                           ^
1023 update atacc1 set b = 2 where a = 3;
1024 ERROR:  column "a" does not exist
1025 LINE 1: update atacc1 set b = 2 where a = 3;
1026                                       ^
1027 update atacc1 set "........pg.dropped.1........" = 3;
1028 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1029 LINE 1: update atacc1 set "........pg.dropped.1........" = 3;
1030                           ^
1031 update atacc1 set b = 2 where "........pg.dropped.1........" = 3;
1032 ERROR:  column "........pg.dropped.1........" does not exist
1033 LINE 1: update atacc1 set b = 2 where "........pg.dropped.1........"...
1034                                       ^
1035 -- INSERTs
1036 insert into atacc1 values (10, 11, 12, 13);
1037 ERROR:  INSERT has more expressions than target columns
1038 LINE 1: insert into atacc1 values (10, 11, 12, 13);
1039                                                ^
1040 insert into atacc1 values (default, 11, 12, 13);
1041 ERROR:  INSERT has more expressions than target columns
1042 LINE 1: insert into atacc1 values (default, 11, 12, 13);
1043                                                     ^
1044 insert into atacc1 values (11, 12, 13);
1045 insert into atacc1 (a) values (10);
1046 ERROR:  column "a" of relation "atacc1" does not exist
1047 LINE 1: insert into atacc1 (a) values (10);
1048                             ^
1049 insert into atacc1 (a) values (default);
1050 ERROR:  column "a" of relation "atacc1" does not exist
1051 LINE 1: insert into atacc1 (a) values (default);
1052                             ^
1053 insert into atacc1 (a,b,c,d) values (10,11,12,13);
1054 ERROR:  column "a" of relation "atacc1" does not exist
1055 LINE 1: insert into atacc1 (a,b,c,d) values (10,11,12,13);
1056                             ^
1057 insert into atacc1 (a,b,c,d) values (default,11,12,13);
1058 ERROR:  column "a" of relation "atacc1" does not exist
1059 LINE 1: insert into atacc1 (a,b,c,d) values (default,11,12,13);
1060                             ^
1061 insert into atacc1 (b,c,d) values (11,12,13);
1062 insert into atacc1 ("........pg.dropped.1........") values (10);
1063 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1064 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1065                             ^
1066 insert into atacc1 ("........pg.dropped.1........") values (default);
1067 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1068 LINE 1: insert into atacc1 ("........pg.dropped.1........") values (...
1069                             ^
1070 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (10,11,12,13);
1071 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1072 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1073                             ^
1074 insert into atacc1 ("........pg.dropped.1........",b,c,d) values (default,11,12,13);
1075 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1076 LINE 1: insert into atacc1 ("........pg.dropped.1........",b,c,d) va...
1077                             ^
1078 -- DELETEs
1079 delete from atacc1 where a = 3;
1080 ERROR:  column "a" does not exist
1081 LINE 1: delete from atacc1 where a = 3;
1082                                  ^
1083 delete from atacc1 where "........pg.dropped.1........" = 3;
1084 ERROR:  column "........pg.dropped.1........" does not exist
1085 LINE 1: delete from atacc1 where "........pg.dropped.1........" = 3;
1086                                  ^
1087 delete from atacc1;
1088 -- try dropping a non-existent column, should fail
1089 alter table atacc1 drop bar;
1090 ERROR:  column "bar" of relation "atacc1" does not exist
1091 -- try dropping the oid column, should succeed
1092 alter table atacc1 drop oid;
1093 -- try dropping the xmin column, should fail
1094 alter table atacc1 drop xmin;
1095 ERROR:  cannot drop system column "xmin"
1096 -- try creating a view and altering that, should fail
1097 create view myview as select * from atacc1;
1098 select * from myview;
1099  b | c | d
1100 ---+---+---
1101 (0 rows)
1102
1103 alter table myview drop d;
1104 ERROR:  "myview" is not a table, composite type, or foreign table
1105 drop view myview;
1106 -- test some commands to make sure they fail on the dropped column
1107 analyze atacc1(a);
1108 ERROR:  column "a" of relation "atacc1" does not exist
1109 analyze atacc1("........pg.dropped.1........");
1110 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1111 vacuum analyze atacc1(a);
1112 ERROR:  column "a" of relation "atacc1" does not exist
1113 vacuum analyze atacc1("........pg.dropped.1........");
1114 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1115 comment on column atacc1.a is 'testing';
1116 ERROR:  column "a" of relation "atacc1" does not exist
1117 comment on column atacc1."........pg.dropped.1........" is 'testing';
1118 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1119 alter table atacc1 alter a set storage plain;
1120 ERROR:  column "a" of relation "atacc1" does not exist
1121 alter table atacc1 alter "........pg.dropped.1........" set storage plain;
1122 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1123 alter table atacc1 alter a set statistics 0;
1124 ERROR:  column "a" of relation "atacc1" does not exist
1125 alter table atacc1 alter "........pg.dropped.1........" set statistics 0;
1126 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1127 alter table atacc1 alter a set default 3;
1128 ERROR:  column "a" of relation "atacc1" does not exist
1129 alter table atacc1 alter "........pg.dropped.1........" set default 3;
1130 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1131 alter table atacc1 alter a drop default;
1132 ERROR:  column "a" of relation "atacc1" does not exist
1133 alter table atacc1 alter "........pg.dropped.1........" drop default;
1134 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1135 alter table atacc1 alter a set not null;
1136 ERROR:  column "a" of relation "atacc1" does not exist
1137 alter table atacc1 alter "........pg.dropped.1........" set not null;
1138 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1139 alter table atacc1 alter a drop not null;
1140 ERROR:  column "a" of relation "atacc1" does not exist
1141 alter table atacc1 alter "........pg.dropped.1........" drop not null;
1142 ERROR:  column "........pg.dropped.1........" of relation "atacc1" does not exist
1143 alter table atacc1 rename a to x;
1144 ERROR:  column "a" does not exist
1145 alter table atacc1 rename "........pg.dropped.1........" to x;
1146 ERROR:  column "........pg.dropped.1........" does not exist
1147 alter table atacc1 add primary key(a);
1148 ERROR:  column "a" named in key does not exist
1149 alter table atacc1 add primary key("........pg.dropped.1........");
1150 ERROR:  column "........pg.dropped.1........" named in key does not exist
1151 alter table atacc1 add unique(a);
1152 ERROR:  column "a" named in key does not exist
1153 alter table atacc1 add unique("........pg.dropped.1........");
1154 ERROR:  column "........pg.dropped.1........" named in key does not exist
1155 alter table atacc1 add check (a > 3);
1156 ERROR:  column "a" does not exist
1157 alter table atacc1 add check ("........pg.dropped.1........" > 3);
1158 ERROR:  column "........pg.dropped.1........" does not exist
1159 create table atacc2 (id int4 unique);
1160 alter table atacc1 add foreign key (a) references atacc2(id);
1161 ERROR:  column "a" referenced in foreign key constraint does not exist
1162 alter table atacc1 add foreign key ("........pg.dropped.1........") references atacc2(id);
1163 ERROR:  column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1164 alter table atacc2 add foreign key (id) references atacc1(a);
1165 ERROR:  column "a" referenced in foreign key constraint does not exist
1166 alter table atacc2 add foreign key (id) references atacc1("........pg.dropped.1........");
1167 ERROR:  column "........pg.dropped.1........" referenced in foreign key constraint does not exist
1168 drop table atacc2;
1169 create index "testing_idx" on atacc1(a);
1170 ERROR:  column "a" does not exist
1171 create index "testing_idx" on atacc1("........pg.dropped.1........");
1172 ERROR:  column "........pg.dropped.1........" does not exist
1173 -- test create as and select into
1174 insert into atacc1 values (21, 22, 23);
1175 create table test1 as select * from atacc1;
1176 select * from test1;
1177  b  | c  | d
1178 ----+----+----
1179  21 | 22 | 23
1180 (1 row)
1181
1182 drop table test1;
1183 select * into test2 from atacc1;
1184 select * from test2;
1185  b  | c  | d
1186 ----+----+----
1187  21 | 22 | 23
1188 (1 row)
1189
1190 drop table test2;
1191 -- try dropping all columns
1192 alter table atacc1 drop c;
1193 alter table atacc1 drop d;
1194 alter table atacc1 drop b;
1195 select * from atacc1;
1196 --
1197 (1 row)
1198
1199 drop table atacc1;
1200 -- test constraint error reporting in presence of dropped columns
1201 create table atacc1 (id serial primary key, value int check (value < 10));
1202 insert into atacc1(value) values (100);
1203 ERROR:  new row for relation "atacc1" violates check constraint "atacc1_value_check"
1204 DETAIL:  Failing row contains (1, 100).
1205 alter table atacc1 drop column value;
1206 alter table atacc1 add column value int check (value < 10);
1207 insert into atacc1(value) values (100);
1208 ERROR:  new row for relation "atacc1" violates check constraint "atacc1_value_check"
1209 DETAIL:  Failing row contains (2, 100).
1210 insert into atacc1(id, value) values (null, 0);
1211 ERROR:  null value in column "id" violates not-null constraint
1212 DETAIL:  Failing row contains (null, 0).
1213 drop table atacc1;
1214 -- test inheritance
1215 create table parent (a int, b int, c int);
1216 insert into parent values (1, 2, 3);
1217 alter table parent drop a;
1218 create table child (d varchar(255)) inherits (parent);
1219 insert into child values (12, 13, 'testing');
1220 select * from parent;
1221  b  | c
1222 ----+----
1223   2 |  3
1224  12 | 13
1225 (2 rows)
1226
1227 select * from child;
1228  b  | c  |    d
1229 ----+----+---------
1230  12 | 13 | testing
1231 (1 row)
1232
1233 alter table parent drop c;
1234 select * from parent;
1235  b
1236 ----
1237   2
1238  12
1239 (2 rows)
1240
1241 select * from child;
1242  b  |    d
1243 ----+---------
1244  12 | testing
1245 (1 row)
1246
1247 drop table child;
1248 drop table parent;
1249 -- test copy in/out
1250 create table test (a int4, b int4, c int4);
1251 insert into test values (1,2,3);
1252 alter table test drop a;
1253 copy test to stdout;
1254 2       3
1255 copy test(a) to stdout;
1256 ERROR:  column "a" of relation "test" does not exist
1257 copy test("........pg.dropped.1........") to stdout;
1258 ERROR:  column "........pg.dropped.1........" of relation "test" does not exist
1259 copy test from stdin;
1260 ERROR:  extra data after last expected column
1261 CONTEXT:  COPY test, line 1: "10        11      12"
1262 select * from test;
1263  b | c
1264 ---+---
1265  2 | 3
1266 (1 row)
1267
1268 copy test from stdin;
1269 select * from test;
1270  b  | c
1271 ----+----
1272   2 |  3
1273  21 | 22
1274 (2 rows)
1275
1276 copy test(a) from stdin;
1277 ERROR:  column "a" of relation "test" does not exist
1278 copy test("........pg.dropped.1........") from stdin;
1279 ERROR:  column "........pg.dropped.1........" of relation "test" does not exist
1280 copy test(b,c) from stdin;
1281 select * from test;
1282  b  | c
1283 ----+----
1284   2 |  3
1285  21 | 22
1286  31 | 32
1287 (3 rows)
1288
1289 drop table test;
1290 -- test inheritance
1291 create table dropColumn (a int, b int, e int);
1292 create table dropColumnChild (c int) inherits (dropColumn);
1293 create table dropColumnAnother (d int) inherits (dropColumnChild);
1294 -- these two should fail
1295 alter table dropColumnchild drop column a;
1296 ERROR:  cannot drop inherited column "a"
1297 alter table only dropColumnChild drop column b;
1298 ERROR:  cannot drop inherited column "b"
1299 -- these three should work
1300 alter table only dropColumn drop column e;
1301 alter table dropColumnChild drop column c;
1302 alter table dropColumn drop column a;
1303 create table renameColumn (a int);
1304 create table renameColumnChild (b int) inherits (renameColumn);
1305 create table renameColumnAnother (c int) inherits (renameColumnChild);
1306 -- these three should fail
1307 alter table renameColumnChild rename column a to d;
1308 ERROR:  cannot rename inherited column "a"
1309 alter table only renameColumnChild rename column a to d;
1310 ERROR:  inherited column "a" must be renamed in child tables too
1311 alter table only renameColumn rename column a to d;
1312 ERROR:  inherited column "a" must be renamed in child tables too
1313 -- these should work
1314 alter table renameColumn rename column a to d;
1315 alter table renameColumnChild rename column b to a;
1316 -- these should work
1317 alter table if exists doesnt_exist_tab rename column a to d;
1318 NOTICE:  relation "doesnt_exist_tab" does not exist, skipping
1319 alter table if exists doesnt_exist_tab rename column b to a;
1320 NOTICE:  relation "doesnt_exist_tab" does not exist, skipping
1321 -- this should work
1322 alter table renameColumn add column w int;
1323 -- this should fail
1324 alter table only renameColumn add column x int;
1325 ERROR:  column must be added to child tables too
1326 -- Test corner cases in dropping of inherited columns
1327 create table p1 (f1 int, f2 int);
1328 create table c1 (f1 int not null) inherits(p1);
1329 NOTICE:  merging column "f1" with inherited definition
1330 -- should be rejected since c1.f1 is inherited
1331 alter table c1 drop column f1;
1332 ERROR:  cannot drop inherited column "f1"
1333 -- should work
1334 alter table p1 drop column f1;
1335 -- c1.f1 is still there, but no longer inherited
1336 select f1 from c1;
1337  f1
1338 ----
1339 (0 rows)
1340
1341 alter table c1 drop column f1;
1342 select f1 from c1;
1343 ERROR:  column "f1" does not exist
1344 LINE 1: select f1 from c1;
1345                ^
1346 drop table p1 cascade;
1347 NOTICE:  drop cascades to table c1
1348 create table p1 (f1 int, f2 int);
1349 create table c1 () inherits(p1);
1350 -- should be rejected since c1.f1 is inherited
1351 alter table c1 drop column f1;
1352 ERROR:  cannot drop inherited column "f1"
1353 alter table p1 drop column f1;
1354 -- c1.f1 is dropped now, since there is no local definition for it
1355 select f1 from c1;
1356 ERROR:  column "f1" does not exist
1357 LINE 1: select f1 from c1;
1358                ^
1359 drop table p1 cascade;
1360 NOTICE:  drop cascades to table c1
1361 create table p1 (f1 int, f2 int);
1362 create table c1 () inherits(p1);
1363 -- should be rejected since c1.f1 is inherited
1364 alter table c1 drop column f1;
1365 ERROR:  cannot drop inherited column "f1"
1366 alter table only p1 drop column f1;
1367 -- c1.f1 is NOT dropped, but must now be considered non-inherited
1368 alter table c1 drop column f1;
1369 drop table p1 cascade;
1370 NOTICE:  drop cascades to table c1
1371 create table p1 (f1 int, f2 int);
1372 create table c1 (f1 int not null) inherits(p1);
1373 NOTICE:  merging column "f1" with inherited definition
1374 -- should be rejected since c1.f1 is inherited
1375 alter table c1 drop column f1;
1376 ERROR:  cannot drop inherited column "f1"
1377 alter table only p1 drop column f1;
1378 -- c1.f1 is still there, but no longer inherited
1379 alter table c1 drop column f1;
1380 drop table p1 cascade;
1381 NOTICE:  drop cascades to table c1
1382 create table p1(id int, name text);
1383 create table p2(id2 int, name text, height int);
1384 create table c1(age int) inherits(p1,p2);
1385 NOTICE:  merging multiple inherited definitions of column "name"
1386 create table gc1() inherits (c1);
1387 select relname, attname, attinhcount, attislocal
1388 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1389 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1390 order by relname, attnum;
1391  relname | attname | attinhcount | attislocal
1392 ---------+---------+-------------+------------
1393  c1      | id      |           1 | f
1394  c1      | name    |           2 | f
1395  c1      | id2     |           1 | f
1396  c1      | height  |           1 | f
1397  c1      | age     |           0 | t
1398  gc1     | id      |           1 | f
1399  gc1     | name    |           1 | f
1400  gc1     | id2     |           1 | f
1401  gc1     | height  |           1 | f
1402  gc1     | age     |           1 | f
1403  p1      | id      |           0 | t
1404  p1      | name    |           0 | t
1405  p2      | id2     |           0 | t
1406  p2      | name    |           0 | t
1407  p2      | height  |           0 | t
1408 (15 rows)
1409
1410 -- should work
1411 alter table only p1 drop column name;
1412 -- should work. Now c1.name is local and inhcount is 0.
1413 alter table p2 drop column name;
1414 -- should be rejected since its inherited
1415 alter table gc1 drop column name;
1416 ERROR:  cannot drop inherited column "name"
1417 -- should work, and drop gc1.name along
1418 alter table c1 drop column name;
1419 -- should fail: column does not exist
1420 alter table gc1 drop column name;
1421 ERROR:  column "name" of relation "gc1" does not exist
1422 -- should work and drop the attribute in all tables
1423 alter table p2 drop column height;
1424 -- IF EXISTS test
1425 create table dropColumnExists ();
1426 alter table dropColumnExists drop column non_existing; --fail
1427 ERROR:  column "non_existing" of relation "dropcolumnexists" does not exist
1428 alter table dropColumnExists drop column if exists non_existing; --succeed
1429 NOTICE:  column "non_existing" of relation "dropcolumnexists" does not exist, skipping
1430 select relname, attname, attinhcount, attislocal
1431 from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid)
1432 where relname in ('p1','p2','c1','gc1') and attnum > 0 and not attisdropped
1433 order by relname, attnum;
1434  relname | attname | attinhcount | attislocal
1435 ---------+---------+-------------+------------
1436  c1      | id      |           1 | f
1437  c1      | id2     |           1 | f
1438  c1      | age     |           0 | t
1439  gc1     | id      |           1 | f
1440  gc1     | id2     |           1 | f
1441  gc1     | age     |           1 | f
1442  p1      | id      |           0 | t
1443  p2      | id2     |           0 | t
1444 (8 rows)
1445
1446 drop table p1, p2 cascade;
1447 NOTICE:  drop cascades to 2 other objects
1448 DETAIL:  drop cascades to table c1
1449 drop cascades to table gc1
1450 -- test attinhcount tracking with merged columns
1451 create table depth0();
1452 create table depth1(c text) inherits (depth0);
1453 create table depth2() inherits (depth1);
1454 alter table depth0 add c text;
1455 NOTICE:  merging definition of column "c" for child "depth1"
1456 select attrelid::regclass, attname, attinhcount, attislocal
1457 from pg_attribute
1458 where attnum > 0 and attrelid::regclass in ('depth0', 'depth1', 'depth2')
1459 order by attrelid::regclass::text, attnum;
1460  attrelid | attname | attinhcount | attislocal
1461 ----------+---------+-------------+------------
1462  depth0   | c       |           0 | t
1463  depth1   | c       |           1 | t
1464  depth2   | c       |           1 | f
1465 (3 rows)
1466
1467 --
1468 -- Test the ALTER TABLE SET WITH/WITHOUT OIDS command
1469 --
1470 create table altstartwith (col integer) with oids;
1471 insert into altstartwith values (1);
1472 select oid > 0, * from altstartwith;
1473  ?column? | col
1474 ----------+-----
1475  t        |   1
1476 (1 row)
1477
1478 alter table altstartwith set without oids;
1479 select oid > 0, * from altstartwith; -- fails
1480 ERROR:  column "oid" does not exist
1481 LINE 1: select oid > 0, * from altstartwith;
1482                ^
1483 select * from altstartwith;
1484  col
1485 -----
1486    1
1487 (1 row)
1488
1489 alter table altstartwith set with oids;
1490 select oid > 0, * from altstartwith;
1491  ?column? | col
1492 ----------+-----
1493  t        |   1
1494 (1 row)
1495
1496 drop table altstartwith;
1497 -- Check inheritance cases
1498 create table altwithoid (col integer) with oids;
1499 -- Inherits parents oid column anyway
1500 create table altinhoid () inherits (altwithoid) without oids;
1501 insert into altinhoid values (1);
1502 select oid > 0, * from altwithoid;
1503  ?column? | col
1504 ----------+-----
1505  t        |   1
1506 (1 row)
1507
1508 select oid > 0, * from altinhoid;
1509  ?column? | col
1510 ----------+-----
1511  t        |   1
1512 (1 row)
1513
1514 alter table altwithoid set without oids;
1515 select oid > 0, * from altwithoid; -- fails
1516 ERROR:  column "oid" does not exist
1517 LINE 1: select oid > 0, * from altwithoid;
1518                ^
1519 select oid > 0, * from altinhoid; -- fails
1520 ERROR:  column "oid" does not exist
1521 LINE 1: select oid > 0, * from altinhoid;
1522                ^
1523 select * from altwithoid;
1524  col
1525 -----
1526    1
1527 (1 row)
1528
1529 select * from altinhoid;
1530  col
1531 -----
1532    1
1533 (1 row)
1534
1535 alter table altwithoid set with oids;
1536 select oid > 0, * from altwithoid;
1537  ?column? | col
1538 ----------+-----
1539  t        |   1
1540 (1 row)
1541
1542 select oid > 0, * from altinhoid;
1543  ?column? | col
1544 ----------+-----
1545  t        |   1
1546 (1 row)
1547
1548 drop table altwithoid cascade;
1549 NOTICE:  drop cascades to table altinhoid
1550 create table altwithoid (col integer) without oids;
1551 -- child can have local oid column
1552 create table altinhoid () inherits (altwithoid) with oids;
1553 insert into altinhoid values (1);
1554 select oid > 0, * from altwithoid; -- fails
1555 ERROR:  column "oid" does not exist
1556 LINE 1: select oid > 0, * from altwithoid;
1557                ^
1558 select oid > 0, * from altinhoid;
1559  ?column? | col
1560 ----------+-----
1561  t        |   1
1562 (1 row)
1563
1564 alter table altwithoid set with oids;
1565 NOTICE:  merging definition of column "oid" for child "altinhoid"
1566 select oid > 0, * from altwithoid;
1567  ?column? | col
1568 ----------+-----
1569  t        |   1
1570 (1 row)
1571
1572 select oid > 0, * from altinhoid;
1573  ?column? | col
1574 ----------+-----
1575  t        |   1
1576 (1 row)
1577
1578 -- the child's local definition should remain
1579 alter table altwithoid set without oids;
1580 select oid > 0, * from altwithoid; -- fails
1581 ERROR:  column "oid" does not exist
1582 LINE 1: select oid > 0, * from altwithoid;
1583                ^
1584 select oid > 0, * from altinhoid;
1585  ?column? | col
1586 ----------+-----
1587  t        |   1
1588 (1 row)
1589
1590 drop table altwithoid cascade;
1591 NOTICE:  drop cascades to table altinhoid
1592 -- test renumbering of child-table columns in inherited operations
1593 create table p1 (f1 int);
1594 create table c1 (f2 text, f3 int) inherits (p1);
1595 alter table p1 add column a1 int check (a1 > 0);
1596 alter table p1 add column f2 text;
1597 NOTICE:  merging definition of column "f2" for child "c1"
1598 insert into p1 values (1,2,'abc');
1599 insert into c1 values(11,'xyz',33,0); -- should fail
1600 ERROR:  new row for relation "c1" violates check constraint "p1_a1_check"
1601 DETAIL:  Failing row contains (11, xyz, 33, 0).
1602 insert into c1 values(11,'xyz',33,22);
1603 select * from p1;
1604  f1 | a1 | f2
1605 ----+----+-----
1606   1 |  2 | abc
1607  11 | 22 | xyz
1608 (2 rows)
1609
1610 update p1 set a1 = a1 + 1, f2 = upper(f2);
1611 select * from p1;
1612  f1 | a1 | f2
1613 ----+----+-----
1614   1 |  3 | ABC
1615  11 | 23 | XYZ
1616 (2 rows)
1617
1618 drop table p1 cascade;
1619 NOTICE:  drop cascades to table c1
1620 -- test that operations with a dropped column do not try to reference
1621 -- its datatype
1622 create domain mytype as text;
1623 create temp table foo (f1 text, f2 mytype, f3 text);
1624 insert into foo values('bb','cc','dd');
1625 select * from foo;
1626  f1 | f2 | f3
1627 ----+----+----
1628  bb | cc | dd
1629 (1 row)
1630
1631 drop domain mytype cascade;
1632 NOTICE:  drop cascades to table foo column f2
1633 select * from foo;
1634  f1 | f3
1635 ----+----
1636  bb | dd
1637 (1 row)
1638
1639 insert into foo values('qq','rr');
1640 select * from foo;
1641  f1 | f3
1642 ----+----
1643  bb | dd
1644  qq | rr
1645 (2 rows)
1646
1647 update foo set f3 = 'zz';
1648 select * from foo;
1649  f1 | f3
1650 ----+----
1651  bb | zz
1652  qq | zz
1653 (2 rows)
1654
1655 select f3,max(f1) from foo group by f3;
1656  f3 | max
1657 ----+-----
1658  zz | qq
1659 (1 row)
1660
1661 -- Simple tests for alter table column type
1662 alter table foo alter f1 TYPE integer; -- fails
1663 ERROR:  column "f1" cannot be cast automatically to type integer
1664 HINT:  Specify a USING expression to perform the conversion.
1665 alter table foo alter f1 TYPE varchar(10);
1666 create table anothertab (atcol1 serial8, atcol2 boolean,
1667         constraint anothertab_chk check (atcol1 <= 3));
1668 insert into anothertab (atcol1, atcol2) values (default, true);
1669 insert into anothertab (atcol1, atcol2) values (default, false);
1670 select * from anothertab;
1671  atcol1 | atcol2
1672 --------+--------
1673       1 | t
1674       2 | f
1675 (2 rows)
1676
1677 alter table anothertab alter column atcol1 type boolean; -- fails
1678 ERROR:  column "atcol1" cannot be cast automatically to type boolean
1679 HINT:  Specify a USING expression to perform the conversion.
1680 alter table anothertab alter column atcol1 type integer;
1681 select * from anothertab;
1682  atcol1 | atcol2
1683 --------+--------
1684       1 | t
1685       2 | f
1686 (2 rows)
1687
1688 insert into anothertab (atcol1, atcol2) values (45, null); -- fails
1689 ERROR:  new row for relation "anothertab" violates check constraint "anothertab_chk"
1690 DETAIL:  Failing row contains (45, null).
1691 insert into anothertab (atcol1, atcol2) values (default, null);
1692 select * from anothertab;
1693  atcol1 | atcol2
1694 --------+--------
1695       1 | t
1696       2 | f
1697       3 |
1698 (3 rows)
1699
1700 alter table anothertab alter column atcol2 type text
1701       using case when atcol2 is true then 'IT WAS TRUE'
1702                  when atcol2 is false then 'IT WAS FALSE'
1703                  else 'IT WAS NULL!' end;
1704 select * from anothertab;
1705  atcol1 |    atcol2
1706 --------+--------------
1707       1 | IT WAS TRUE
1708       2 | IT WAS FALSE
1709       3 | IT WAS NULL!
1710 (3 rows)
1711
1712 alter table anothertab alter column atcol1 type boolean
1713         using case when atcol1 % 2 = 0 then true else false end; -- fails
1714 ERROR:  default for column "atcol1" cannot be cast automatically to type boolean
1715 alter table anothertab alter column atcol1 drop default;
1716 alter table anothertab alter column atcol1 type boolean
1717         using case when atcol1 % 2 = 0 then true else false end; -- fails
1718 ERROR:  operator does not exist: boolean <= integer
1719 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
1720 alter table anothertab drop constraint anothertab_chk;
1721 alter table anothertab drop constraint anothertab_chk; -- fails
1722 ERROR:  constraint "anothertab_chk" of relation "anothertab" does not exist
1723 alter table anothertab drop constraint IF EXISTS anothertab_chk; -- succeeds
1724 NOTICE:  constraint "anothertab_chk" of relation "anothertab" does not exist, skipping
1725 alter table anothertab alter column atcol1 type boolean
1726         using case when atcol1 % 2 = 0 then true else false end;
1727 select * from anothertab;
1728  atcol1 |    atcol2
1729 --------+--------------
1730  f      | IT WAS TRUE
1731  t      | IT WAS FALSE
1732  f      | IT WAS NULL!
1733 (3 rows)
1734
1735 drop table anothertab;
1736 create table another (f1 int, f2 text);
1737 insert into another values(1, 'one');
1738 insert into another values(2, 'two');
1739 insert into another values(3, 'three');
1740 select * from another;
1741  f1 |  f2
1742 ----+-------
1743   1 | one
1744   2 | two
1745   3 | three
1746 (3 rows)
1747
1748 alter table another
1749   alter f1 type text using f2 || ' more',
1750   alter f2 type bigint using f1 * 10;
1751 select * from another;
1752      f1     | f2
1753 ------------+----
1754  one more   | 10
1755  two more   | 20
1756  three more | 30
1757 (3 rows)
1758
1759 drop table another;
1760 -- table's row type
1761 create table tab1 (a int, b text);
1762 create table tab2 (x int, y tab1);
1763 alter table tab1 alter column b type varchar; -- fails
1764 ERROR:  cannot alter table "tab1" because column "tab2.y" uses its row type
1765 -- disallow recursive containment of row types
1766 create temp table recur1 (f1 int);
1767 alter table recur1 add column f2 recur1; -- fails
1768 ERROR:  composite type recur1 cannot be made a member of itself
1769 alter table recur1 add column f2 recur1[]; -- fails
1770 ERROR:  composite type recur1 cannot be made a member of itself
1771 create domain array_of_recur1 as recur1[];
1772 alter table recur1 add column f2 array_of_recur1; -- fails
1773 ERROR:  composite type recur1 cannot be made a member of itself
1774 create temp table recur2 (f1 int, f2 recur1);
1775 alter table recur1 add column f2 recur2; -- fails
1776 ERROR:  composite type recur1 cannot be made a member of itself
1777 alter table recur1 add column f2 int;
1778 alter table recur1 alter column f2 type recur2; -- fails
1779 ERROR:  composite type recur1 cannot be made a member of itself
1780 -- SET STORAGE may need to add a TOAST table
1781 create table test_storage (a text);
1782 alter table test_storage alter a set storage plain;
1783 alter table test_storage add b int default 0; -- rewrite table to remove its TOAST table
1784 alter table test_storage alter a set storage extended; -- re-add TOAST table
1785 select reltoastrelid <> 0 as has_toast_table
1786 from pg_class
1787 where oid = 'test_storage'::regclass;
1788  has_toast_table
1789 -----------------
1790  t
1791 (1 row)
1792
1793 -- ALTER TYPE with a check constraint and a child table (bug before Nov 2012)
1794 CREATE TABLE test_inh_check (a float check (a > 10.2));
1795 CREATE TABLE test_inh_check_child() INHERITS(test_inh_check);
1796 ALTER TABLE test_inh_check ALTER COLUMN a TYPE numeric;
1797 \d test_inh_check
1798 Table "public.test_inh_check"
1799  Column |  Type   | Modifiers
1800 --------+---------+-----------
1801  a      | numeric |
1802 Check constraints:
1803     "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
1804 Number of child tables: 1 (Use \d+ to list them.)
1805
1806 \d test_inh_check_child
1807 Table "public.test_inh_check_child"
1808  Column |  Type   | Modifiers
1809 --------+---------+-----------
1810  a      | numeric |
1811 Check constraints:
1812     "test_inh_check_a_check" CHECK (a::double precision > 10.2::double precision)
1813 Inherits: test_inh_check
1814
1815 --
1816 -- lock levels
1817 --
1818 drop type lockmodes;
1819 ERROR:  type "lockmodes" does not exist
1820 create type lockmodes as enum (
1821  'AccessShareLock'
1822 ,'RowShareLock'
1823 ,'RowExclusiveLock'
1824 ,'ShareUpdateExclusiveLock'
1825 ,'ShareLock'
1826 ,'ShareRowExclusiveLock'
1827 ,'ExclusiveLock'
1828 ,'AccessExclusiveLock'
1829 );
1830 drop view my_locks;
1831 ERROR:  view "my_locks" does not exist
1832 create or replace view my_locks as
1833 select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
1834 from pg_locks l join pg_class c on l.relation = c.oid
1835 where virtualtransaction = (
1836         select virtualtransaction
1837         from pg_locks
1838         where transactionid = txid_current()::integer)
1839 and locktype = 'relation'
1840 and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
1841 and c.relname != 'my_locks'
1842 group by c.relname;
1843 create table alterlock (f1 int primary key, f2 text);
1844 begin; alter table alterlock alter column f2 set statistics 150;
1845 select * from my_locks order by 1;
1846   relname  |    max_lockmode
1847 -----------+---------------------
1848  alterlock | AccessExclusiveLock
1849 (1 row)
1850
1851 rollback;
1852 begin; alter table alterlock cluster on alterlock_pkey;
1853 select * from my_locks order by 1;
1854     relname     |    max_lockmode
1855 ----------------+---------------------
1856  alterlock      | AccessExclusiveLock
1857  alterlock_pkey | AccessExclusiveLock
1858 (2 rows)
1859
1860 commit;
1861 begin; alter table alterlock set without cluster;
1862 select * from my_locks order by 1;
1863   relname  |    max_lockmode
1864 -----------+---------------------
1865  alterlock | AccessExclusiveLock
1866 (1 row)
1867
1868 commit;
1869 begin; alter table alterlock set (fillfactor = 100);
1870 select * from my_locks order by 1;
1871   relname  |    max_lockmode
1872 -----------+---------------------
1873  alterlock | AccessExclusiveLock
1874  pg_toast  | AccessExclusiveLock
1875 (2 rows)
1876
1877 commit;
1878 begin; alter table alterlock reset (fillfactor);
1879 select * from my_locks order by 1;
1880   relname  |    max_lockmode
1881 -----------+---------------------
1882  alterlock | AccessExclusiveLock
1883  pg_toast  | AccessExclusiveLock
1884 (2 rows)
1885
1886 commit;
1887 begin; alter table alterlock set (toast.autovacuum_enabled = off);
1888 select * from my_locks order by 1;
1889   relname  |    max_lockmode
1890 -----------+---------------------
1891  alterlock | AccessExclusiveLock
1892  pg_toast  | AccessExclusiveLock
1893 (2 rows)
1894
1895 commit;
1896 begin; alter table alterlock set (autovacuum_enabled = off);
1897 select * from my_locks order by 1;
1898   relname  |    max_lockmode
1899 -----------+---------------------
1900  alterlock | AccessExclusiveLock
1901  pg_toast  | AccessExclusiveLock
1902 (2 rows)
1903
1904 commit;
1905 begin; alter table alterlock alter column f2 set (n_distinct = 1);
1906 select * from my_locks order by 1;
1907   relname  |    max_lockmode
1908 -----------+---------------------
1909  alterlock | AccessExclusiveLock
1910 (1 row)
1911
1912 rollback;
1913 begin; alter table alterlock alter column f2 set storage extended;
1914 select * from my_locks order by 1;
1915   relname  |    max_lockmode
1916 -----------+---------------------
1917  alterlock | AccessExclusiveLock
1918 (1 row)
1919
1920 rollback;
1921 begin; alter table alterlock alter column f2 set default 'x';
1922 select * from my_locks order by 1;
1923   relname  |    max_lockmode
1924 -----------+---------------------
1925  alterlock | AccessExclusiveLock
1926 (1 row)
1927
1928 rollback;
1929 -- cleanup
1930 drop table alterlock;
1931 drop view my_locks;
1932 drop type lockmodes;
1933 --
1934 -- alter function
1935 --
1936 create function test_strict(text) returns text as
1937     'select coalesce($1, ''got passed a null'');'
1938     language sql returns null on null input;
1939 select test_strict(NULL);
1940  test_strict
1941 -------------
1942
1943 (1 row)
1944
1945 alter function test_strict(text) called on null input;
1946 select test_strict(NULL);
1947     test_strict
1948 -------------------
1949  got passed a null
1950 (1 row)
1951
1952 create function non_strict(text) returns text as
1953     'select coalesce($1, ''got passed a null'');'
1954     language sql called on null input;
1955 select non_strict(NULL);
1956     non_strict
1957 -------------------
1958  got passed a null
1959 (1 row)
1960
1961 alter function non_strict(text) returns null on null input;
1962 select non_strict(NULL);
1963  non_strict
1964 ------------
1965
1966 (1 row)
1967
1968 --
1969 -- alter object set schema
1970 --
1971 create schema alter1;
1972 create schema alter2;
1973 create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
1974 create view alter1.v1 as select * from alter1.t1;
1975 create function alter1.plus1(int) returns int as 'select $1+1' language sql;
1976 create domain alter1.posint integer check (value > 0);
1977 create type alter1.ctype as (f1 int, f2 text);
1978 create function alter1.same(alter1.ctype, alter1.ctype) returns boolean language sql
1979 as 'select $1.f1 is not distinct from $2.f1 and $1.f2 is not distinct from $2.f2';
1980 create operator alter1.=(procedure = alter1.same, leftarg  = alter1.ctype, rightarg = alter1.ctype);
1981 create operator class alter1.ctype_hash_ops default for type alter1.ctype using hash as
1982   operator 1 alter1.=(alter1.ctype, alter1.ctype);
1983 create conversion alter1.ascii_to_utf8 for 'sql_ascii' to 'utf8' from ascii_to_utf8;
1984 create text search parser alter1.prs(start = prsd_start, gettoken = prsd_nexttoken, end = prsd_end, lextypes = prsd_lextype);
1985 create text search configuration alter1.cfg(parser = alter1.prs);
1986 create text search template alter1.tmpl(init = dsimple_init, lexize = dsimple_lexize);
1987 create text search dictionary alter1.dict(template = alter1.tmpl);
1988 insert into alter1.t1(f2) values(11);
1989 insert into alter1.t1(f2) values(12);
1990 alter table alter1.t1 set schema alter2;
1991 alter table alter1.v1 set schema alter2;
1992 alter function alter1.plus1(int) set schema alter2;
1993 alter domain alter1.posint set schema alter2;
1994 alter operator class alter1.ctype_hash_ops using hash set schema alter2;
1995 alter operator family alter1.ctype_hash_ops using hash set schema alter2;
1996 alter operator alter1.=(alter1.ctype, alter1.ctype) set schema alter2;
1997 alter function alter1.same(alter1.ctype, alter1.ctype) set schema alter2;
1998 alter type alter1.ctype set schema alter2;
1999 alter conversion alter1.ascii_to_utf8 set schema alter2;
2000 alter text search parser alter1.prs set schema alter2;
2001 alter text search configuration alter1.cfg set schema alter2;
2002 alter text search template alter1.tmpl set schema alter2;
2003 alter text search dictionary alter1.dict set schema alter2;
2004 -- this should succeed because nothing is left in alter1
2005 drop schema alter1;
2006 insert into alter2.t1(f2) values(13);
2007 insert into alter2.t1(f2) values(14);
2008 select * from alter2.t1;
2009  f1 | f2
2010 ----+----
2011   1 | 11
2012   2 | 12
2013   3 | 13
2014   4 | 14
2015 (4 rows)
2016
2017 select * from alter2.v1;
2018  f1 | f2
2019 ----+----
2020   1 | 11
2021   2 | 12
2022   3 | 13
2023   4 | 14
2024 (4 rows)
2025
2026 select alter2.plus1(41);
2027  plus1
2028 -------
2029     42
2030 (1 row)
2031
2032 -- clean up
2033 drop schema alter2 cascade;
2034 NOTICE:  drop cascades to 13 other objects
2035 DETAIL:  drop cascades to table alter2.t1
2036 drop cascades to view alter2.v1
2037 drop cascades to function alter2.plus1(integer)
2038 drop cascades to type alter2.posint
2039 drop cascades to operator family alter2.ctype_hash_ops for access method hash
2040 drop cascades to type alter2.ctype
2041 drop cascades to function alter2.same(alter2.ctype,alter2.ctype)
2042 drop cascades to operator alter2.=(alter2.ctype,alter2.ctype)
2043 drop cascades to conversion ascii_to_utf8
2044 drop cascades to text search parser prs
2045 drop cascades to text search configuration cfg
2046 drop cascades to text search template tmpl
2047 drop cascades to text search dictionary dict
2048 --
2049 -- composite types
2050 --
2051 CREATE TYPE test_type AS (a int);
2052 \d test_type
2053 Composite type "public.test_type"
2054  Column |  Type   | Modifiers
2055 --------+---------+-----------
2056  a      | integer |
2057
2058 ALTER TYPE nosuchtype ADD ATTRIBUTE b text; -- fails
2059 ERROR:  relation "nosuchtype" does not exist
2060 ALTER TYPE test_type ADD ATTRIBUTE b text;
2061 \d test_type
2062 Composite type "public.test_type"
2063  Column |  Type   | Modifiers
2064 --------+---------+-----------
2065  a      | integer |
2066  b      | text    |
2067
2068 ALTER TYPE test_type ADD ATTRIBUTE b text; -- fails
2069 ERROR:  column "b" of relation "test_type" already exists
2070 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE varchar;
2071 \d test_type
2072    Composite type "public.test_type"
2073  Column |       Type        | Modifiers
2074 --------+-------------------+-----------
2075  a      | integer           |
2076  b      | character varying |
2077
2078 ALTER TYPE test_type ALTER ATTRIBUTE b SET DATA TYPE integer;
2079 \d test_type
2080 Composite type "public.test_type"
2081  Column |  Type   | Modifiers
2082 --------+---------+-----------
2083  a      | integer |
2084  b      | integer |
2085
2086 ALTER TYPE test_type DROP ATTRIBUTE b;
2087 \d test_type
2088 Composite type "public.test_type"
2089  Column |  Type   | Modifiers
2090 --------+---------+-----------
2091  a      | integer |
2092
2093 ALTER TYPE test_type DROP ATTRIBUTE c; -- fails
2094 ERROR:  column "c" of relation "test_type" does not exist
2095 ALTER TYPE test_type DROP ATTRIBUTE IF EXISTS c;
2096 NOTICE:  column "c" of relation "test_type" does not exist, skipping
2097 ALTER TYPE test_type DROP ATTRIBUTE a, ADD ATTRIBUTE d boolean;
2098 \d test_type
2099 Composite type "public.test_type"
2100  Column |  Type   | Modifiers
2101 --------+---------+-----------
2102  d      | boolean |
2103
2104 ALTER TYPE test_type RENAME ATTRIBUTE a TO aa;
2105 ERROR:  column "a" does not exist
2106 ALTER TYPE test_type RENAME ATTRIBUTE d TO dd;
2107 \d test_type
2108 Composite type "public.test_type"
2109  Column |  Type   | Modifiers
2110 --------+---------+-----------
2111  dd     | boolean |
2112
2113 DROP TYPE test_type;
2114 CREATE TYPE test_type1 AS (a int, b text);
2115 CREATE TABLE test_tbl1 (x int, y test_type1);
2116 ALTER TYPE test_type1 ALTER ATTRIBUTE b TYPE varchar; -- fails
2117 ERROR:  cannot alter type "test_type1" because column "test_tbl1.y" uses it
2118 CREATE TYPE test_type2 AS (a int, b text);
2119 CREATE TABLE test_tbl2 OF test_type2;
2120 CREATE TABLE test_tbl2_subclass () INHERITS (test_tbl2);
2121 \d test_type2
2122 Composite type "public.test_type2"
2123  Column |  Type   | Modifiers
2124 --------+---------+-----------
2125  a      | integer |
2126  b      | text    |
2127
2128 \d test_tbl2
2129    Table "public.test_tbl2"
2130  Column |  Type   | Modifiers
2131 --------+---------+-----------
2132  a      | integer |
2133  b      | text    |
2134 Number of child tables: 1 (Use \d+ to list them.)
2135 Typed table of type: test_type2
2136
2137 ALTER TYPE test_type2 ADD ATTRIBUTE c text; -- fails
2138 ERROR:  cannot alter type "test_type2" because it is the type of a typed table
2139 HINT:  Use ALTER ... CASCADE to alter the typed tables too.
2140 ALTER TYPE test_type2 ADD ATTRIBUTE c text CASCADE;
2141 \d test_type2
2142 Composite type "public.test_type2"
2143  Column |  Type   | Modifiers
2144 --------+---------+-----------
2145  a      | integer |
2146  b      | text    |
2147  c      | text    |
2148
2149 \d test_tbl2
2150    Table "public.test_tbl2"
2151  Column |  Type   | Modifiers
2152 --------+---------+-----------
2153  a      | integer |
2154  b      | text    |
2155  c      | text    |
2156 Number of child tables: 1 (Use \d+ to list them.)
2157 Typed table of type: test_type2
2158
2159 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar; -- fails
2160 ERROR:  cannot alter type "test_type2" because it is the type of a typed table
2161 HINT:  Use ALTER ... CASCADE to alter the typed tables too.
2162 ALTER TYPE test_type2 ALTER ATTRIBUTE b TYPE varchar CASCADE;
2163 \d test_type2
2164    Composite type "public.test_type2"
2165  Column |       Type        | Modifiers
2166 --------+-------------------+-----------
2167  a      | integer           |
2168  b      | character varying |
2169  c      | text              |
2170
2171 \d test_tbl2
2172         Table "public.test_tbl2"
2173  Column |       Type        | Modifiers
2174 --------+-------------------+-----------
2175  a      | integer           |
2176  b      | character varying |
2177  c      | text              |
2178 Number of child tables: 1 (Use \d+ to list them.)
2179 Typed table of type: test_type2
2180
2181 ALTER TYPE test_type2 DROP ATTRIBUTE b; -- fails
2182 ERROR:  cannot alter type "test_type2" because it is the type of a typed table
2183 HINT:  Use ALTER ... CASCADE to alter the typed tables too.
2184 ALTER TYPE test_type2 DROP ATTRIBUTE b CASCADE;
2185 \d test_type2
2186 Composite type "public.test_type2"
2187  Column |  Type   | Modifiers
2188 --------+---------+-----------
2189  a      | integer |
2190  c      | text    |
2191
2192 \d test_tbl2
2193    Table "public.test_tbl2"
2194  Column |  Type   | Modifiers
2195 --------+---------+-----------
2196  a      | integer |
2197  c      | text    |
2198 Number of child tables: 1 (Use \d+ to list them.)
2199 Typed table of type: test_type2
2200
2201 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa; -- fails
2202 ERROR:  cannot alter type "test_type2" because it is the type of a typed table
2203 HINT:  Use ALTER ... CASCADE to alter the typed tables too.
2204 ALTER TYPE test_type2 RENAME ATTRIBUTE a TO aa CASCADE;
2205 \d test_type2
2206 Composite type "public.test_type2"
2207  Column |  Type   | Modifiers
2208 --------+---------+-----------
2209  aa     | integer |
2210  c      | text    |
2211
2212 \d test_tbl2
2213    Table "public.test_tbl2"
2214  Column |  Type   | Modifiers
2215 --------+---------+-----------
2216  aa     | integer |
2217  c      | text    |
2218 Number of child tables: 1 (Use \d+ to list them.)
2219 Typed table of type: test_type2
2220
2221 \d test_tbl2_subclass
2222 Table "public.test_tbl2_subclass"
2223  Column |  Type   | Modifiers
2224 --------+---------+-----------
2225  aa     | integer |
2226  c      | text    |
2227 Inherits: test_tbl2
2228
2229 DROP TABLE test_tbl2_subclass;
2230 -- This test isn't that interesting on its own, but the purpose is to leave
2231 -- behind a table to test pg_upgrade with. The table has a composite type
2232 -- column in it, and the composite type has a dropped attribute.
2233 CREATE TYPE test_type3 AS (a int);
2234 CREATE TABLE test_tbl3 (c) AS SELECT '(1)'::test_type3;
2235 ALTER TYPE test_type3 DROP ATTRIBUTE a, ADD ATTRIBUTE b int;
2236 CREATE TYPE test_type_empty AS ();
2237 DROP TYPE test_type_empty;
2238 --
2239 -- typed tables: OF / NOT OF
2240 --
2241 CREATE TYPE tt_t0 AS (z inet, x int, y numeric(8,2));
2242 ALTER TYPE tt_t0 DROP ATTRIBUTE z;
2243 CREATE TABLE tt0 (x int NOT NULL, y numeric(8,2));      -- OK
2244 CREATE TABLE tt1 (x int, y bigint);                                     -- wrong base type
2245 CREATE TABLE tt2 (x int, y numeric(9,2));                       -- wrong typmod
2246 CREATE TABLE tt3 (y numeric(8,2), x int);                       -- wrong column order
2247 CREATE TABLE tt4 (x int);                                                       -- too few columns
2248 CREATE TABLE tt5 (x int, y numeric(8,2), z int);        -- too few columns
2249 CREATE TABLE tt6 () INHERITS (tt0);                                     -- can't have a parent
2250 CREATE TABLE tt7 (x int, q text, y numeric(8,2)) WITH OIDS;
2251 ALTER TABLE tt7 DROP q;                                                         -- OK
2252 ALTER TABLE tt0 OF tt_t0;
2253 ALTER TABLE tt1 OF tt_t0;
2254 ERROR:  table "tt1" has different type for column "y"
2255 ALTER TABLE tt2 OF tt_t0;
2256 ERROR:  table "tt2" has different type for column "y"
2257 ALTER TABLE tt3 OF tt_t0;
2258 ERROR:  table has column "y" where type requires "x"
2259 ALTER TABLE tt4 OF tt_t0;
2260 ERROR:  table is missing column "y"
2261 ALTER TABLE tt5 OF tt_t0;
2262 ERROR:  table has extra column "z"
2263 ALTER TABLE tt6 OF tt_t0;
2264 ERROR:  typed tables cannot inherit
2265 ALTER TABLE tt7 OF tt_t0;
2266 CREATE TYPE tt_t1 AS (x int, y numeric(8,2));
2267 ALTER TABLE tt7 OF tt_t1;                       -- reassign an already-typed table
2268 ALTER TABLE tt7 NOT OF;
2269 \d tt7
2270         Table "public.tt7"
2271  Column |     Type     | Modifiers
2272 --------+--------------+-----------
2273  x      | integer      |
2274  y      | numeric(8,2) |
2275
2276 -- make sure we can drop a constraint on the parent but it remains on the child
2277 CREATE TABLE test_drop_constr_parent (c text CHECK (c IS NOT NULL));
2278 CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
2279 ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
2280 -- should fail
2281 INSERT INTO test_drop_constr_child (c) VALUES (NULL);
2282 ERROR:  new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
2283 DETAIL:  Failing row contains (null).
2284 DROP TABLE test_drop_constr_parent CASCADE;
2285 NOTICE:  drop cascades to table test_drop_constr_child
2286 --
2287 -- IF EXISTS test
2288 --
2289 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
2290 NOTICE:  relation "tt8" does not exist, skipping
2291 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
2292 NOTICE:  relation "tt8" does not exist, skipping
2293 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
2294 NOTICE:  relation "tt8" does not exist, skipping
2295 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
2296 NOTICE:  relation "tt8" does not exist, skipping
2297 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
2298 NOTICE:  relation "tt8" does not exist, skipping
2299 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
2300 NOTICE:  relation "tt8" does not exist, skipping
2301 CREATE TABLE tt8(a int);
2302 CREATE SCHEMA alter2;
2303 ALTER TABLE IF EXISTS tt8 ADD COLUMN f int;
2304 ALTER TABLE IF EXISTS tt8 ADD CONSTRAINT xxx PRIMARY KEY(f);
2305 ALTER TABLE IF EXISTS tt8 ADD CHECK (f BETWEEN 0 AND 10);
2306 ALTER TABLE IF EXISTS tt8 ALTER COLUMN f SET DEFAULT 0;
2307 ALTER TABLE IF EXISTS tt8 RENAME COLUMN f TO f1;
2308 ALTER TABLE IF EXISTS tt8 SET SCHEMA alter2;
2309 \d alter2.tt8
2310           Table "alter2.tt8"
2311  Column |  Type   |     Modifiers
2312 --------+---------+--------------------
2313  a      | integer |
2314  f1     | integer | not null default 0
2315 Indexes:
2316     "xxx" PRIMARY KEY, btree (f1)
2317 Check constraints:
2318     "tt8_f_check" CHECK (f1 >= 0 AND f1 <= 10)
2319
2320 DROP TABLE alter2.tt8;
2321 DROP SCHEMA alter2;
2322 -- Check that we map relation oids to filenodes and back correctly.
2323 -- Don't display all the mappings so the test output doesn't change
2324 -- all the time, but make sure we actually do test some values.
2325 SELECT
2326     SUM((mapped_oid != oid OR mapped_oid IS NULL)::int) incorrectly_mapped,
2327     count(*) > 200 have_mappings
2328 FROM (
2329     SELECT
2330         oid, reltablespace, relfilenode, relname,
2331         pg_filenode_relation(reltablespace, pg_relation_filenode(oid)) mapped_oid
2332     FROM pg_class
2333     WHERE relkind IN ('r', 'i', 'S', 't', 'm')
2334     ) mapped;
2335  incorrectly_mapped | have_mappings
2336 --------------------+---------------
2337                   0 | t
2338 (1 row)
2339
2340 -- Checks on creating and manipulation of user defined relations in
2341 -- pg_catalog.
2342 --
2343 -- XXX: It would be useful to add checks around trying to manipulate
2344 -- catalog tables, but that might have ugly consequences when run
2345 -- against an existing server with allow_system_table_mods = on.
2346 SHOW allow_system_table_mods;
2347  allow_system_table_mods
2348 -------------------------
2349  off
2350 (1 row)
2351
2352 -- disallowed because of search_path issues with pg_dump
2353 CREATE TABLE pg_catalog.new_system_table();
2354 ERROR:  permission denied to create "pg_catalog.new_system_table"
2355 DETAIL:  System catalog modifications are currently disallowed.
2356 -- instead create in public first, move to catalog
2357 CREATE TABLE new_system_table(id serial primary key, othercol text);
2358 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2359 -- XXX: it's currently impossible to move relations out of pg_catalog
2360 ALTER TABLE new_system_table SET SCHEMA public;
2361 ERROR:  cannot remove dependency on schema pg_catalog because it is a system object
2362 -- move back, will currently error out, already there
2363 ALTER TABLE new_system_table SET SCHEMA pg_catalog;
2364 ERROR:  table new_system_table is already in schema "pg_catalog"
2365 ALTER TABLE new_system_table RENAME TO old_system_table;
2366 CREATE INDEX old_system_table__othercol ON old_system_table (othercol);
2367 INSERT INTO old_system_table(othercol) VALUES ('somedata'), ('otherdata');
2368 UPDATE old_system_table SET id = -id;
2369 DELETE FROM old_system_table WHERE othercol = 'somedata';
2370 TRUNCATE old_system_table;
2371 ALTER TABLE old_system_table DROP CONSTRAINT new_system_table_pkey;
2372 ALTER TABLE old_system_table DROP COLUMN othercol;
2373 DROP TABLE old_system_table;