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