*** ./expected/geometry.out Tue Sep 12 17:07:16 2000 --- ./results/geometry.out Wed Mar 28 20:47:23 2001 *************** *** 150,160 **** six | box -----+---------------------------------------------------------------------------- | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186548,72.7106781186548),(-69.7106781186548,-68.7106781186548) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932738) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559643) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186548),(29.2893218813452,-70.7106781186548) (6 rows) -- translation --- 150,160 ---- six | box -----+---------------------------------------------------------------------------- | (2.12132034355964,2.12132034355964),(-2.12132034355964,-2.12132034355964) ! | (71.7106781186547,72.7106781186547),(-69.7106781186547,-68.7106781186547) ! | (4.53553390593274,6.53553390593274),(-2.53553390593274,-0.535533905932737) ! | (3.12132034355964,4.12132034355964),(-1.12132034355964,-0.121320343559642) | (107.071067811865,207.071067811865),(92.9289321881345,192.928932188135) ! | (170.710678118655,70.7106781186547),(29.2893218813453,-70.7106781186547) (6 rows) -- translation *************** *** 443,454 **** FROM CIRCLE_TBL; six | polygon -----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359078377e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718156754e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077235131e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138)) | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795)) ! | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887966),(-3.33012701896897,0.500000000081028)) ! | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.59807621137373),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048617)) | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162)) ! | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239385585e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795)) (6 rows) -- convert the circle to an 8-point polygon --- 443,454 ---- FROM CIRCLE_TBL; six | polygon -----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! | ((-3,0),(-2.59807621135076,1.50000000000442),(-1.49999999999116,2.59807621135842),(1.53102359017709e-11,3),(1.50000000001768,2.59807621134311),(2.59807621136607,1.4999999999779),(3,-3.06204718035418e-11),(2.59807621133545,-1.50000000003094),(1.49999999996464,-2.59807621137373),(-4.59307077053127e-11,-3),(-1.5000000000442,-2.5980762113278),(-2.59807621138138,-1.49999999995138)) | ((-99,2),(-85.6025403783588,52.0000000001473),(-48.9999999997054,88.602540378614),(1.00000000051034,102),(51.0000000005893,88.6025403781036),(87.6025403788692,51.9999999992634),(101,1.99999999897932),(87.6025403778485,-48.0000000010313),(50.9999999988214,-84.6025403791243),(0.999999998468976,-98),(-49.0000000014732,-84.6025403775933),(-85.6025403793795,-47.9999999983795)) ! | ((-4,3),(-3.33012701891794,5.50000000000737),(-1.49999999998527,7.3301270189307),(1.00000000002552,8),(3.50000000002946,7.33012701890518),(5.33012701894346,5.49999999996317),(6,2.99999999994897),(5.33012701889242,0.499999999948437),(3.49999999994107,-1.33012701895622),(0.999999999923449,-2),(-1.50000000007366,-1.33012701887967),(-3.33012701896897,0.500000000081028)) ! | ((-2,2),(-1.59807621135076,3.50000000000442),(-0.499999999991161,4.59807621135842),(1.00000000001531,5),(2.50000000001768,4.59807621134311),(3.59807621136607,3.4999999999779),(4,1.99999999996938),(3.59807621133545,0.499999999969062),(2.49999999996464,-0.598076211373729),(0.999999999954069,-1),(-0.500000000044197,-0.598076211327799),(-1.59807621138138,0.500000000048616)) | ((90,200),(91.3397459621641,205.000000000015),(95.0000000000295,208.660254037861),(100.000000000051,210),(105.000000000059,208.66025403781),(108.660254037887,204.999999999926),(110,199.999999999898),(108.660254037785,194.999999999897),(104.999999999882,191.339745962088),(99.9999999998469,190),(94.9999999998527,191.339745962241),(91.3397459620621,195.000000000162)) ! | ((0,0),(13.3974596216412,50.0000000001473),(50.0000000002946,86.602540378614),(100.00000000051,100),(150.000000000589,86.6025403781036),(186.602540378869,49.9999999992634),(200,-1.02068239345139e-09),(186.602540377848,-50.0000000010313),(149.999999998821,-86.6025403791243),(99.999999998469,-100),(49.9999999985268,-86.6025403775933),(13.3974596206205,-49.9999999983795)) (6 rows) -- convert the circle to an 8-point polygon *************** *** 456,467 **** FROM CIRCLE_TBL; six | polygon -----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359078377e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718156754e-11),(2.12132034353258,-2.12132034358671),(-4.59307077235131e-11,-3),(-2.12132034359753,-2.12132034352175)) ! | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181134),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917)) | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586)) | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752)) | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261)) ! | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181134),(200,-1.02068239385585e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917)) (6 rows) -- --- 456,467 ---- FROM CIRCLE_TBL; six | polygon -----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ! | ((-3,0),(-2.12132034355423,2.12132034356506),(1.53102359017709e-11,3),(2.12132034357588,2.1213203435434),(3,-3.06204718035418e-11),(2.12132034353258,-2.12132034358671),(-4.59307077053127e-11,-3),(-2.12132034359753,-2.12132034352175)) ! | ((-99,2),(-69.7106781184743,72.7106781188352),(1.00000000051034,102),(71.710678119196,72.7106781181135),(101,1.99999999897932),(71.7106781177526,-68.7106781195569),(0.999999998468976,-98),(-69.7106781199178,-68.7106781173917)) | ((-4,3),(-2.53553390592372,6.53553390594176),(1.00000000002552,8),(4.5355339059598,6.53553390590567),(6,2.99999999994897),(4.53553390588763,-0.535533905977846),(0.999999999923449,-2),(-2.53553390599589,-0.535533905869586)) | ((-2,2),(-1.12132034355423,4.12132034356506),(1.00000000001531,5),(3.12132034357588,4.1213203435434),(4,1.99999999996938),(3.12132034353258,-0.121320343586707),(0.999999999954069,-1),(-1.12132034359753,-0.121320343521752)) | ((90,200),(92.9289321881526,207.071067811884),(100.000000000051,210),(107.07106781192,207.071067811811),(110,199.999999999898),(107.071067811775,192.928932188044),(99.9999999998469,190),(92.9289321880082,192.928932188261)) ! | ((0,0),(29.2893218815257,70.7106781188352),(100.00000000051,100),(170.710678119196,70.7106781181135),(200,-1.02068239345139e-09),(170.710678117753,-70.7106781195569),(99.999999998469,-100),(29.2893218800822,-70.7106781173917)) (6 rows) -- *************** *** 503,513 **** WHERE (p1.f1 <-> c1.f1) > 0 ORDER BY distance, circle, point using <<; twentyfour | circle | point | distance ! ------------+----------------+------------+------------------- ! | <(100,0),100> | (5.1,34.5) | 0.976531926977964 | <(1,2),3> | (-3,4) | 1.47213595499958 | <(0,0),3> | (-3,4) | 2 ! | <(100,0),100> | (-3,4) | 3.07764064044151 | <(100,0),100> | (-5,-12) | 5.68348972285122 | <(1,3),5> | (-10,0) | 6.40175425099138 | <(1,3),5> | (10,10) | 6.40175425099138 --- 503,513 ---- WHERE (p1.f1 <-> c1.f1) > 0 ORDER BY distance, circle, point using <<; twentyfour | circle | point | distance ! ------------+----------------+------------+------------------ ! | <(100,0),100> | (5.1,34.5) | 0.97653192697797 | <(1,2),3> | (-3,4) | 1.47213595499958 | <(0,0),3> | (-3,4) | 2 ! | <(100,0),100> | (-3,4) | 3.07764064044152 | <(100,0),100> | (-5,-12) | 5.68348972285122 | <(1,3),5> | (-10,0) | 6.40175425099138 | <(1,3),5> | (10,10) | 6.40175425099138 *************** *** 519,525 **** | <(0,0),3> | (10,10) | 11.142135623731 | <(1,3),5> | (-5,-12) | 11.1554944214035 | <(1,2),3> | (-5,-12) | 12.2315462117278 ! | <(1,3),5> | (5.1,34.5) | 26.7657047773224 | <(1,2),3> | (5.1,34.5) | 29.757594539282 | <(0,0),3> | (5.1,34.5) | 31.8749193547455 | <(100,200),10> | (5.1,34.5) | 180.778038568384 --- 519,525 ---- | <(0,0),3> | (10,10) | 11.142135623731 | <(1,3),5> | (-5,-12) | 11.1554944214035 | <(1,2),3> | (-5,-12) | 12.2315462117278 ! | <(1,3),5> | (5.1,34.5) | 26.7657047773223 | <(1,2),3> | (5.1,34.5) | 29.757594539282 | <(0,0),3> | (5.1,34.5) | 31.8749193547455 | <(100,200),10> | (5.1,34.5) | 180.778038568384 ====================================================================== *** ./expected/constraints.out Wed Mar 28 19:58:32 2001 --- ./results/constraints.out Wed Mar 28 20:48:19 2001 *************** *** 18,28 **** INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0); SELECT '' AS five, * FROM DEFAULT_TBL; five | i | x | f ! ------+-----+--------+--------- | 1 | thomas | 57.0613 ! | 1 | bruce | 123.456 ! | 2 | vadim | 987.654 ! | 100 | marc | 123.456 | 3 | | 1 (5 rows) --- 18,28 ---- INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0); SELECT '' AS five, * FROM DEFAULT_TBL; five | i | x | f ! ------+---+--------+--------- | 1 | thomas | 57.0613 ! | 1 | bruce | ! | 2 | | 987.654 ! | | marc | | 3 | | 1 (5 rows) *************** *** 35,45 **** INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL); SELECT '' AS four, * FROM DEFAULTEXPR_TBL; four | i1 | i2 ! ------+-----+---- | -1 | -2 ! | -3 | 1 ! | 102 | -4 ! | 102 | (4 rows) -- syntax errors --- 35,45 ---- INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL); SELECT '' AS four, * FROM DEFAULTEXPR_TBL; four | i1 | i2 ! ------+----+---- | -1 | -2 ! | -3 | ! | | -4 ! | | (4 rows) -- syntax errors *************** *** 62,80 **** INSERT INTO CHECK_TBL VALUES (5); INSERT INTO CHECK_TBL VALUES (4); INSERT INTO CHECK_TBL VALUES (3); - ERROR: ExecAppend: rejected due to CHECK constraint check_con INSERT INTO CHECK_TBL VALUES (2); - ERROR: ExecAppend: rejected due to CHECK constraint check_con INSERT INTO CHECK_TBL VALUES (6); INSERT INTO CHECK_TBL VALUES (1); - ERROR: ExecAppend: rejected due to CHECK constraint check_con SELECT '' AS three, * FROM CHECK_TBL; three | x -------+--- | 5 | 4 | 6 ! (3 rows) CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, --- 62,80 ---- INSERT INTO CHECK_TBL VALUES (5); INSERT INTO CHECK_TBL VALUES (4); INSERT INTO CHECK_TBL VALUES (3); INSERT INTO CHECK_TBL VALUES (2); INSERT INTO CHECK_TBL VALUES (6); INSERT INTO CHECK_TBL VALUES (1); SELECT '' AS three, * FROM CHECK_TBL; three | x -------+--- | 5 | 4 + | 3 + | 2 | 6 ! | 1 ! (6 rows) CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, *************** *** 82,101 **** CHECK (x > 3 and y <> 'check failed' and z < 8)); INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2); INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2); - ERROR: ExecAppend: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10); - ERROR: ExecAppend: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2); - ERROR: ExecAppend: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11); - ERROR: ExecAppend: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7); SELECT '' AS two, * from CHECK2_TBL; two | x | y | z ! -----+---+----------+---- | 4 | check ok | -2 | 7 | check ok | 7 ! (2 rows) -- -- Check constraints on INSERT --- 82,101 ---- CHECK (x > 3 and y <> 'check failed' and z < 8)); INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2); INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2); INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10); INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2); INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11); INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7); SELECT '' AS two, * from CHECK2_TBL; two | x | y | z ! -----+---+----------------+---- | 4 | check ok | -2 + | 1 | x check failed | -2 + | 5 | z check failed | 10 + | 0 | check failed | -2 + | 6 | check failed | 11 | 7 | check ok | 7 ! (6 rows) -- -- Check constraints on INSERT *************** *** 107,117 **** CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK (x + z = 0)); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con SELECT '' AS zero, * FROM INSERT_TBL; zero | x | y | z ! ------+---+---+--- ! (0 rows) SELECT 'one' AS one, nextval('insert_seq'); one | nextval --- 107,117 ---- CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK (x + z = 0)); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); SELECT '' AS zero, * FROM INSERT_TBL; zero | x | y | z ! ------+---+---+---- ! | 2 | | -2 ! (1 row) SELECT 'one' AS one, nextval('insert_seq'); one | nextval *************** *** 120,172 **** (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL(y) VALUES ('Y'); INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); - ERROR: ExecAppend: rejected due to CHECK constraint $2 INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS four, * FROM INSERT_TBL; four | x | y | z ------+---+---------------+---- ! | 3 | Y | -3 ! | 7 | -NULL- | -7 | 7 | !check failed | -7 ! | 4 | -!NULL- | -4 ! (4 rows) INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); - ERROR: ExecAppend: rejected due to CHECK constraint $2 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS six, * FROM INSERT_TBL; six | x | y | z -----+---+---------------+---- ! | 3 | Y | -3 ! | 7 | -NULL- | -7 | 7 | !check failed | -7 ! | 4 | -!NULL- | -4 ! | 5 | !check failed | -5 ! | 6 | -!NULL- | -6 ! (6 rows) SELECT 'seven' AS one, nextval('insert_seq'); one | nextval -------+--------- ! seven | 7 (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con SELECT 'eight' AS one, currval('insert_seq'); one | currval -------+--------- ! eight | 8 (1 row) -- According to SQL92, it is OK to insert a record that gives rise to NULL --- 120,176 ---- (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); INSERT INTO INSERT_TBL(y) VALUES ('Y'); INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS four, * FROM INSERT_TBL; four | x | y | z ------+---+---------------+---- ! | 2 | | -2 ! | | Y | ! | | Y | ! | 1 | | -2 ! | 7 | | -7 ! | 5 | check failed | -5 | 7 | !check failed | -7 ! | | -!NULL- | ! (8 rows) INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS six, * FROM INSERT_TBL; six | x | y | z -----+---+---------------+---- ! | 2 | | -2 ! | | Y | ! | | Y | ! | 1 | | -2 ! | 7 | | -7 ! | 5 | check failed | -5 | 7 | !check failed | -7 ! | | -!NULL- | ! | | check failed | 4 ! | 5 | check failed | ! | 5 | !check failed | ! | | -!NULL- | ! (12 rows) SELECT 'seven' AS one, nextval('insert_seq'); one | nextval -------+--------- ! seven | 2 (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); SELECT 'eight' AS one, currval('insert_seq'); one | currval -------+--------- ! eight | 2 (1 row) -- According to SQL92, it is OK to insert a record that gives rise to NULL *************** *** 176,189 **** SELECT '' AS nine, * FROM INSERT_TBL; nine | x | y | z ------+---+---------------+---- ! | 3 | Y | -3 ! | 7 | -NULL- | -7 | 7 | !check failed | -7 ! | 4 | -!NULL- | -4 ! | 5 | !check failed | -5 ! | 6 | -!NULL- | -6 | | | ! (7 rows) -- -- Check inheritance of defaults and constraints --- 180,200 ---- SELECT '' AS nine, * FROM INSERT_TBL; nine | x | y | z ------+---+---------------+---- ! | 2 | | -2 ! | | Y | ! | | Y | ! | 1 | | -2 ! | 7 | | -7 ! | 5 | check failed | -5 | 7 | !check failed | -7 ! | | -!NULL- | ! | | check failed | 4 ! | 5 | check failed | ! | 5 | !check failed | ! | | -!NULL- | ! | | Y | | | | ! (14 rows) -- -- Check inheritance of defaults and constraints *************** *** 193,210 **** INHERITS (INSERT_TBL); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); - ERROR: ExecAppend: rejected due to CHECK constraint insert_child_cy INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); - ERROR: ExecAppend: rejected due to CHECK constraint $1 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7); - ERROR: ExecAppend: rejected due to CHECK constraint insert_con SELECT * FROM INSERT_CHILD; x | y | z | cx | cy ! ---+--------+----+----+---- ! 7 | -NULL- | -7 | 42 | 11 ! (1 row) DROP TABLE INSERT_CHILD; -- -- Check constraints on INSERT INTO -- --- 204,222 ---- INHERITS (INSERT_TBL); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7); SELECT * FROM INSERT_CHILD; x | y | z | cx | cy ! ---+--------------+----+----+---- ! 7 | | -7 | | 11 ! 7 | | -7 | | 6 ! 6 | | -7 | | 7 ! 6 | check failed | -6 | | 7 ! (4 rows) DROP TABLE INSERT_CHILD; + ERROR: IndexGetRelation: can't find index id 0 -- -- Check constraints on INSERT INTO -- *************** *** 216,245 **** INSERT INTO tmp VALUES (5, '!check failed', null); INSERT INTO tmp VALUES (null, 'try again', null); INSERT INTO INSERT_TBL(y) select yd from tmp; - NOTICE: insert_seq.nextval: sequence was re-created SELECT '' AS three, * FROM INSERT_TBL; three | x | y | z ! -------+---+---------------+---- ! | 4 | Y | -4 ! | 5 | !check failed | -5 ! | 6 | try again | -6 (3 rows) INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again'; - ERROR: ExecAppend: rejected due to CHECK constraint insert_con SELECT '' AS four, * FROM INSERT_TBL; four | x | y | z ------+---+---------------+---- ! | 4 | Y | -4 ! | 5 | !check failed | -5 ! | 6 | try again | -6 | | try again | ! | 7 | try again | -7 ! (5 rows) DROP TABLE tmp; -- -- Check constraints on UPDATE -- --- 228,257 ---- INSERT INTO tmp VALUES (5, '!check failed', null); INSERT INTO tmp VALUES (null, 'try again', null); INSERT INTO INSERT_TBL(y) select yd from tmp; SELECT '' AS three, * FROM INSERT_TBL; three | x | y | z ! -------+---+---------------+--- ! | | Y | ! | | !check failed | ! | | try again | (3 rows) INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again'; SELECT '' AS four, * FROM INSERT_TBL; four | x | y | z ------+---+---------------+---- ! | | Y | ! | | !check failed | | | try again | ! | | try again | ! | | try again | -7 ! | | try again | -8 ! (6 rows) DROP TABLE tmp; + ERROR: IndexGetRelation: can't find index id 0 -- -- Check constraints on UPDATE -- *************** *** 247,262 **** UPDATE INSERT_TBL SET x = 6 WHERE x = 6; UPDATE INSERT_TBL SET x = -z, z = -x; UPDATE INSERT_TBL SET x = z, z = x; - ERROR: ExecReplace: rejected due to CHECK constraint insert_con SELECT * FROM INSERT_TBL; x | y | z ! ---+---------------+---- ! 4 | Y | -4 | try again | ! 7 | try again | -7 ! 5 | !check failed | ! 6 | try again | -6 ! (5 rows) -- DROP TABLE INSERT_TBL; -- --- 259,274 ---- UPDATE INSERT_TBL SET x = 6 WHERE x = 6; UPDATE INSERT_TBL SET x = -z, z = -x; UPDATE INSERT_TBL SET x = z, z = x; SELECT * FROM INSERT_TBL; x | y | z ! ---+---------------+--- ! | Y | ! | !check failed | | try again | ! | try again | ! | try again | 7 ! | try again | 8 ! (6 rows) -- DROP TABLE INSERT_TBL; -- *************** *** 274,286 **** (2 rows) COPY COPY_TBL FROM '/usr/src/postgresql-7.1beta6/src/test/regress/data/constrf.data'; - ERROR: copy: line 2, CopyFrom: rejected due to CHECK constraint copy_con SELECT * FROM COPY_TBL; x | y | z ---+---------------+--- 4 | !check failed | 5 6 | OK | 4 ! (2 rows) -- -- Primary keys --- 286,299 ---- (2 rows) COPY COPY_TBL FROM '/usr/src/postgresql-7.1beta6/src/test/regress/data/constrf.data'; SELECT * FROM COPY_TBL; x | y | z ---+---------------+--- 4 | !check failed | 5 6 | OK | 4 ! 5 | !check failed | 6 ! 7 | check failed | 6 ! (4 rows) -- -- Primary keys *************** *** 305,318 **** --- 318,338 ---- (4 rows) DROP TABLE PRIMARY_TBL; + ERROR: IndexGetRelation: can't find index id 0 CREATE TABLE PRIMARY_TBL (i int, t text, PRIMARY KEY(i,t)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'primary_tbl_pkey' for table 'primary_tbl' + ERROR: Relation 'primary_tbl' already exists INSERT INTO PRIMARY_TBL VALUES (1, 'one'); + ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL VALUES (2, 'two'); + ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL VALUES (1, 'three'); + ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL VALUES (4, 'three'); + ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL VALUES (5, 'one'); + ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL (t) VALUES ('six'); ERROR: ExecAppend: Fail to add null value in not null attribute i SELECT '' AS three, * FROM PRIMARY_TBL; *************** *** 320,331 **** -------+---+------- | 1 | one | 2 | two - | 1 | three | 4 | three | 5 | one ! (5 rows) DROP TABLE PRIMARY_TBL; -- -- Unique keys -- --- 340,351 ---- -------+---+------- | 1 | one | 2 | two | 4 | three | 5 | one ! (4 rows) DROP TABLE PRIMARY_TBL; + ERROR: IndexGetRelation: can't find index id 0 -- -- Unique keys -- *************** *** 351,374 **** (6 rows) DROP TABLE UNIQUE_TBL; CREATE TABLE UNIQUE_TBL (i int, t text, UNIQUE(i,t)); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'unique_tbl_i_key' for table 'unique_tbl' INSERT INTO UNIQUE_TBL VALUES (1, 'one'); INSERT INTO UNIQUE_TBL VALUES (2, 'two'); INSERT INTO UNIQUE_TBL VALUES (1, 'three'); INSERT INTO UNIQUE_TBL VALUES (1, 'one'); ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL VALUES (5, 'one'); INSERT INTO UNIQUE_TBL (t) VALUES ('six'); SELECT '' AS five, * FROM UNIQUE_TBL; five | i | t ------+---+------- | 1 | one | 2 | two ! | 1 | three | 5 | one | | six ! (5 rows) DROP TABLE UNIQUE_TBL; --- 371,403 ---- (6 rows) DROP TABLE UNIQUE_TBL; + ERROR: IndexGetRelation: can't find index id 0 CREATE TABLE UNIQUE_TBL (i int, t text, UNIQUE(i,t)); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'unique_tbl_i_key' for table 'unique_tbl' + ERROR: Relation 'unique_tbl' already exists INSERT INTO UNIQUE_TBL VALUES (1, 'one'); + ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL VALUES (2, 'two'); + ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL VALUES (1, 'three'); + ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL VALUES (1, 'one'); ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL VALUES (5, 'one'); + ERROR: Cannot insert a duplicate key into unique index unique_tbl_i_key INSERT INTO UNIQUE_TBL (t) VALUES ('six'); SELECT '' AS five, * FROM UNIQUE_TBL; five | i | t ------+---+------- | 1 | one | 2 | two ! | 4 | four | 5 | one | | six ! | | seven ! | | six ! (7 rows) DROP TABLE UNIQUE_TBL; + ERROR: IndexGetRelation: can't find index id 0 ====================================================================== *** ./expected/triggers.out Sat Jan 15 14:18:23 2000 --- ./results/triggers.out Wed Mar 28 20:48:17 2001 *************** *** 87,94 **** --- 87,97 ---- NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted DROP TABLE pkeys; + ERROR: IndexGetRelation: can't find index id 0 DROP TABLE fkeys; + ERROR: IndexGetRelation: can't find index id 31390208 DROP TABLE fkeys2; + ERROR: IndexGetRelation: can't find index id 0 -- -- I've disabled the funny_dup17 test because the new semantics -- -- of AFTER ROW triggers, which get now fired at the end of a -- -- query allways, cause funny_dup17 to enter an endless loop. *************** *** 146,163 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 999999 ! 3 | 3 | 30 | 999999 (3 rows) delete from tttest where price_id = 2; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 3 | 3 | 30 | 999999 ! 2 | 2 | 20 | 40 (3 rows) -- what do we see ? --- 149,167 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 3 | 3 | 30 | (3 rows) delete from tttest where price_id = 2; + ERROR: ttdummy (tttest): price_off must be NOT NULL select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 3 | 3 | 30 | (3 rows) -- what do we see ? *************** *** 165,197 **** select * from tttest where price_off = 999999; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 3 | 3 | 30 | 999999 ! (2 rows) -- change price for price_id == 3 update tttest set price_val = 30 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 30 | 50 | 999999 ! 3 | 3 | 30 | 50 ! (4 rows) -- now we want to change pric_id in ALL tuples -- this gets us not what we need update tttest set price_id = 5 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 3 | 30 | 50 ! 5 | 30 | 60 | 999999 ! 3 | 30 | 50 | 60 ! (5 rows) -- restore data as before last update: select set_ttdummy(0); --- 169,198 ---- select * from tttest where price_off = 999999; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! (0 rows) -- change price for price_id == 3 update tttest set price_val = 30 where price_id = 3; + ERROR: ttdummy (tttest): price_off must be NOT NULL select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 3 | 3 | 30 | ! (3 rows) -- now we want to change pric_id in ALL tuples -- this gets us not what we need update tttest set price_id = 5 where price_id = 3; + ERROR: ttdummy (tttest): price_off must be NOT NULL select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 3 | 3 | 30 | ! (3 rows) -- restore data as before last update: select set_ttdummy(0); *************** *** 205,226 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 3 | 3 | 30 | 50 ! 3 | 30 | 50 | 999999 ! (4 rows) -- and try change price_id now! update tttest set price_id = 5 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | 999999 ! 2 | 2 | 20 | 40 ! 5 | 3 | 30 | 50 ! 5 | 30 | 50 | 999999 ! (4 rows) -- isn't it what we need ? select set_ttdummy(1); --- 206,225 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 3 | 3 | 30 | ! (3 rows) -- and try change price_id now! update tttest set price_id = 5 where price_id = 3; select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 1 | 1 | 10 | ! 2 | 2 | 20 | ! 5 | 3 | 30 | ! (3 rows) -- isn't it what we need ? select set_ttdummy(1); *************** *** 231,237 **** -- we want to correct some "date" update tttest set price_on = -1 where price_id = 1; ! ERROR: ttdummy (tttest): you can't change price_on and/or price_off columns (use set_ttdummy) -- but this doesn't work -- try in this way select set_ttdummy(0); --- 230,236 ---- -- we want to correct some "date" update tttest set price_on = -1 where price_id = 1; ! ERROR: ttdummy (tttest): price_off must be NOT NULL -- but this doesn't work -- try in this way select set_ttdummy(0); *************** *** 244,262 **** select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 2 | 2 | 20 | 40 ! 5 | 3 | 30 | 50 ! 5 | 30 | 50 | 999999 ! 1 | 1 | -1 | 999999 ! (4 rows) -- isn't it what we need ? -- get price for price_id == 5 as it was @ "date" 35 select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 5 | 3 | 30 | 50 ! (1 row) drop table tttest; drop sequence ttdummy_seq; --- 243,259 ---- select * from tttest; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! 2 | 2 | 20 | ! 5 | 3 | 30 | ! 1 | 1 | -1 | ! (3 rows) -- isn't it what we need ? -- get price for price_id == 5 as it was @ "date" 35 select * from tttest where price_on <= 35 and price_off > 35 and price_id = 5; price_id | price_val | price_on | price_off ----------+-----------+----------+----------- ! (0 rows) drop table tttest; drop sequence ttdummy_seq; ====================================================================== *** ./expected/create_misc.out Sat Feb 19 21:15:59 2000 --- ./results/create_misc.out Wed Mar 28 20:48:30 2001 *************** *** 156,170 **** NOTICE: CREATE TABLE will create implicit sequence 'serialtest_f2_seq' for SERIAL column 'serialtest.f2' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'serialtest_f2_key' for table 'serialtest' INSERT INTO serialTest VALUES ('foo'); INSERT INTO serialTest VALUES ('bar'); INSERT INTO serialTest VALUES ('force', 100); INSERT INTO serialTest VALUES ('wrong', NULL); ERROR: ExecAppend: Fail to add null value in not null attribute f2 SELECT * FROM serialTest; f1 | f2 -------+----- - foo | 1 - bar | 2 force | 100 ! (3 rows) --- 156,170 ---- NOTICE: CREATE TABLE will create implicit sequence 'serialtest_f2_seq' for SERIAL column 'serialtest.f2' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'serialtest_f2_key' for table 'serialtest' INSERT INTO serialTest VALUES ('foo'); + ERROR: ExecAppend: Fail to add null value in not null attribute f2 INSERT INTO serialTest VALUES ('bar'); + ERROR: ExecAppend: Fail to add null value in not null attribute f2 INSERT INTO serialTest VALUES ('force', 100); INSERT INTO serialTest VALUES ('wrong', NULL); ERROR: ExecAppend: Fail to add null value in not null attribute f2 SELECT * FROM serialTest; f1 | f2 -------+----- force | 100 ! (1 row) ====================================================================== *** ./expected/sanity_check.out Mon Oct 23 21:38:44 2000 --- ./results/sanity_check.out Wed Mar 28 20:49:26 2001 *************** *** 15,20 **** --- 15,22 ---- bt_name_heap | t bt_txt_heap | t fast_emp4000 | t + fkeys | t + fkeys2 | t hash_f8_heap | t hash_i4_heap | t hash_name_heap | t *************** *** 50,59 **** pg_statistic | t pg_trigger | t pg_type | t road | t serialtest | t shighway | t tenk1 | t tenk2 | t ! (45 rows) --- 52,64 ---- pg_statistic | t pg_trigger | t pg_type | t + pkeys | t + primary_tbl | t road | t serialtest | t shighway | t tenk1 | t tenk2 | t ! unique_tbl | t ! (50 rows) ====================================================================== *** ./expected/select.out Tue Feb 15 15:49:29 2000 --- ./results/select.out Wed Mar 28 20:49:28 2001 *************** *** 226,231 **** --- 226,232 ---- SELECT two, stringu1, ten, string4 INTO TABLE tmp FROM onek; + ERROR: Relation 'tmp' already exists -- -- awk '{print $1,$2;}' person.data | -- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data | ====================================================================== *** ./expected/select_distinct.out Thu Jan 6 01:40:54 2000 --- ./results/select_distinct.out Wed Mar 28 20:49:33 2001 *************** *** 5,46 **** -- awk '{print $3;}' onek.data | sort -n | uniq -- SELECT DISTINCT two FROM tmp; ! two ! ----- ! 0 ! 1 ! (2 rows) ! -- -- awk '{print $5;}' onek.data | sort -n | uniq -- SELECT DISTINCT ten FROM tmp; ! ten ! ----- ! 0 ! 1 ! 2 ! 3 ! 4 ! 5 ! 6 ! 7 ! 8 ! 9 ! (10 rows) ! -- -- awk '{print $16;}' onek.data | sort -d | uniq -- SELECT DISTINCT string4 FROM tmp; ! string4 ! --------- ! AAAAxx ! HHHHxx ! OOOOxx ! VVVVxx ! (4 rows) ! -- -- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | -- sort +0n -1 +1d -2 +2n -3 --- 5,21 ---- -- awk '{print $3;}' onek.data | sort -n | uniq -- SELECT DISTINCT two FROM tmp; ! ERROR: Attribute 'two' not found -- -- awk '{print $5;}' onek.data | sort -n | uniq -- SELECT DISTINCT ten FROM tmp; ! ERROR: Attribute 'ten' not found -- -- awk '{print $16;}' onek.data | sort -d | uniq -- SELECT DISTINCT string4 FROM tmp; ! ERROR: Attribute 'string4' not found -- -- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | -- sort +0n -1 +1d -2 +2n -3 *************** *** 48,97 **** SELECT DISTINCT two, string4, ten FROM tmp ORDER BY two using <, string4 using <, ten using <; ! two | string4 | ten ! -----+---------+----- ! 0 | AAAAxx | 0 ! 0 | AAAAxx | 2 ! 0 | AAAAxx | 4 ! 0 | AAAAxx | 6 ! 0 | AAAAxx | 8 ! 0 | HHHHxx | 0 ! 0 | HHHHxx | 2 ! 0 | HHHHxx | 4 ! 0 | HHHHxx | 6 ! 0 | HHHHxx | 8 ! 0 | OOOOxx | 0 ! 0 | OOOOxx | 2 ! 0 | OOOOxx | 4 ! 0 | OOOOxx | 6 ! 0 | OOOOxx | 8 ! 0 | VVVVxx | 0 ! 0 | VVVVxx | 2 ! 0 | VVVVxx | 4 ! 0 | VVVVxx | 6 ! 0 | VVVVxx | 8 ! 1 | AAAAxx | 1 ! 1 | AAAAxx | 3 ! 1 | AAAAxx | 5 ! 1 | AAAAxx | 7 ! 1 | AAAAxx | 9 ! 1 | HHHHxx | 1 ! 1 | HHHHxx | 3 ! 1 | HHHHxx | 5 ! 1 | HHHHxx | 7 ! 1 | HHHHxx | 9 ! 1 | OOOOxx | 1 ! 1 | OOOOxx | 3 ! 1 | OOOOxx | 5 ! 1 | OOOOxx | 7 ! 1 | OOOOxx | 9 ! 1 | VVVVxx | 1 ! 1 | VVVVxx | 3 ! 1 | VVVVxx | 5 ! 1 | VVVVxx | 7 ! 1 | VVVVxx | 9 ! (40 rows) ! -- -- awk '{print $2;}' person.data | -- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | --- 23,29 ---- SELECT DISTINCT two, string4, ten FROM tmp ORDER BY two using <, string4 using <, ten using <; ! ERROR: Attribute 'two' not found -- -- awk '{print $2;}' person.data | -- awk '{if(NF!=1){print $2;}else{print;}}' - emp.data | ====================================================================== *** ./expected/select_distinct_on.out Thu Jan 27 13:11:50 2000 --- ./results/select_distinct_on.out Wed Mar 28 20:49:32 2001 *************** *** 4,66 **** SELECT DISTINCT ON (string4) string4, two, ten FROM tmp ORDER BY string4 using <, two using >, ten using <; ! string4 | two | ten ! ---------+-----+----- ! AAAAxx | 1 | 1 ! HHHHxx | 1 | 1 ! OOOOxx | 1 | 1 ! VVVVxx | 1 | 1 ! (4 rows) ! -- this will fail due to conflict of ordering requirements SELECT DISTINCT ON (string4, ten) string4, two, ten FROM tmp ORDER BY string4 using <, two using <, ten using <; ! ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions SELECT DISTINCT ON (string4, ten) string4, ten, two FROM tmp ORDER BY string4 using <, ten using >, two using <; ! string4 | ten | two ! ---------+-----+----- ! AAAAxx | 9 | 1 ! AAAAxx | 8 | 0 ! AAAAxx | 7 | 1 ! AAAAxx | 6 | 0 ! AAAAxx | 5 | 1 ! AAAAxx | 4 | 0 ! AAAAxx | 3 | 1 ! AAAAxx | 2 | 0 ! AAAAxx | 1 | 1 ! AAAAxx | 0 | 0 ! HHHHxx | 9 | 1 ! HHHHxx | 8 | 0 ! HHHHxx | 7 | 1 ! HHHHxx | 6 | 0 ! HHHHxx | 5 | 1 ! HHHHxx | 4 | 0 ! HHHHxx | 3 | 1 ! HHHHxx | 2 | 0 ! HHHHxx | 1 | 1 ! HHHHxx | 0 | 0 ! OOOOxx | 9 | 1 ! OOOOxx | 8 | 0 ! OOOOxx | 7 | 1 ! OOOOxx | 6 | 0 ! OOOOxx | 5 | 1 ! OOOOxx | 4 | 0 ! OOOOxx | 3 | 1 ! OOOOxx | 2 | 0 ! OOOOxx | 1 | 1 ! OOOOxx | 0 | 0 ! VVVVxx | 9 | 1 ! VVVVxx | 8 | 0 ! VVVVxx | 7 | 1 ! VVVVxx | 6 | 0 ! VVVVxx | 5 | 1 ! VVVVxx | 4 | 0 ! VVVVxx | 3 | 1 ! VVVVxx | 2 | 0 ! VVVVxx | 1 | 1 ! VVVVxx | 0 | 0 ! (40 rows) ! --- 4,16 ---- SELECT DISTINCT ON (string4) string4, two, ten FROM tmp ORDER BY string4 using <, two using >, ten using <; ! ERROR: Attribute 'string4' not found -- this will fail due to conflict of ordering requirements SELECT DISTINCT ON (string4, ten) string4, two, ten FROM tmp ORDER BY string4 using <, two using <, ten using <; ! ERROR: Attribute 'string4' not found SELECT DISTINCT ON (string4, ten) string4, ten, two FROM tmp ORDER BY string4 using <, ten using >, two using <; ! ERROR: Attribute 'string4' not found ====================================================================== *** ./expected/join.out Sat Jan 6 21:03:42 2001 --- ./results/join.out Wed Mar 28 20:49:41 2001 *************** *** 1850,1853 **** --- 1850,1854 ---- -- Clean up -- DROP TABLE J1_TBL; + ERROR: IndexGetRelation: can't find index id 0 DROP TABLE J2_TBL; ====================================================================== *** ./expected/random.out Thu Jan 6 01:40:54 2000 --- ./results/random.out Wed Mar 28 20:49:37 2001 *************** *** 25,31 **** GROUP BY random HAVING count(random) > 1; random | count --------+------- ! (0 rows) SELECT random FROM RANDOM_TBL WHERE random NOT BETWEEN 80 AND 120; --- 25,32 ---- GROUP BY random HAVING count(random) > 1; random | count --------+------- ! 104 | 2 ! (1 row) SELECT random FROM RANDOM_TBL WHERE random NOT BETWEEN 80 AND 120; ====================================================================== *** ./expected/misc.out Wed Mar 28 19:58:32 2001 --- ./results/misc.out Wed Mar 28 20:49:51 2001 *************** *** 26,36 **** --- 26,39 ---- SET stringu1 = reverse_name(onek.stringu1) WHERE onek.stringu1 = 'JBAAAA' and onek.stringu1 = tmp.stringu1; + ERROR: No such attribute or function 'stringu1' UPDATE tmp SET stringu1 = reverse_name(onek2.stringu1) WHERE onek2.stringu1 = 'JCAAAA' and onek2.stringu1 = tmp.stringu1; + ERROR: No such attribute or function 'stringu1' DROP TABLE tmp; + ERROR: IndexGetRelation: can't find index id 0 --UPDATE person* -- SET age = age + 1; --UPDATE person* *************** *** 597,602 **** --- 600,607 ---- equipment_r f_star fast_emp4000 + fkeys + fkeys2 float4_tbl float8_tbl hash_f8_heap *************** *** 607,612 **** --- 612,618 ---- iexit ihighway inet_tbl + insert_child insert_seq insert_tbl int2_tbl *************** *** 614,619 **** --- 620,626 ---- int8_tbl interval_tbl iportaltest + j1_tbl lseg_tbl num_data num_exp_add *************** *** 629,636 **** --- 636,645 ---- onek2 path_tbl person + pkeys point_tbl polygon_tbl + primary_tbl ramp random_tbl real_city *************** *** 650,659 **** time_tbl timestamp_tbl tinterval_tbl toyemp varchar_tbl xacttest ! (90 rows) --SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name; -- --- 659,670 ---- time_tbl timestamp_tbl tinterval_tbl + tmp toyemp + unique_tbl varchar_tbl xacttest ! (98 rows) --SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer'))) AS equip_name; -- *************** *** 674,679 **** --- 685,691 ---- (4 rows) drop table oldstyle_test; + ERROR: IndexGetRelation: can't find index id 33554432 -- -- functional joins -- ====================================================================== *** ./expected/alter_table.out Tue Dec 5 14:57:56 2000 --- ./results/alter_table.out Wed Mar 28 20:50:05 2001 *************** *** 3,8 **** --- 3,9 ---- -- add attribute -- CREATE TABLE tmp (initial int4); + ERROR: Relation 'tmp' already exists ALTER TABLE tmp ADD COLUMN a int4; ALTER TABLE tmp ADD COLUMN b name; ALTER TABLE tmp ADD COLUMN c text; *************** *** 40,81 **** '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); ERROR: Relation 'tmp' does not have attribute 'k' SELECT * FROM tmp; ! initial | a | b | c | d | e | f | g | h | i | j | l | m | n | p | q | r | s | t | u | v | w | x | y | z ! ---------+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+--- ! (0 rows) DROP TABLE tmp; -- the wolf bug - schema mods caused inconsistent row descriptors CREATE TABLE tmp ( initial int4 ); ALTER TABLE tmp ADD COLUMN a int4; ALTER TABLE tmp ADD COLUMN b name; ALTER TABLE tmp ADD COLUMN c text; ALTER TABLE tmp ADD COLUMN d float8; ALTER TABLE tmp ADD COLUMN e float4; ALTER TABLE tmp ADD COLUMN f int2; ALTER TABLE tmp ADD COLUMN g polygon; ALTER TABLE tmp ADD COLUMN h abstime; ALTER TABLE tmp ADD COLUMN i char; ALTER TABLE tmp ADD COLUMN j abstime[]; ALTER TABLE tmp ADD COLUMN k dt; ERROR: Unable to locate type name 'dt' in catalog ALTER TABLE tmp ADD COLUMN l tid; ALTER TABLE tmp ADD COLUMN m xid; ALTER TABLE tmp ADD COLUMN n oidvector; --ALTER TABLE tmp ADD COLUMN o lock; ALTER TABLE tmp ADD COLUMN p smgr; ALTER TABLE tmp ADD COLUMN q point; ALTER TABLE tmp ADD COLUMN r lseg; ALTER TABLE tmp ADD COLUMN s path; ALTER TABLE tmp ADD COLUMN t box; ALTER TABLE tmp ADD COLUMN u tinterval; ALTER TABLE tmp ADD COLUMN v datetime; ALTER TABLE tmp ADD COLUMN w timespan; ALTER TABLE tmp ADD COLUMN x float8[]; ALTER TABLE tmp ADD COLUMN y float4[]; ALTER TABLE tmp ADD COLUMN z int2[]; INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, v, w, x, y, z) VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', --- 41,111 ---- '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); ERROR: Relation 'tmp' does not have attribute 'k' SELECT * FROM tmp; ! xd | yd | zd | a | b | c | d | e | f | g | h | i | j | l | m | n | p | q | r | s | t | u | v | w | x | y | z ! ----+---------------+----+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+--- ! | Y | | | | | | | | | | | | | | | | | | | | | | | | | ! 5 | !check failed | | | | | | | | | | | | | | | | | | | | | | | | | ! | try again | | | | | | | | | | | | | | | | | | | | | | | | | ! (3 rows) DROP TABLE tmp; + ERROR: IndexGetRelation: can't find index id 0 -- the wolf bug - schema mods caused inconsistent row descriptors CREATE TABLE tmp ( initial int4 ); + ERROR: Relation 'tmp' already exists ALTER TABLE tmp ADD COLUMN a int4; + ERROR: ALTER TABLE: column name "a" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN b name; + ERROR: ALTER TABLE: column name "b" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN c text; + ERROR: ALTER TABLE: column name "c" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN d float8; + ERROR: ALTER TABLE: column name "d" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN e float4; + ERROR: ALTER TABLE: column name "e" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN f int2; + ERROR: ALTER TABLE: column name "f" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN g polygon; + ERROR: ALTER TABLE: column name "g" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN h abstime; + ERROR: ALTER TABLE: column name "h" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN i char; + ERROR: ALTER TABLE: column name "i" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN j abstime[]; + ERROR: ALTER TABLE: column name "j" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN k dt; ERROR: Unable to locate type name 'dt' in catalog ALTER TABLE tmp ADD COLUMN l tid; + ERROR: ALTER TABLE: column name "l" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN m xid; + ERROR: ALTER TABLE: column name "m" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN n oidvector; + ERROR: ALTER TABLE: column name "n" already exists in table "tmp" --ALTER TABLE tmp ADD COLUMN o lock; ALTER TABLE tmp ADD COLUMN p smgr; + ERROR: ALTER TABLE: column name "p" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN q point; + ERROR: ALTER TABLE: column name "q" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN r lseg; + ERROR: ALTER TABLE: column name "r" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN s path; + ERROR: ALTER TABLE: column name "s" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN t box; + ERROR: ALTER TABLE: column name "t" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN u tinterval; + ERROR: ALTER TABLE: column name "u" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN v datetime; + ERROR: ALTER TABLE: column name "v" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN w timespan; + ERROR: ALTER TABLE: column name "w" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN x float8[]; + ERROR: ALTER TABLE: column name "x" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN y float4[]; + ERROR: ALTER TABLE: column name "y" already exists in table "tmp" ALTER TABLE tmp ADD COLUMN z int2[]; + ERROR: ALTER TABLE: column name "z" already exists in table "tmp" INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, v, w, x, y, z) VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', *************** *** 86,96 **** '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); ERROR: Relation 'tmp' does not have attribute 'k' SELECT * FROM tmp; ! initial | a | b | c | d | e | f | g | h | i | j | l | m | n | p | q | r | s | t | u | v | w | x | y | z ! ---------+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+--- ! (0 rows) DROP TABLE tmp; -- -- rename - -- should preserve indices, which we can check by seeing if a SELECT --- 116,130 ---- '1/3', '1,name', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); ERROR: Relation 'tmp' does not have attribute 'k' SELECT * FROM tmp; ! xd | yd | zd | a | b | c | d | e | f | g | h | i | j | l | m | n | p | q | r | s | t | u | v | w | x | y | z ! ----+---------------+----+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+---+--- ! | Y | | | | | | | | | | | | | | | | | | | | | | | | | ! 5 | !check failed | | | | | | | | | | | | | | | | | | | | | | | | | ! | try again | | | | | | | | | | | | | | | | | | | | | | | | | ! (3 rows) DROP TABLE tmp; + ERROR: IndexGetRelation: can't find index id 0 -- -- rename - -- should preserve indices, which we can check by seeing if a SELECT *************** *** 309,315 **** --- 343,351 ---- ERROR: UNIQUE constraint matching given keys for referenced table "tmp4" not found DROP TABLE tmp5; DROP TABLE tmp4; + ERROR: IndexGetRelation: can't find index id 0 DROP TABLE tmp3; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2" NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2" DROP TABLE tmp2; + ERROR: IndexGetRelation: can't find index id 243466240 ====================================================================== *** ./expected/rules.out Tue Dec 5 14:15:48 2000 --- ./results/rules.out Wed Mar 28 20:50:11 2001 *************** *** 1259,1265 **** --- 1259,1267 ---- drop rule rrule; drop view vview; drop table pparent; + ERROR: IndexGetRelation: can't find index id 0 drop table cchild; + ERROR: IndexGetRelation: can't find index id 0 -- -- Check that ruleutils are working -- ====================================================================== *** ./expected/foreign_key.out Tue Dec 5 14:57:56 2000 --- ./results/foreign_key.out Wed Mar 28 20:50:03 2001 *************** *** 57,244 **** DROP TABLE PKTABLE; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" DROP TABLE FKTABLE; -- -- check set NULL and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2, 4); INSERT INTO FKTABLE VALUES (1, 3, 5); INSERT INTO FKTABLE VALUES (2, 4, 8); INSERT INTO FKTABLE VALUES (3, 6, 12); INSERT INTO FKTABLE VALUES (NULL, NULL, 0); -- Insert failed rows into FK TABLE INSERT INTO FKTABLE VALUES (100, 2, 4); ! ERROR: constrname referential integrity violation - key referenced from fktable not found in pktable INSERT INTO FKTABLE VALUES (2, 2, 4); ! ERROR: constrname referential integrity violation - key referenced from fktable not found in pktable INSERT INTO FKTABLE VALUES (NULL, 2, 4); ! ERROR: constrname referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values INSERT INTO FKTABLE VALUES (1, NULL, 4); ! ERROR: constrname referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values -- Check FKTABLE SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 2 | 4 ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! (5 rows) ! -- Delete a row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; -- Check FKTABLE for removal of matched row SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! | | 4 ! (5 rows) ! -- Delete another row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; -- Check FKTABLE (should be no change) SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! | | 4 ! (5 rows) ! -- Update a row from PK TABLE UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; -- Check FKTABLE for update of matched row SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 3 | 6 | 12 ! | | 0 ! | | 4 ! | | 8 ! (5 rows) ! DROP TABLE PKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" DROP TABLE FKTABLE; -- -- check set default and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert a value in PKTABLE for default INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!'); -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2, 4); INSERT INTO FKTABLE VALUES (1, 3, 5); INSERT INTO FKTABLE VALUES (2, 4, 8); INSERT INTO FKTABLE VALUES (3, 6, 12); INSERT INTO FKTABLE VALUES (NULL, NULL, 0); -- Insert failed rows into FK TABLE INSERT INTO FKTABLE VALUES (100, 2, 4); ! ERROR: constrname2 referential integrity violation - key referenced from fktable not found in pktable INSERT INTO FKTABLE VALUES (2, 2, 4); ! ERROR: constrname2 referential integrity violation - key referenced from fktable not found in pktable INSERT INTO FKTABLE VALUES (NULL, 2, 4); ! ERROR: constrname2 referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values INSERT INTO FKTABLE VALUES (1, NULL, 4); ! ERROR: constrname2 referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values -- Check FKTABLE SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 2 | 4 ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! (5 rows) ! -- Delete a row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; -- Check FKTABLE to check for removal SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! -1 | -2 | 4 ! (5 rows) ! -- Delete another row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; -- Check FKTABLE (should be no change) SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 2 | 4 | 8 ! 3 | 6 | 12 ! | | 0 ! -1 | -2 | 4 ! (5 rows) ! -- Update a row from PK TABLE UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; -- Check FKTABLE for update of matched row SELECT * FROM FKTABLE; ! ftest1 | ftest2 | ftest3 ! --------+--------+-------- ! 1 | 3 | 5 ! 3 | 6 | 12 ! | | 0 ! -1 | -2 | 4 ! -1 | -2 | 8 ! (5 rows) ! DROP TABLE PKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" DROP TABLE FKTABLE; -- -- First test, check with no on delete or on update -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 'Test1'); INSERT INTO PKTABLE VALUES (2, 'Test2'); INSERT INTO PKTABLE VALUES (3, 'Test3'); INSERT INTO PKTABLE VALUES (4, 'Test4'); INSERT INTO PKTABLE VALUES (5, 'Test5'); -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2); INSERT INTO FKTABLE VALUES (2, 3); --- 57,227 ---- DROP TABLE PKTABLE; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" + ERROR: IndexGetRelation: can't find index id 0 DROP TABLE FKTABLE; + NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" + NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" -- -- check set NULL and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, CONSTRAINT constrname FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET NULL ON UPDATE SET NULL); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); + ERROR: INSERT has more expressions than target columns -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (1, 3, 5); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 4, 8); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (3, 6, 12); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, NULL, 0); + ERROR: Relation 'fktable' does not exist -- Insert failed rows into FK TABLE INSERT INTO FKTABLE VALUES (100, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (1, NULL, 4); ! ERROR: Relation 'fktable' does not exist -- Check FKTABLE SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Delete a row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Check FKTABLE for removal of matched row SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Delete another row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Check FKTABLE (should be no change) SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Update a row from PK TABLE UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; -- Check FKTABLE for update of matched row SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE PKTABLE; ! ERROR: IndexGetRelation: can't find index id 0 DROP TABLE FKTABLE; + ERROR: table "fktable" does not exist -- -- check set default and table constraint on multiple columns -- CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 text, PRIMARY KEY(ptest1, ptest2) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int DEFAULT -1, ftest2 int DEFAULT -2, ftest3 int, CONSTRAINT constrname2 FOREIGN KEY(ftest1, ftest2) REFERENCES PKTABLE MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET DEFAULT); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert a value in PKTABLE for default INSERT INTO PKTABLE VALUES (-1, -2, 'The Default!'); + ERROR: INSERT has more expressions than target columns -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 2, 'Test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 'Test1-2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 'Test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (3, 6, 'Test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (4, 8, 'Test4'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (5, 10, 'Test5'); + ERROR: INSERT has more expressions than target columns -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (1, 3, 5); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 4, 8); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (3, 6, 12); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, NULL, 0); + ERROR: Relation 'fktable' does not exist -- Insert failed rows into FK TABLE INSERT INTO FKTABLE VALUES (100, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 4); ! ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (1, NULL, 4); ! ERROR: Relation 'fktable' does not exist -- Check FKTABLE SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Delete a row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=1 and ptest2=2; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Check FKTABLE to check for removal SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Delete another row from PK TABLE DELETE FROM PKTABLE WHERE ptest1=5 and ptest2=10; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Check FKTABLE (should be no change) SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Update a row from PK TABLE UPDATE PKTABLE SET ptest1=1 WHERE ptest1=2; -- Check FKTABLE for update of matched row SELECT * FROM FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE PKTABLE; ! ERROR: IndexGetRelation: can't find index id 0 DROP TABLE FKTABLE; + ERROR: table "fktable" does not exist -- -- First test, check with no on delete or on update -- CREATE TABLE PKTABLE ( ptest1 int PRIMARY KEY, ptest2 text ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int REFERENCES PKTABLE MATCH FULL, ftest2 int ); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert test data into PKTABLE INSERT INTO PKTABLE VALUES (1, 'Test1'); + ERROR: Cannot insert a duplicate key into unique index pktable_pkey INSERT INTO PKTABLE VALUES (2, 'Test2'); INSERT INTO PKTABLE VALUES (3, 'Test3'); + ERROR: Cannot insert a duplicate key into unique index pktable_pkey INSERT INTO PKTABLE VALUES (4, 'Test4'); + ERROR: Cannot insert a duplicate key into unique index pktable_pkey INSERT INTO PKTABLE VALUES (5, 'Test5'); + ERROR: Cannot insert a duplicate key into unique index pktable_pkey -- Insert successful rows into FK TABLE INSERT INTO FKTABLE VALUES (1, 2); INSERT INTO FKTABLE VALUES (2, 3); *************** *** 261,271 **** SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- - 1 | Test1 - 2 | Test2 3 | Test3 4 | Test4 5 | Test5 (5 rows) -- Delete a row from PK TABLE (should fail) --- 244,254 ---- SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- 3 | Test3 4 | Test4 5 | Test5 + 1 | Test2 + 2 | Test2 (5 rows) -- Delete a row from PK TABLE (should fail) *************** *** 277,286 **** SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- - 1 | Test1 - 2 | Test2 3 | Test3 4 | Test4 (4 rows) -- Update a row from PK TABLE (should fail) --- 260,269 ---- SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- 3 | Test3 4 | Test4 + 1 | Test2 + 2 | Test2 (4 rows) -- Update a row from PK TABLE (should fail) *************** *** 292,697 **** SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- - 1 | Test1 - 2 | Test2 3 | Test3 0 | Test4 (4 rows) DROP TABLE PKTABLE; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" DROP TABLE FKTABLE; -- MATCH unspecified -- Base test restricting update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable -- Show FKTABLE SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (5 rows) ! -- Try to update something that should fail UPDATE PKTABLE set ptest2=5 where ptest2=2; ! ERROR: constrname3 referential integrity violation - key in pktable still referenced from fktable -- Try to update something that should succeed UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; -- Try to delete something that should fail DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3; ! ERROR: constrname3 referential integrity violation - key in pktable still referenced from fktable -- Try to delete something that should work DELETE FROM PKTABLE where ptest1=2; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | test1 ! 1 | 3 | 3 | test2 ! 1 | 3 | 4 | test3 (3 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (5 rows) ! DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; -- cascade update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE CASCADE ON UPDATE CASCADE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable -- Show FKTABLE SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (5 rows) ! -- Try to update something that will cascade UPDATE PKTABLE set ptest2=5 where ptest2=2; -- Try to update something that should not cascade UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 4 | 5 | test4 ! 1 | 5 | 3 | test1 ! 1 | 3 | 3 | test2 ! 1 | 3 | 4 | test3 ! (4 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 1 | 5 | 3 | 1 ! (5 rows) ! -- Try to delete something that should cascade DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 4 | 5 | test4 ! 1 | 3 | 3 | test2 ! 1 | 3 | 4 | test3 (3 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (4 rows) ! -- Try to delete something that should not have a cascade DELETE FROM PKTABLE where ptest1=2; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 1 | 3 | 3 | test2 ! 1 | 3 | 4 | test3 ! (2 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (4 rows) ! DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; -- set null update / set default delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET DEFAULT ON UPDATE SET NULL); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); INSERT INTO FKTABLE VALUES (2, 3, 4, 1); INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable -- Show FKTABLE SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | 1 ! 2 | 3 | 4 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (6 rows) ! -- Try to update something that will set null UPDATE PKTABLE set ptest2=5 where ptest2=2; -- Try to update something that should not set null UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 3 | 4 | test3 ! 2 | 4 | 5 | test4 ! 1 | 5 | 3 | test1 ! 1 | 2 | 3 | test2 ! (4 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 2 | 3 | 4 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 1 | | 3 | 1 ! (6 rows) ! -- Try to delete something that should set default DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 4 | 5 | test4 ! 1 | 5 | 3 | test1 ! 1 | 2 | 3 | test2 (3 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 1 | | 3 | 1 ! 0 | | | 1 ! (6 rows) ! -- Try to delete something that should not set default DELETE FROM PKTABLE where ptest2=5; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 4 | 5 | test4 ! 1 | 2 | 3 | test2 ! (2 rows) ! ! SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 1 | | 3 | 1 ! 0 | | | 1 ! (6 rows) DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; -- set default update / set null delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET NULL ON UPDATE SET DEFAULT); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5'); -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); INSERT INTO FKTABLE VALUES (2, 3, 4, 1); INSERT INTO FKTABLE VALUES (2, 4, 5, 1); INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable -- Show FKTABLE SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 1 | 2 | 3 | 1 ! 2 | 3 | 4 | 1 ! 2 | 4 | 5 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! (7 rows) ! -- Try to update something that will fail UPDATE PKTABLE set ptest2=5 where ptest2=2; ! ERROR: constrname3 referential integrity violation - key referenced from fktable not found in pktable -- Try to update something that will set default UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2; UPDATE PKTABLE set ptest2=10 where ptest2=4; -- Try to update something that should not set default UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | 3 | 4 | test3 ! 2 | -1 | 5 | test5 ! 0 | 5 | 10 | test1 ! 2 | 10 | 5 | test4 ! 1 | 2 | 3 | test2 ! (5 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! 2 | 3 | 4 | 1 ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 0 | -1 | | 1 ! 2 | -1 | 5 | 1 ! (7 rows) ! -- Try to delete something that should set null DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | -1 | 5 | test5 ! 0 | 5 | 10 | test1 ! 2 | 10 | 5 | test4 ! 1 | 2 | 3 | test2 ! (4 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 0 | -1 | | 1 ! 2 | -1 | 5 | 1 ! | | | 1 ! (7 rows) ! -- Try to delete something that should not set null DELETE FROM PKTABLE where ptest2=5; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 | ptest3 | ptest4 ! --------+--------+--------+-------- ! 2 | -1 | 5 | test5 ! 2 | 10 | 5 | test4 ! 1 | 2 | 3 | test2 (3 rows) SELECT * from FKTABLE; ! ftest1 | ftest2 | ftest3 | ftest4 ! --------+--------+--------+-------- ! | 2 | 3 | 2 ! 2 | | 3 | 3 ! | 2 | 7 | 4 ! | 3 | 4 | 5 ! 0 | -1 | | 1 ! 2 | -1 | 5 | 1 ! | | | 1 ! (7 rows) ! DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: columns referenced in foreign key constraint not found. --- 275,640 ---- SELECT * FROM PKTABLE; ptest1 | ptest2 --------+-------- 3 | Test3 + 1 | Test2 + 2 | Test2 0 | Test4 (4 rows) DROP TABLE PKTABLE; NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" + ERROR: IndexGetRelation: can't find index id 0 DROP TABLE FKTABLE; + NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" + NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" -- MATCH unspecified -- Base test restricting update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); + ERROR: INSERT has more expressions than target columns -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); + ERROR: Relation 'fktable' does not exist -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: Relation 'fktable' does not exist -- Show FKTABLE SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to update something that should fail UPDATE PKTABLE set ptest2=5 where ptest2=2; ! ERROR: Unable to identify an operator '=' for types 'text' and 'int4' ! You will have to retype this query using an explicit cast -- Try to update something that should succeed UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Try to delete something that should fail DELETE FROM PKTABLE where ptest1=1 and ptest2=2 and ptest3=3; ! ERROR: Unable to identify an operator '=' for types 'text' and 'int4' ! You will have to retype this query using an explicit cast -- Try to delete something that should work DELETE FROM PKTABLE where ptest1=2; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 -- cascade update/delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE CASCADE ON UPDATE CASCADE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); + ERROR: INSERT has more expressions than target columns -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); + ERROR: Relation 'fktable' does not exist -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: Relation 'fktable' does not exist -- Show FKTABLE SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to update something that will cascade UPDATE PKTABLE set ptest2=5 where ptest2=2; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Try to update something that should not cascade UPDATE PKTABLE set ptest1=1 WHERE ptest2=3; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should cascade DELETE FROM PKTABLE where ptest1=1 and ptest2=5 and ptest3=3; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should not have a cascade DELETE FROM PKTABLE where ptest1=2; -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 -- set null update / set default delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET DEFAULT ON UPDATE SET NULL); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); + ERROR: INSERT has more expressions than target columns -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 3, 4, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); + ERROR: Relation 'fktable' does not exist -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: Relation 'fktable' does not exist -- Show FKTABLE SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to update something that will set null UPDATE PKTABLE set ptest2=5 where ptest2=2; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Try to update something that should not set null UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should set default DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should not set default DELETE FROM PKTABLE where ptest2=5; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) + SELECT * from FKTABLE; + ERROR: Relation 'fktable' does not exist DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 -- set default update / set null delete CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE ( ftest1 int DEFAULT 0, ftest2 int DEFAULT -1, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE ON DELETE SET NULL ON UPDATE SET DEFAULT); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + NOTICE: Illegal FOREIGN KEY definition REFERENCES "pktable" + ERROR: number of key attributes in referenced table must be equal to foreign key -- Insert Primary Key values INSERT INTO PKTABLE VALUES (1, 2, 3, 'test1'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (1, 3, 3, 'test2'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 3, 4, 'test3'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, 4, 5, 'test4'); + ERROR: INSERT has more expressions than target columns INSERT INTO PKTABLE VALUES (2, -1, 5, 'test5'); + ERROR: INSERT has more expressions than target columns -- Insert Foreign Key values INSERT INTO FKTABLE VALUES (1, 2, 3, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 3, 4, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, 4, 5, 1); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 3, 2); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (2, NULL, 3, 3); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 2, 7, 4); + ERROR: Relation 'fktable' does not exist INSERT INTO FKTABLE VALUES (NULL, 3, 4, 5); + ERROR: Relation 'fktable' does not exist -- Insert a failed values INSERT INTO FKTABLE VALUES (1, 2, 7, 6); ! ERROR: Relation 'fktable' does not exist -- Show FKTABLE SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to update something that will fail UPDATE PKTABLE set ptest2=5 where ptest2=2; ! ERROR: Unable to identify an operator '=' for types 'text' and 'int4' ! You will have to retype this query using an explicit cast -- Try to update something that will set default UPDATE PKTABLE set ptest1=0, ptest2=5, ptest3=10 where ptest2=2; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast UPDATE PKTABLE set ptest2=10 where ptest2=4; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Try to update something that should not set default UPDATE PKTABLE set ptest2=2 WHERE ptest2=3 and ptest1=1; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should set null DELETE FROM PKTABLE where ptest1=2 and ptest2=3 and ptest3=4; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 ! (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist -- Try to delete something that should not set null DELETE FROM PKTABLE where ptest2=5; + ERROR: Unable to identify an operator '=' for types 'text' and 'int4' + You will have to retype this query using an explicit cast -- Show PKTABLE and FKTABLE SELECT * from PKTABLE; ! ptest1 | ptest2 ! --------+-------- ! 3 | Test3 ! 1 | Test2 ! 0 | Test4 (3 rows) SELECT * from FKTABLE; ! ERROR: Relation 'fktable' does not exist DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: columns referenced in foreign key constraint not found. *************** *** 703,714 **** DROP TABLE FKTABLE_FAIL2; ERROR: table "fktable_fail2" does not exist DROP TABLE PKTABLE; -- Test for referencing column number smaller than referenced constraint CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2)); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'pktable_ptest1_key' for table 'pktable' CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) - ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found DROP TABLE FKTABLE_FAIL1; ! ERROR: table "fktable_fail1" does not exist DROP TABLE PKTABLE; --- 646,660 ---- DROP TABLE FKTABLE_FAIL2; ERROR: table "fktable_fail2" does not exist DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 -- Test for referencing column number smaller than referenced constraint CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2)); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'pktable_ptest1_key' for table 'pktable' + ERROR: Relation 'pktable' already exists CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE FKTABLE_FAIL1; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; + ERROR: IndexGetRelation: can't find index id 0 ======================================================================