regression.diffs
application/octet-stream
Filename: regression.diffs
Type: application/octet-stream
Part: 0
Message:
Re: Re: Call for platforms
*** ./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
======================================================================