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 deferrable UNIQUE with a partitioned table 551CREATE TABLE parted_uniq_tbl (i int UNIQUE DEFERRABLE) partition by range (i); 552CREATE TABLE parted_uniq_tbl_1 PARTITION OF parted_uniq_tbl FOR VALUES FROM (0) TO (10); 553CREATE TABLE parted_uniq_tbl_2 PARTITION OF parted_uniq_tbl FOR VALUES FROM (20) TO (30); 554SELECT conname, conrelid::regclass FROM pg_constraint 555 WHERE conname LIKE 'parted_uniq%' ORDER BY conname; 556 conname | conrelid 557-------------------------+------------------- 558 parted_uniq_tbl_1_i_key | parted_uniq_tbl_1 559 parted_uniq_tbl_2_i_key | parted_uniq_tbl_2 560 parted_uniq_tbl_i_key | parted_uniq_tbl 561(3 rows) 562 563BEGIN; 564INSERT INTO parted_uniq_tbl VALUES (1); 565SAVEPOINT f; 566INSERT INTO parted_uniq_tbl VALUES (1); -- unique violation 567ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key" 568DETAIL: Key (i)=(1) already exists. 569ROLLBACK TO f; 570SET CONSTRAINTS parted_uniq_tbl_i_key DEFERRED; 571INSERT INTO parted_uniq_tbl VALUES (1); -- OK now, fail at commit 572COMMIT; 573ERROR: duplicate key value violates unique constraint "parted_uniq_tbl_1_i_key" 574DETAIL: Key (i)=(1) already exists. 575DROP TABLE parted_uniq_tbl; 576-- test a HOT update that invalidates the conflicting tuple. 577-- the trigger should still fire and catch the violation 578BEGIN; 579INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now 580UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three'; 581COMMIT; -- should fail 582ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" 583DETAIL: Key (i)=(3) already exists. 584SELECT * FROM unique_tbl; 585 i | t 586---+------- 587 3 | three 588 1 | five 589 5 | one 590 4 | two 591 2 | four 592(5 rows) 593 594-- test a HOT update that modifies the newly inserted tuple, 595-- but should succeed because we then remove the other conflicting tuple. 596BEGIN; 597INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now 598UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree'; 599DELETE FROM unique_tbl WHERE t = 'three'; 600SELECT * FROM unique_tbl; 601 i | t 602---+-------- 603 1 | five 604 5 | one 605 4 | two 606 2 | four 607 3 | threex 608(5 rows) 609 610COMMIT; 611SELECT * FROM unique_tbl; 612 i | t 613---+-------- 614 1 | five 615 5 | one 616 4 | two 617 2 | four 618 3 | threex 619(5 rows) 620 621DROP TABLE unique_tbl; 622-- 623-- EXCLUDE constraints 624-- 625CREATE TABLE circles ( 626 c1 CIRCLE, 627 c2 TEXT, 628 EXCLUDE USING gist 629 (c1 WITH &&, (c2::circle) WITH &&) 630 WHERE (circle_center(c1) <> '(0,0)') 631); 632-- these should succeed because they don't match the index predicate 633INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); 634INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>'); 635-- succeed 636INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); 637-- fail, overlaps 638INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>'); 639ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_excl" 640DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>). 641-- succeed, because violation is ignored 642INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') 643 ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING; 644-- fail, because DO UPDATE variant requires unique index 645INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') 646 ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; 647ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints 648-- succeed because c1 doesn't overlap 649INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); 650-- succeed because c2 doesn't overlap 651INSERT INTO circles VALUES('<(20,20), 10>', '<(10,10), 5>'); 652-- should fail on existing data without the WHERE clause 653ALTER TABLE circles ADD EXCLUDE USING gist 654 (c1 WITH &&, (c2::circle) WITH &&); 655ERROR: could not create exclusion constraint "circles_c1_c2_excl1" 656DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),4>). 657-- try reindexing an existing constraint 658REINDEX INDEX circles_c1_c2_excl; 659DROP TABLE circles; 660-- Check deferred exclusion constraint 661CREATE TABLE deferred_excl ( 662 f1 int, 663 f2 int, 664 CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED 665); 666INSERT INTO deferred_excl VALUES(1); 667INSERT INTO deferred_excl VALUES(2); 668INSERT INTO deferred_excl VALUES(1); -- fail 669ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 670DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1). 671INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail 672ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters 673BEGIN; 674INSERT INTO deferred_excl VALUES(2); -- no fail here 675COMMIT; -- should fail here 676ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 677DETAIL: Key (f1)=(2) conflicts with existing key (f1)=(2). 678BEGIN; 679INSERT INTO deferred_excl VALUES(3); 680INSERT INTO deferred_excl VALUES(3); -- no fail here 681COMMIT; -- should fail here 682ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" 683DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3). 684-- bug #13148: deferred constraint versus HOT update 685BEGIN; 686INSERT INTO deferred_excl VALUES(2, 1); -- no fail here 687DELETE FROM deferred_excl WHERE f1 = 2 AND f2 IS NULL; -- remove old row 688UPDATE deferred_excl SET f2 = 2 WHERE f1 = 2; 689COMMIT; -- should not fail 690SELECT * FROM deferred_excl; 691 f1 | f2 692----+---- 693 1 | 694 2 | 2 695(2 rows) 696 697ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con; 698-- This should fail, but worth testing because of HOT updates 699UPDATE deferred_excl SET f1 = 3; 700ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); 701ERROR: could not create exclusion constraint "deferred_excl_f1_excl" 702DETAIL: Key (f1)=(3) conflicts with key (f1)=(3). 703DROP TABLE deferred_excl; 704-- Comments 705-- Setup a low-level role to enforce non-superuser checks. 706CREATE ROLE regress_constraint_comments; 707SET SESSION AUTHORIZATION regress_constraint_comments; 708CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0)); 709CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0); 710COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment'; 711COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment'; 712-- no such constraint 713COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment'; 714ERROR: constraint "no_constraint" for table "constraint_comments_tbl" does not exist 715COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment'; 716ERROR: constraint "no_constraint" for domain constraint_comments_dom does not exist 717-- no such table/domain 718COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment'; 719ERROR: relation "no_comments_tbl" does not exist 720COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment'; 721ERROR: type "no_comments_dom" does not exist 722COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL; 723COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL; 724-- unauthorized user 725RESET SESSION AUTHORIZATION; 726CREATE ROLE regress_constraint_comments_noaccess; 727SET SESSION AUTHORIZATION regress_constraint_comments_noaccess; 728COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'no, the comment'; 729ERROR: must be owner of relation constraint_comments_tbl 730COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'no, another comment'; 731ERROR: must be owner of type constraint_comments_dom 732RESET SESSION AUTHORIZATION; 733DROP TABLE constraint_comments_tbl; 734DROP DOMAIN constraint_comments_dom; 735DROP ROLE regress_constraint_comments; 736DROP ROLE regress_constraint_comments_noaccess; 737