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 231-- 232-- Check inheritance of defaults and constraints 233-- 234CREATE TABLE INSERT_CHILD (cx INT default 42, 235 cy INT CHECK (cy > x)) 236 INHERITS (INSERT_TBL); 237INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); 238INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); 239ERROR: new row for relation "insert_child" violates check constraint "insert_child_check" 240DETAIL: Failing row contains (7, -NULL-, -7, 42, 6). 241INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); 242ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_check" 243DETAIL: Failing row contains (6, -NULL-, -7, 42, 7). 244INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7); 245ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_con" 246DETAIL: Failing row contains (6, check failed, -6, 42, 7). 247SELECT * FROM INSERT_CHILD; 248 x | y | z | cx | cy 249---+--------+----+----+---- 250 7 | -NULL- | -7 | 42 | 11 251(1 row) 252 253DROP TABLE INSERT_CHILD; 254-- 255-- Check NO INHERIT type of constraints and inheritance 256-- 257CREATE TABLE ATACC1 (TEST INT 258 CHECK (TEST > 0) NO INHERIT); 259CREATE TABLE ATACC2 (TEST2 INT) INHERITS (ATACC1); 260-- check constraint is not there on child 261INSERT INTO ATACC2 (TEST) VALUES (-3); 262-- check constraint is there on parent 263INSERT INTO ATACC1 (TEST) VALUES (-3); 264ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check" 265DETAIL: Failing row contains (-3). 266DROP TABLE ATACC1 CASCADE; 267NOTICE: drop cascades to table atacc2 268CREATE TABLE ATACC1 (TEST INT, TEST2 INT 269 CHECK (TEST > 0), CHECK (TEST2 > 10) NO INHERIT); 270CREATE TABLE ATACC2 () INHERITS (ATACC1); 271-- check constraint is there on child 272INSERT INTO ATACC2 (TEST) VALUES (-3); 273ERROR: new row for relation "atacc2" violates check constraint "atacc1_test_check" 274DETAIL: Failing row contains (-3, null). 275-- check constraint is there on parent 276INSERT INTO ATACC1 (TEST) VALUES (-3); 277ERROR: new row for relation "atacc1" violates check constraint "atacc1_test_check" 278DETAIL: Failing row contains (-3, null). 279-- check constraint is not there on child 280INSERT INTO ATACC2 (TEST2) VALUES (3); 281-- check constraint is there on parent 282INSERT INTO ATACC1 (TEST2) VALUES (3); 283ERROR: new row for relation "atacc1" violates check constraint "atacc1_test2_check" 284DETAIL: Failing row contains (null, 3). 285DROP TABLE ATACC1 CASCADE; 286NOTICE: drop cascades to table atacc2 287-- 288-- Check constraints on INSERT INTO 289-- 290DELETE FROM INSERT_TBL; 291ALTER SEQUENCE INSERT_SEQ RESTART WITH 4; 292CREATE TABLE tmp (xd INT, yd TEXT, zd INT); 293INSERT INTO tmp VALUES (null, 'Y', null); 294INSERT INTO tmp VALUES (5, '!check failed', null); 295INSERT INTO tmp VALUES (null, 'try again', null); 296INSERT INTO INSERT_TBL(y) select yd from tmp; 297SELECT '' AS three, * FROM INSERT_TBL; 298 three | x | y | z 299-------+---+---------------+---- 300 | 4 | Y | -4 301 | 5 | !check failed | -5 302 | 6 | try again | -6 303(3 rows) 304 305INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again'; 306INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again'; 307INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again'; 308ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con" 309DETAIL: Failing row contains (8, try again, -8). 310SELECT '' AS four, * FROM INSERT_TBL; 311 four | x | y | z 312------+---+---------------+---- 313 | 4 | Y | -4 314 | 5 | !check failed | -5 315 | 6 | try again | -6 316 | | try again | 317 | 7 | try again | -7 318(5 rows) 319 320DROP TABLE tmp; 321-- 322-- Check constraints on UPDATE 323-- 324UPDATE INSERT_TBL SET x = NULL WHERE x = 5; 325UPDATE INSERT_TBL SET x = 6 WHERE x = 6; 326UPDATE INSERT_TBL SET x = -z, z = -x; 327UPDATE INSERT_TBL SET x = z, z = x; 328ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con" 329DETAIL: Failing row contains (-4, Y, 4). 330SELECT * FROM INSERT_TBL; 331 x | y | z 332---+---------------+---- 333 4 | Y | -4 334 | try again | 335 7 | try again | -7 336 5 | !check failed | 337 6 | try again | -6 338(5 rows) 339 340-- DROP TABLE INSERT_TBL; 341-- 342-- Check constraints on COPY FROM 343-- 344CREATE TABLE COPY_TBL (x INT, y TEXT, z INT, 345 CONSTRAINT COPY_CON 346 CHECK (x > 3 AND y <> 'check failed' AND x < 7 )); 347COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data'; 348SELECT '' AS two, * FROM COPY_TBL; 349 two | x | y | z 350-----+---+---------------+--- 351 | 4 | !check failed | 5 352 | 6 | OK | 4 353(2 rows) 354 355COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data'; 356ERROR: new row for relation "copy_tbl" violates check constraint "copy_con" 357DETAIL: Failing row contains (7, check failed, 6). 358CONTEXT: COPY copy_tbl, line 2: "7 check failed 6" 359SELECT * FROM COPY_TBL; 360 x | y | z 361---+---------------+--- 362 4 | !check failed | 5 363 6 | OK | 4 364(2 rows) 365 366-- 367-- Primary keys 368-- 369CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text); 370INSERT INTO PRIMARY_TBL VALUES (1, 'one'); 371INSERT INTO PRIMARY_TBL VALUES (2, 'two'); 372INSERT INTO PRIMARY_TBL VALUES (1, 'three'); 373ERROR: duplicate key value violates unique constraint "primary_tbl_pkey" 374DETAIL: Key (i)=(1) already exists. 375INSERT INTO PRIMARY_TBL VALUES (4, 'three'); 376INSERT INTO PRIMARY_TBL VALUES (5, 'one'); 377INSERT INTO PRIMARY_TBL (t) VALUES ('six'); 378ERROR: null value in column "i" violates not-null constraint 379DETAIL: Failing row contains (null, six). 380SELECT '' AS four, * FROM PRIMARY_TBL; 381 four | i | t 382------+---+------- 383 | 1 | one 384 | 2 | two 385 | 4 | three 386 | 5 | one 387(4 rows) 388 389DROP TABLE PRIMARY_TBL; 390CREATE TABLE PRIMARY_TBL (i int, t text, 391 PRIMARY KEY(i,t)); 392INSERT INTO PRIMARY_TBL VALUES (1, 'one'); 393INSERT INTO PRIMARY_TBL VALUES (2, 'two'); 394INSERT INTO PRIMARY_TBL VALUES (1, 'three'); 395INSERT INTO PRIMARY_TBL VALUES (4, 'three'); 396INSERT INTO PRIMARY_TBL VALUES (5, 'one'); 397INSERT INTO PRIMARY_TBL (t) VALUES ('six'); 398ERROR: null value in column "i" violates not-null constraint 399DETAIL: Failing row contains (null, six). 400SELECT '' AS three, * FROM PRIMARY_TBL; 401 three | i | t 402-------+---+------- 403 | 1 | one 404 | 2 | two 405 | 1 | three 406 | 4 | three 407 | 5 | one 408(5 rows) 409 410DROP TABLE PRIMARY_TBL; 411-- 412-- Unique keys 413-- 414CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text); 415INSERT INTO UNIQUE_TBL VALUES (1, 'one'); 416INSERT INTO UNIQUE_TBL VALUES (2, 'two'); 417INSERT INTO UNIQUE_TBL VALUES (1, 'three'); 418ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 419DETAIL: Key (i)=(1) already exists. 420INSERT INTO UNIQUE_TBL VALUES (4, 'four'); 421INSERT INTO UNIQUE_TBL VALUES (5, 'one'); 422INSERT INTO UNIQUE_TBL (t) VALUES ('six'); 423INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); 424INSERT INTO UNIQUE_TBL VALUES (5, 'five-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'five-upsert-update'; 425INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'six-upsert-update'; 426-- should fail 427INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) DO UPDATE SET t = 'fails'; 428ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time 429HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. 430SELECT '' AS five, * FROM UNIQUE_TBL; 431 five | i | t 432------+---+-------------------- 433 | 1 | one 434 | 2 | two 435 | 4 | four 436 | | six 437 | | seven 438 | 5 | five-upsert-update 439 | 6 | six-upsert-insert 440(7 rows) 441 442DROP TABLE UNIQUE_TBL; 443CREATE TABLE UNIQUE_TBL (i int, t text, 444 UNIQUE(i,t)); 445INSERT INTO UNIQUE_TBL VALUES (1, 'one'); 446INSERT INTO UNIQUE_TBL VALUES (2, 'two'); 447INSERT INTO UNIQUE_TBL VALUES (1, 'three'); 448INSERT INTO UNIQUE_TBL VALUES (1, 'one'); 449ERROR: duplicate key value violates unique constraint "unique_tbl_i_t_key" 450DETAIL: Key (i, t)=(1, one) already exists. 451INSERT INTO UNIQUE_TBL VALUES (5, 'one'); 452INSERT INTO UNIQUE_TBL (t) VALUES ('six'); 453SELECT '' AS five, * FROM UNIQUE_TBL; 454 five | i | t 455------+---+------- 456 | 1 | one 457 | 2 | two 458 | 1 | three 459 | 5 | one 460 | | six 461(5 rows) 462 463DROP TABLE UNIQUE_TBL; 464-- 465-- Deferrable unique constraints 466-- 467CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text); 468INSERT INTO unique_tbl VALUES (0, 'one'); 469INSERT INTO unique_tbl VALUES (1, 'two'); 470INSERT INTO unique_tbl VALUES (2, 'tree'); 471INSERT INTO unique_tbl VALUES (3, 'four'); 472INSERT INTO unique_tbl VALUES (4, 'five'); 473BEGIN; 474-- default is immediate so this should fail right away 475UPDATE unique_tbl SET i = 1 WHERE i = 0; 476ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 477DETAIL: Key (i)=(1) already exists. 478ROLLBACK; 479-- check is done at end of statement, so this should succeed 480UPDATE unique_tbl SET i = i+1; 481SELECT * FROM unique_tbl; 482 i | t 483---+------ 484 1 | one 485 2 | two 486 3 | tree 487 4 | four 488 5 | five 489(5 rows) 490 491-- explicitly defer the constraint 492BEGIN; 493SET CONSTRAINTS unique_tbl_i_key DEFERRED; 494INSERT INTO unique_tbl VALUES (3, 'three'); 495DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again 496COMMIT; -- should succeed 497SELECT * FROM unique_tbl; 498 i | t 499---+------- 500 1 | one 501 2 | two 502 4 | four 503 5 | five 504 3 | three 505(5 rows) 506 507-- try adding an initially deferred constraint 508ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; 509ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key 510 UNIQUE (i) DEFERRABLE INITIALLY DEFERRED; 511BEGIN; 512INSERT INTO unique_tbl VALUES (1, 'five'); 513INSERT INTO unique_tbl VALUES (5, 'one'); 514UPDATE unique_tbl SET i = 4 WHERE i = 2; 515UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four'; 516DELETE FROM unique_tbl WHERE i = 1 AND t = 'one'; 517DELETE FROM unique_tbl WHERE i = 5 AND t = 'five'; 518COMMIT; 519SELECT * FROM unique_tbl; 520 i | t 521---+------- 522 3 | three 523 1 | five 524 5 | one 525 4 | two 526 2 | four 527(5 rows) 528 529-- should fail at commit-time 530BEGIN; 531INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now 532COMMIT; -- should fail 533ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 534DETAIL: Key (i)=(3) already exists. 535-- make constraint check immediate 536BEGIN; 537SET CONSTRAINTS ALL IMMEDIATE; 538INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail 539ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 540DETAIL: Key (i)=(3) already exists. 541COMMIT; 542-- forced check when SET CONSTRAINTS is called 543BEGIN; 544SET CONSTRAINTS ALL DEFERRED; 545INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now 546SET CONSTRAINTS ALL IMMEDIATE; -- should fail 547ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 548DETAIL: Key (i)=(3) already exists. 549COMMIT; 550-- test a HOT update that invalidates the conflicting tuple. 551-- the trigger should still fire and catch the violation 552BEGIN; 553INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now 554UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three'; 555COMMIT; -- should fail 556ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 557DETAIL: Key (i)=(3) already exists. 558SELECT * FROM unique_tbl; 559 i | t 560---+------- 561 3 | three 562 1 | five 563 5 | one 564 4 | two 565 2 | four 566(5 rows) 567 568-- test a HOT update that modifies the newly inserted tuple, 569-- but should succeed because we then remove the other conflicting tuple. 570BEGIN; 571INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now 572UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree'; 573DELETE FROM unique_tbl WHERE t = 'three'; 574SELECT * FROM unique_tbl; 575 i | t 576---+-------- 577 1 | five 578 5 | one 579 4 | two 580 2 | four 581 3 | threex 582(5 rows) 583 584COMMIT; 585SELECT * FROM unique_tbl; 586 i | t 587---+-------- 588 1 | five 589 5 | one 590 4 | two 591 2 | four 592 3 | threex 593(5 rows) 594 595DROP TABLE unique_tbl; 596-- 597-- EXCLUDE constraints 598-- 599CREATE TABLE circles ( 600 c1 CIRCLE, 601 c2 TEXT, 602 EXCLUDE USING gist 603 (c1 WITH &&, (c2::circle) WITH &&) 604 WHERE (circle_center(c1) <> '(0,0)') 605); 606-- these should succeed because they don't match the index predicate 607INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); 608INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>'); 609-- succeed 610INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); 611-- fail, overlaps 612INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>'); 613ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_excl" 614DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>). 615-- succeed, because violation is ignored 616INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') 617 ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING; 618-- fail, because DO UPDATE variant requires unique index 619INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') 620 ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; 621ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints 622-- succeed because c1 doesn't overlap 623INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); 624-- succeed because c2 doesn't overlap 625INSERT INTO circles VALUES('<(20,20), 10>', '<(10,10), 5>'); 626-- should fail on existing data without the WHERE clause 627ALTER TABLE circles ADD EXCLUDE USING gist 628 (c1 WITH &&, (c2::circle) WITH &&); 629ERROR: could not create exclusion constraint "circles_c1_c2_excl1" 630DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),4>). 631-- try reindexing an existing constraint 632REINDEX INDEX circles_c1_c2_excl; 633DROP TABLE circles; 634-- Check deferred exclusion constraint 635CREATE TABLE deferred_excl ( 636 f1 int, 637 f2 int, 638 CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED 639); 640INSERT INTO deferred_excl VALUES(1); 641INSERT INTO deferred_excl VALUES(2); 642INSERT INTO deferred_excl VALUES(1); -- fail 643ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 644DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1). 645INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail 646ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters 647BEGIN; 648INSERT INTO deferred_excl VALUES(2); -- no fail here 649COMMIT; -- should fail here 650ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 651DETAIL: Key (f1)=(2) conflicts with existing key (f1)=(2). 652BEGIN; 653INSERT INTO deferred_excl VALUES(3); 654INSERT INTO deferred_excl VALUES(3); -- no fail here 655COMMIT; -- should fail here 656ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 657DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3). 658-- bug #13148: deferred constraint versus HOT update 659BEGIN; 660INSERT INTO deferred_excl VALUES(2, 1); -- no fail here 661DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row 662UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2; 663COMMIT; -- should not fail 664SELECT * FROM deferred_excl; 665 f1 | f2 666----+---- 667 1 | 668 2 | 2 669(2 rows) 670 671ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con; 672-- This should fail, but worth testing because of HOT updates 673UPDATE deferred_excl SET f1 = 3; 674ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); 675ERROR: could not create exclusion constraint "deferred_excl_f1_excl" 676DETAIL: Key (f1)=(3) conflicts with key (f1)=(3). 677DROP TABLE deferred_excl; 678-- Comments 679-- Setup a low-level role to enforce non-superuser checks. 680CREATE ROLE regress_constraint_comments; 681SET SESSION AUTHORIZATION regress_constraint_comments; 682CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0)); 683CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0); 684COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment'; 685COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment'; 686-- no such constraint 687COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment'; 688ERROR: constraint "no_constraint" for table "constraint_comments_tbl" does not exist 689COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment'; 690ERROR: constraint "no_constraint" for domain "constraint_comments_dom" does not exist 691-- no such table/domain 692COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment'; 693ERROR: relation "no_comments_tbl" does not exist 694COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment'; 695ERROR: type "no_comments_dom" does not exist 696COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL; 697COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL; 698-- unauthorized user 699RESET SESSION AUTHORIZATION; 700CREATE ROLE regress_constraint_comments_noaccess; 701SET SESSION AUTHORIZATION regress_constraint_comments_noaccess; 702COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'no, the comment'; 703ERROR: must be owner of relation constraint_comments_tbl 704COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'no, another comment'; 705ERROR: must be owner of type constraint_comments_dom 706RESET SESSION AUTHORIZATION; 707DROP TABLE constraint_comments_tbl; 708DROP DOMAIN constraint_comments_dom; 709DROP ROLE regress_constraint_comments; 710DROP ROLE regress_constraint_comments_noaccess; 711