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