src/test/regress/sql/constraints.sql

   1 --
   2 -- CONSTRAINTS
   3 -- Constraints can be specified with:
   4 --  - DEFAULT clause
   5 --  - CHECK clauses
   6 --  - PRIMARY KEY clauses
   7 --  - UNIQUE clauses
   8 --  - EXCLUDE clauses
   9 --
  10
  11 -- directory paths are passed to us in environment variables
  12 \getenv abs_srcdir PG_ABS_SRCDIR
  13
  14 --
  15 -- DEFAULT syntax
  16 --
  17
  18 CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
  19         x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
  20
  21 INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
  22 INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
  23 INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
  24 INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
  25 INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
  26
  27 SELECT * FROM DEFAULT_TBL;
  28
  29 CREATE SEQUENCE DEFAULT_SEQ;
  30
  31 CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
  32         i2 int DEFAULT nextval('default_seq'));
  33
  34 INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
  35 INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
  36 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
  37 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
  38
  39 SELECT * FROM DEFAULTEXPR_TBL;
  40
  41 -- syntax errors
  42 --  test for extraneous comma
  43 CREATE TABLE error_tbl (i int DEFAULT (100, ));
  44 --  this will fail because gram.y uses b_expr not a_expr for defaults,
  45 --  to avoid a shift/reduce conflict that arises from NOT NULL being
  46 --  part of the column definition syntax:
  47 CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
  48 --  this should work, however:
  49 CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
  50
  51 DROP TABLE error_tbl;
  52
  53 --
  54 -- CHECK syntax
  55 --
  56
  57 CREATE TABLE CHECK_TBL (x int,
  58         CONSTRAINT CHECK_CON CHECK (x > 3));
  59
  60 INSERT INTO CHECK_TBL VALUES (5);
  61 INSERT INTO CHECK_TBL VALUES (4);
  62 INSERT INTO CHECK_TBL VALUES (3);
  63 INSERT INTO CHECK_TBL VALUES (2);
  64 INSERT INTO CHECK_TBL VALUES (6);
  65 INSERT INTO CHECK_TBL VALUES (1);
  66
  67 SELECT * FROM CHECK_TBL;
  68
  69 CREATE SEQUENCE CHECK_SEQ;
  70
  71 CREATE TABLE CHECK2_TBL (x int, y text, z int,
  72         CONSTRAINT SEQUENCE_CON
  73         CHECK (x > 3 and y <> 'check failed' and z < 8));
  74
  75 INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
  76 INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
  77 INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
  78 INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
  79 INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
  80 INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
  81
  82 SELECT * from CHECK2_TBL;
  83
  84 --
  85 -- Check constraints on INSERT
  86 --
  87
  88 CREATE SEQUENCE INSERT_SEQ;
  89
  90 CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
  91         y TEXT DEFAULT '-NULL-',
  92         z INT DEFAULT -1 * currval('insert_seq'),
  93         CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
  94         CHECK (x + z = 0));
  95
  96 INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
  97
  98 SELECT * FROM INSERT_TBL;
  99
 100 SELECT 'one' AS one, nextval('insert_seq');
 101
 102 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 103 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 104 INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
 105 INSERT INTO INSERT_TBL(z,x) VALUES (-7,  7);
 106 INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
 107 INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
 108 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 109
 110 SELECT * FROM INSERT_TBL;
 111
 112 INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
 113 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
 114 INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
 115 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 116
 117 SELECT * FROM INSERT_TBL;
 118
 119 SELECT 'seven' AS one, nextval('insert_seq');
 120
 121 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 122
 123 SELECT 'eight' AS one, currval('insert_seq');
 124
 125 -- According to SQL, it is OK to insert a record that gives rise to NULL
 126 -- constraint-condition results.  Postgres used to reject this, but it
 127 -- was wrong:
 128 INSERT INTO INSERT_TBL VALUES (null, null, null);
 129
 130 SELECT * FROM INSERT_TBL;
 131
 132 --
 133 -- Check constraints on system columns
 134 --
 135
 136 CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
 137                   altitude int,
 138                   CHECK (NOT (is_capital AND tableoid::regclass::text = 'sys_col_check_tbl')));
 139
 140 INSERT INTO SYS_COL_CHECK_TBL VALUES ('Seattle', 'Washington', false, 100);
 141 INSERT INTO SYS_COL_CHECK_TBL VALUES ('Olympia', 'Washington', true, 100);
 142
 143 SELECT *, tableoid::regclass::text FROM SYS_COL_CHECK_TBL;
 144
 145 DROP TABLE SYS_COL_CHECK_TBL;
 146
 147 --
 148 -- Check constraints on system columns other then TableOid should return error
 149 --
 150 CREATE TABLE SYS_COL_CHECK_TBL (city text, state text, is_capital bool,
 151                   altitude int,
 152                                   CHECK (NOT (is_capital AND ctid::text = 'sys_col_check_tbl')));
 153
 154 --
 155 -- Check inheritance of defaults and constraints
 156 --
 157
 158 CREATE TABLE INSERT_CHILD (cx INT default 42,
 159         cy INT CHECK (cy > x))
 160         INHERITS (INSERT_TBL);
 161
 162 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
 163 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
 164 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
 165 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
 166
 167 SELECT * FROM INSERT_CHILD;
 168
 169 DROP TABLE INSERT_CHILD;
 170
 171 --
 172 -- Check NO INHERIT type of constraints and inheritance
 173 --
 174
 175 CREATE TABLE ATACC1 (TEST INT
 176         CHECK (TEST > 0) NO INHERIT);
 177
 178 CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1);
 179 -- check constraint is not there on child
 180 INSERT INTO ATACC2 (TEST) VALUES (-3);
 181 -- check constraint is there on parent
 182 INSERT INTO ATACC1 (TEST) VALUES (-3);
 183 DROP TABLE ATACC1 CASCADE;
 184
 185 CREATE TABLE ATACC1 (TEST INT, TEST2 INT
 186         CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT);
 187
 188 CREATE TABLE ATACC2 () INHERITS (ATACC1);
 189 -- check constraint is there on child
 190 INSERT INTO ATACC2 (TEST) VALUES (-3);
 191 -- check constraint is there on parent
 192 INSERT INTO ATACC1 (TEST) VALUES (-3);
 193 -- check constraint is not there on child
 194 INSERT INTO ATACC2 (TEST2) VALUES (3);
 195 -- check constraint is there on parent
 196 INSERT INTO ATACC1 (TEST2) VALUES (3);
 197 DROP TABLE ATACC1 CASCADE;
 198
 199 --
 200 -- Check constraints on INSERT INTO
 201 --
 202
 203 DELETE FROM INSERT_TBL;
 204
 205 ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 206
 207 CREATE TEMP TABLE tmp (xd INT, yd TEXT, zd INT);
 208
 209 INSERT INTO tmp VALUES (null, 'Y', null);
 210 INSERT INTO tmp VALUES (5, '!check failed', null);
 211 INSERT INTO tmp VALUES (null, 'try again', null);
 212 INSERT INTO INSERT_TBL(y) select yd from tmp;
 213
 214 SELECT * FROM INSERT_TBL;
 215
 216 INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
 217 INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
 218 INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
 219
 220 SELECT * FROM INSERT_TBL;
 221
 222 DROP TABLE tmp;
 223
 224 --
 225 -- Check constraints on UPDATE
 226 --
 227
 228 UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
 229 UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
 230 UPDATE INSERT_TBL SET x = -z, z = -x;
 231 UPDATE INSERT_TBL SET x = z, z = x;
 232
 233 SELECT * FROM INSERT_TBL;
 234
 235 -- DROP TABLE INSERT_TBL;
 236
 237 --
 238 -- Check constraints on COPY FROM
 239 --
 240
 241 CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
 242         CONSTRAINT COPY_CON
 243         CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
 244
 245 \set filename :abs_srcdir '/data/constro.data'
 246 COPY COPY_TBL FROM :'filename';
 247
 248 SELECT * FROM COPY_TBL;
 249
 250 \set filename :abs_srcdir '/data/constrf.data'
 251 COPY COPY_TBL FROM :'filename';
 252
 253 SELECT * FROM COPY_TBL;
 254
 255 --
 256 -- Primary keys
 257 --
 258
 259 CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
 260
 261 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 262 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 263 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 264 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 265 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 266 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 267
 268 SELECT * FROM PRIMARY_TBL;
 269
 270 DROP TABLE PRIMARY_TBL;
 271
 272 CREATE TABLE PRIMARY_TBL (i int, t text,
 273         PRIMARY KEY(i,t));
 274
 275 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 276 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 277 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 278 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 279 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 280 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 281
 282 SELECT * FROM PRIMARY_TBL;
 283
 284 DROP TABLE PRIMARY_TBL;
 285
 286 --
 287 -- Unique keys
 288 --
 289
 290 CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
 291
 292 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 293 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 294 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 295 INSERT INTO UNIQUE_TBL VALUES (4, 'four');
 296 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 297 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 298 INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
 299
 300 INSERT INTO UNIQUE_TBL VALUES (5, 'five-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'five-upsert-update';
 301 INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'six-upsert-update';
 302 -- should fail
 303 INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) DO UPDATE SET t = 'fails';
 304
 305 SELECT * FROM UNIQUE_TBL;
 306
 307 DROP TABLE UNIQUE_TBL;
 308
 309 CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
 310
 311 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 312 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 313 INSERT INTO UNIQUE_TBL VALUES (1, 'three');  -- fail
 314 INSERT INTO UNIQUE_TBL VALUES (4, 'four');
 315 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 316 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 317 INSERT INTO UNIQUE_TBL (t) VALUES ('seven');  -- fail
 318 INSERT INTO UNIQUE_TBL (t) VALUES ('eight') ON CONFLICT DO NOTHING;  -- no-op
 319
 320 SELECT * FROM UNIQUE_TBL;
 321
 322 DROP TABLE UNIQUE_TBL;
 323
 324 CREATE TABLE UNIQUE_TBL (i int, t text,
 325         UNIQUE(i,t));
 326
 327 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 328 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 329 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 330 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 331 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 332 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 333
 334 SELECT * FROM UNIQUE_TBL;
 335
 336 DROP TABLE UNIQUE_TBL;
 337
 338 --
 339 -- Deferrable unique constraints
 340 --
 341
 342 CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text);
 343
 344 INSERT INTO unique_tbl VALUES (0, 'one');
 345 INSERT INTO unique_tbl VALUES (1, 'two');
 346 INSERT INTO unique_tbl VALUES (2, 'tree');
 347 INSERT INTO unique_tbl VALUES (3, 'four');
 348 INSERT INTO unique_tbl VALUES (4, 'five');
 349
 350 BEGIN;
 351
 352 -- default is immediate so this should fail right away
 353 UPDATE unique_tbl SET i = 1 WHERE i = 0;
 354
 355 ROLLBACK;
 356
 357 -- check is done at end of statement, so this should succeed
 358 UPDATE unique_tbl SET i = i+1;
 359
 360 SELECT * FROM unique_tbl;
 361
 362 -- explicitly defer the constraint
 363 BEGIN;
 364
 365 SET CONSTRAINTS unique_tbl_i_key DEFERRED;
 366
 367 INSERT INTO unique_tbl VALUES (3, 'three');
 368 DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again
 369
 370 COMMIT; -- should succeed
 371
 372 SELECT * FROM unique_tbl;
 373
 374 -- try adding an initially deferred constraint
 375 ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key;
 376 ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key
 377         UNIQUE (i) DEFERRABLE INITIALLY DEFERRED;
 378
 379 BEGIN;
 380
 381 INSERT INTO unique_tbl VALUES (1, 'five');
 382 INSERT INTO unique_tbl VALUES (5, 'one');
 383 UPDATE unique_tbl SET i = 4 WHERE i = 2;
 384 UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four';
 385 DELETE FROM unique_tbl WHERE i = 1 AND t = 'one';
 386 DELETE FROM unique_tbl WHERE i = 5 AND t = 'five';
 387
 388 COMMIT;
 389
 390 SELECT * FROM unique_tbl;
 391
 392 -- should fail at commit-time
 393 BEGIN;
 394 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
 395 COMMIT; -- should fail
 396
 397 -- make constraint check immediate
 398 BEGIN;
 399
 400 SET CONSTRAINTS ALL IMMEDIATE;
 401
 402 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail
 403
 404 COMMIT;
 405
 406 -- forced check when SET CONSTRAINTS is called
 407 BEGIN;
 408
 409 SET CONSTRAINTS ALL DEFERRED;
 410
 411 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
 412
 413 SET CONSTRAINTS ALL IMMEDIATE; -- should fail
 414
 415 COMMIT;
 416
 417 -- test deferrable UNIQUE with a partitioned table
 418 CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i);
 419 CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10);
 420 CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30);
 421 SELECT conname, conrelid::regclass FROM pg_constraint
 422   WHERE conname LIKE 'parted_uniq%' ORDER BY conname;
 423 BEGIN;
 424 INSERT INTO parted_uniq_tbl VALUES (1);
 425 SAVEPOINT f;
 426 INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation
 427 ROLLBACK TO f;
 428 SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED;
 429 INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit
 430 COMMIT;
 431 DROP TABLE parted_uniq_tbl;
 432
 433 -- test naming a constraint in a partition when a conflict exists
 434 CREATE TABLE parted_fk_naming (
 435     id bigint NOT NULL default 1,
 436     id_abc bigint,
 437     CONSTRAINT dummy_constr FOREIGN KEY (id_abc)
 438         REFERENCES parted_fk_naming (id),
 439     PRIMARY KEY (id)
 440 )
 441 PARTITION BY LIST (id);
 442 CREATE TABLE parted_fk_naming_1 (
 443     id bigint NOT NULL default 1,
 444     id_abc bigint,
 445     PRIMARY KEY (id),
 446     CONSTRAINT dummy_constr CHECK (true)
 447 );
 448 ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN ('1');
 449 SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
 450 DROP TABLE parted_fk_naming;
 451
 452 --
 453 -- Test various ways to create primary keys on partitions, linked to unique
 454 -- indexes (without constraints) on the partitioned table.  Ideally these should
 455 -- fail, but we don't dare change released behavior, so instead cope with it at
 456 -- DETACH time.
 457 CREATE TEMP TABLE t (a integer, b integer) PARTITION BY HASH (a, b);
 458 CREATE TEMP TABLE tp (a integer, b integer, PRIMARY KEY (a, b), UNIQUE (b, a));
 459 ALTER TABLE t ATTACH PARTITION tp FOR VALUES WITH (MODULUS 1, REMAINDER 0);
 460 CREATE UNIQUE INDEX t_a_idx ON t (a, b);
 461 CREATE UNIQUE INDEX t_b_idx ON t (b, a);
 462 ALTER INDEX t_a_idx ATTACH PARTITION tp_pkey;
 463 ALTER INDEX t_b_idx ATTACH PARTITION tp_b_a_key;
 464 SELECT conname, conparentid, conislocal, coninhcount
 465   FROM pg_constraint WHERE conname IN ('tp_pkey', 'tp_b_a_key')
 466   ORDER BY conname DESC;
 467 ALTER TABLE t DETACH PARTITION tp;
 468 DROP TABLE t, tp;
 469
 470 CREATE TEMP TABLE t (a integer) PARTITION BY LIST (a);
 471 CREATE TEMP TABLE tp (a integer PRIMARY KEY);
 472 CREATE UNIQUE INDEX t_a_idx ON t (a);
 473 ALTER TABLE t ATTACH PARTITION tp FOR VALUES IN (1);
 474 ALTER TABLE t DETACH PARTITION tp;
 475 DROP TABLE t, tp;
 476
 477 CREATE TEMP TABLE t (a integer) PARTITION BY LIST (a);
 478 CREATE TEMP TABLE tp (a integer PRIMARY KEY);
 479 CREATE UNIQUE INDEX t_a_idx ON ONLY t (a);
 480 ALTER TABLE t ATTACH PARTITION tp FOR VALUES IN (1);
 481 ALTER TABLE t DETACH PARTITION tp;
 482 DROP TABLE t, tp;
 483
 484 CREATE TABLE regress_constr_partitioned (a integer) PARTITION BY LIST (a);
 485 CREATE TABLE regress_constr_partition1 PARTITION OF regress_constr_partitioned FOR VALUES IN (1);
 486 ALTER TABLE regress_constr_partition1 ADD PRIMARY KEY (a);
 487 CREATE UNIQUE INDEX ON regress_constr_partitioned (a);
 488 BEGIN;
 489 ALTER TABLE regress_constr_partitioned DETACH PARTITION regress_constr_partition1;
 490 ROLLBACK;
 491 --  Leave this one in funny state for pg_upgrade testing
 492
 493 -- test a HOT update that invalidates the conflicting tuple.
 494 -- the trigger should still fire and catch the violation
 495
 496 BEGIN;
 497
 498 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
 499 UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three';
 500
 501 COMMIT; -- should fail
 502
 503 SELECT * FROM unique_tbl;
 504
 505 -- test a HOT update that modifies the newly inserted tuple,
 506 -- but should succeed because we then remove the other conflicting tuple.
 507
 508 BEGIN;
 509
 510 INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now
 511 UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree';
 512 DELETE FROM unique_tbl WHERE t = 'three';
 513
 514 SELECT * FROM unique_tbl;
 515
 516 COMMIT;
 517
 518 SELECT * FROM unique_tbl;
 519
 520 DROP TABLE unique_tbl;
 521
 522 --
 523 -- EXCLUDE constraints
 524 --
 525
 526 CREATE TABLE circles (
 527   c1 CIRCLE,
 528   c2 TEXT,
 529   EXCLUDE USING gist
 530     (c1 WITH &&, (c2::circle) WITH &&)
 531     WHERE (circle_center(c1) <> '(0,0)')
 532 );
 533
 534 -- these should succeed because they don't match the index predicate
 535 INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
 536 INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>');
 537
 538 -- succeed
 539 INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');
 540 -- fail, overlaps
 541 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>');
 542 -- succeed, because violation is ignored
 543 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
 544   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING;
 545 -- fail, because DO UPDATE variant requires unique index
 546 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>')
 547   ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2;
 548 -- succeed because c1 doesn't overlap
 549 INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
 550 -- succeed because c2 doesn't overlap
 551 INSERT INTO circles VALUES('<(20,20), 10>', '<(10,10), 5>');
 552
 553 -- should fail on existing data without the WHERE clause
 554 ALTER TABLE circles ADD EXCLUDE USING gist
 555   (c1 WITH &&, (c2::circle) WITH &&);
 556
 557 -- try reindexing an existing constraint
 558 REINDEX INDEX circles_c1_c2_excl;
 559
 560 DROP TABLE circles;
 561
 562 -- Check deferred exclusion constraint
 563
 564 CREATE TABLE deferred_excl (
 565   f1 int,
 566   f2 int,
 567   CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
 568 );
 569
 570 INSERT INTO deferred_excl VALUES(1);
 571 INSERT INTO deferred_excl VALUES(2);
 572 INSERT INTO deferred_excl VALUES(1); -- fail
 573 INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail
 574 BEGIN;
 575 INSERT INTO deferred_excl VALUES(2); -- no fail here
 576 COMMIT; -- should fail here
 577 BEGIN;
 578 INSERT INTO deferred_excl VALUES(3);
 579 INSERT INTO deferred_excl VALUES(3); -- no fail here
 580 COMMIT; -- should fail here
 581
 582 -- bug #13148: deferred constraint versus HOT update
 583 BEGIN;
 584 INSERT INTO deferred_excl VALUES(2, 1); -- no fail here
 585 DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row
 586 UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2;
 587 COMMIT; -- should not fail
 588
 589 SELECT * FROM deferred_excl;
 590
 591 ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
 592
 593 -- This should fail, but worth testing because of HOT updates
 594 UPDATE deferred_excl SET f1 = 3;
 595
 596 ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
 597
 598 DROP TABLE deferred_excl;
 599
 600 -- Comments
 601 -- Setup a low-level role to enforce non-superuser checks.
 602 CREATE ROLE regress_constraint_comments;
 603 SET SESSION AUTHORIZATION regress_constraint_comments;
 604
 605 CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0));
 606 CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0);
 607
 608 COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment';
 609 COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
 610
 611 -- no such constraint
 612 COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment';
 613 COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
 614
 615 -- no such table/domain
 616 COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment';
 617 COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment';
 618
 619 COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL;
 620 COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL;
 621
 622 -- unauthorized user
 623 RESET SESSION AUTHORIZATION;
 624 CREATE ROLE regress_constraint_comments_noaccess;
 625 SET SESSION AUTHORIZATION regress_constraint_comments_noaccess;
 626 COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'no, the comment';
 627 COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'no, another comment';
 628 RESET SESSION AUTHORIZATION;
 629
 630 DROP TABLE constraint_comments_tbl;
 631 DROP DOMAIN constraint_comments_dom;
 632
 633 DROP ROLE regress_constraint_comments;
 634 DROP ROLE regress_constraint_comments_noaccess;