\c table_name_test_db;
set dolphin.lower_case_table_names TO 0;
--
--FOR BLACKLIST FEATURE: REFERENCES/INHERITS/WITH OIDS/RULE/CREATE TYPE/DOMAIN is not supported.
--
--
-- ALTER_TABLE
-- add attribute
--
CREATE TABLE Tmp (initial int4);
COMMENT ON TABLE tmp_wrong IS 'table comment';
ERROR: relation "tmp_wrong" does not exist
COMMENT ON TABLE Tmp IS 'table comment';
COMMENT ON TABLE Tmp IS NULL;
ALTER TABLE Tmp ADD COLUMN xmin integer; -- fails
ERROR: column name "xmin" conflicts with a system column name
ALTER TABLE Tmp ADD COLUMN a int4 default 3;
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 int4;
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 timestamp;
ALTER TABLE Tmp ADD COLUMN w interval;
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)',
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
314159, '(1,1)', '512',
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
SELECT * FROM Tmp;
initial | 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
---------+---+------+------+-----+-----+---+-----------------------+------------------------+---+------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------+------------------------+------------------+-----------+-----------+-----------
| 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | 1995-05-01 00:30:30-07 | c | {"1995-05-01 00:30:30-07","1992-08-24 14:43:07-07","1969-12-31 16:00:00-08"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["1969-12-31 16:00:00-08" "infinity"] | 1969-12-31 16:00:00-08 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
(1 row)
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 int4;
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 timestamp;
ALTER TABLE Tmp ADD COLUMN w interval;
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)',
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
314159, '(1,1)', '512',
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]',
'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
SELECT * FROM Tmp;
initial | 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
---------+---+------+------+-----+-----+---+-----------------------+------------------------+---+------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------+------------------------+------------------+-----------+-----------+-----------
| 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | 1995-05-01 00:30:30-07 | c | {"1995-05-01 00:30:30-07","1992-08-24 14:43:07-07","1969-12-31 16:00:00-08"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["1969-12-31 16:00:00-08" "infinity"] | 1969-12-31 16:00:00-08 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4}
(1 row)
DROP TABLE Tmp;
--
-- rename - check on both non-temp and temp tables
--
CREATE TABLE Tmp (regtable int);
-- Enforce use of COMMIT instead of 2PC for temporary objects
-- CREATE TEMP TABLE Tmp (tmptable int);
ALTER TABLE Tmp RENAME TO Tmp_new;
-- SELECT * FROM Tmp;
-- SELECT * FROM Tmp_new;
-- ALTER TABLE Tmp RENAME TO Tmp_new2;
SELECT * FROM Tmp; -- should fail
ERROR: relation "Tmp" does not exist on datanode1
LINE 1: SELECT * FROM Tmp;
^
SELECT *, FROM Tmp; -- should fail
ERROR: syntax error at or near "FROM"
LINE 1: SELECT *, FROM Tmp;
^
SELECT * FROM Tmp_new;
regtable
----------
(0 rows)
-- SELECT * FROM Tmp_new2;
DROP TABLE Tmp_new;
-- DROP TABLE Tmp_new2;
CREATE TABLE Tmp (ch1 character(1));
insert into Tmp values ('asdv');
ERROR: value too long for type character(1)
CONTEXT: referenced column: ch1
DROP TABLE Tmp;
\set VERBOSITY default
-- ALTER TABLE ... RENAME on non-table relations
-- renaming indexes (FIXME: this should probably test the index's functionality)
ALTER INDEX IF EXISTS __onek_unique1 RENAME TO tmp_onek_unique1;
NOTICE: relation "__onek_unique1" does not exist, skipping
ALTER INDEX IF EXISTS __tmp_onek_unique1 RENAME TO onek_unique1;
NOTICE: relation "__tmp_onek_unique1" does not exist, skipping
ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM Tenk1;
ALTER TABLE tmp_view RENAME TO tmp_view_new;
-- hack to ensure we get an indexscan here
ANALYZE Tenk1;
set enable_seqscan to off;
set enable_bitmapscan to off;
-- 5 values, sorted
SELECT unique1 FROM Tenk1 WHERE unique1 < 5 ORDER BY unique1;
unique1
---------
(0 rows)
reset enable_seqscan;
reset enable_bitmapscan;
DROP VIEW tmp_view_new;
-- toast-like relation name
alter table Stud_Emp rename to pg_toast_stud_emp;
alter table pg_toast_stud_emp rename to Stud_Emp;
-- renaming index should rename constraint as well
ALTER TABLE Onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "Onek"
ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
ALTER TABLE Onek DROP CONSTRAINT onek_unique1_constraint_foo;
-- renaming constraint
ALTER TABLE Onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
ALTER TABLE Onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
ALTER TABLE Onek DROP CONSTRAINT onek_check_constraint_foo;
-- renaming constraint should rename index as well
ALTER TABLE Onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "Onek"
DROP INDEX onek_unique1_constraint; -- to see whether it's there
ALTER TABLE Onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
ERROR: constraint "onek_unique1_constraint" for table "Onek" does not exist
DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
ERROR: index "onek_unique1_constraint_foo" does not exist
ALTER TABLE Onek DROP CONSTRAINT onek_unique1_constraint_foo;
ERROR: constraint "onek_unique1_constraint_foo" of relation "Onek" does not exist
-- renaming constraints vs. Inheritance
CREATE TABLE Constraint_Rename_Test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
\d "Constraint_Rename_Test"
Table "public.Constraint_Rename_Test"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
Check constraints:
"con1" CHECK (a > 0)
CREATE TABLE Constraint_Rename_Test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) InheRITS (Constraint_Rename_Test);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
\d "Constraint_Rename_Test2"
ALTER TABLE Constraint_Rename_Test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
ERROR: relation "Constraint_Rename_Test2" does not exist
ALTER TABLE ONLY (Constraint_Rename_Test) RENAME CONSTRAINT con1 TO con1foo; -- fail
ALTER TABLE Constraint_Rename_Test RENAME CONSTRAINT con1 TO con1foo; -- ok
ERROR: constraint "con1" for table "Constraint_Rename_Test" does not exist
\d "Constraint_Rename_Test"
Table "public.Constraint_Rename_Test"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
Check constraints:
"con1foo" CHECK (a > 0)
\d "Constraint_Rename_Test2"
ALTER TABLE Constraint_Rename_Test ADD CONSTRAINT con2 CHECK (b > 0) NO InheRIT;
ALTER TABLE ONLY (Constraint_Rename_Test) RENAME CONSTRAINT con2 TO con2foo; -- ok
ALTER TABLE Constraint_Rename_Test RENAME CONSTRAINT con2foo TO con2bar; -- ok
\d "Constraint_Rename_Test"
Table "public.Constraint_Rename_Test"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
c | integer |
Check constraints:
"con1foo" CHECK (a > 0)
"con2bar" CHECK (b > 0) NO INHERIT
\d "Constraint_Rename_Test2"
ALTER TABLE Constraint_Rename_Test ADD CONSTRAINT con3 PRIMARY KEY (a);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "con3" for table "Constraint_Rename_Test"
ALTER TABLE Constraint_Rename_Test RENAME CONSTRAINT con3 TO con3foo; -- ok
\d "Constraint_Rename_Test"
Table "public.Constraint_Rename_Test"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | integer |
c | integer |
Indexes:
"con3foo" PRIMARY KEY, btree (a) TABLESPACE pg_default
Check constraints:
"con1foo" CHECK (a > 0)
"con2bar" CHECK (b > 0) NO INHERIT
\d "Constraint_Rename_Test2"
DROP TABLE Constraint_Rename_Test2;
ERROR: table "Constraint_Rename_Test2" does not exist
DROP TABLE Constraint_Rename_Test;
ALTER TABLE IF EXISTS Constraint_Rename_Test ADD CONSTRAINT con4 UNIQUE (a);
NOTICE: relation "Constraint_Rename_Test" does not exist, skipping
-- FOREIGN KEY CONSTRAINT adding TEST
CREATE TABLE Tmp2 (a int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "Tmp2_pkey" for table "Tmp2"
CREATE TABLE Tmp3 (a int, b int);
CREATE TABLE Tmp4 (a int, b int, unique(a,b));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "Tmp4_a_b_key" for table "Tmp4"
CREATE TABLE Tmp5 (a int, b int);
-- Insert rows into Tmp2 (PKTABLE)
INSERT INTO Tmp2 values (1);
INSERT INTO Tmp2 values (2);
INSERT INTO Tmp2 values (3);
INSERT INTO Tmp2 values (4);
-- Insert rows into Tmp3
INSERT INTO Tmp3 values (1,10);
INSERT INTO Tmp3 values (1,20);
INSERT INTO Tmp3 values (5,50);
-- Try (and fail) to add constraint due to invalid source columns
ALTER TABLE Tmp3 add constraint tmpconstr foreign key(c) references Tmp2 match full;
ERROR: column "c" referenced in foreign key constraint does not exist
-- Try (and fail) to add constraint due to invalide destination columns explicitly given
ALTER TABLE Tmp3 add constraint tmpconstr foreign key(a) references Tmp2(b) match full;
ERROR: column "b" referenced in foreign key constraint does not exist
-- Try (and fail) to add constraint due to invalid data
ALTER TABLE Tmp3 add constraint tmpconstr foreign key (a) references Tmp2 match full;
ERROR: insert or update on table "Tmp3" violates foreign key constraint "tmpconstr"
DETAIL: Key (a)=(5) is not present in table "Tmp2".
-- Delete failing row
DELETE FROM Tmp3 where a=5;
-- Try (and succeed)
ALTER TABLE Tmp3 add constraint tmpconstr foreign key (a) references Tmp2 match full;
ALTER TABLE Tmp3 drop constraint tmpconstr;
INSERT INTO Tmp3 values (5,50);
-- Try NOT VALID and then VALIDATE CONSTRAINT, but fails. Delete failure then re-validate
ALTER TABLE Tmp3 add constraint tmpconstr foreign key (a) references Tmp2 match full NOT VALID;
ALTER TABLE Tmp3 validate constraint tmpconstr;
ERROR: insert or update on table "Tmp3" violates foreign key constraint "tmpconstr"
DETAIL: Key (a)=(5) is not present in table "Tmp2".
-- Delete failing row
DELETE FROM Tmp3 where a=5;
-- Try (and succeed) and repeat to show it works on already valid constraint
ALTER TABLE Tmp3 validate constraint tmpconstr;
ALTER TABLE Tmp3 validate constraint tmpconstr;
-- Try a non-verified CHECK constraint
ALTER TABLE Tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail
ERROR: check constraint "b_greater_than_ten" is violated by some row
ALTER TABLE Tmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds
ALTER TABLE Tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails
ERROR: check constraint "b_greater_than_ten" is violated by some row
DELETE FROM Tmp3 WHERE NOT b > 10;
ALTER TABLE Tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
ALTER TABLE Tmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds
-- Test inherited NOT VALID CHECK constraints
select * from Tmp3;
a | b
---+----
1 | 20
(1 row)
CREATE TABLE Tmp6 () INHERITS (Tmp3);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
CREATE TABLE Tmp7 () INHERITS (Tmp3);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
INSERT INTO Tmp6 VALUES (6, 30), (7, 16);
ERROR: relation "Tmp6" does not exist on datanode1
LINE 1: INSERT INTO Tmp6 VALUES (6, 30), (7, 16);
^
ALTER TABLE Tmp3 ADD CONSTRAINT b_le_20 CHECK (b <= 20) NOT VALID;
ALTER TABLE Tmp3 VALIDATE CONSTRAINT b_le_20; -- fails
DELETE FROM Tmp6 WHERE b > 20;
ERROR: relation "Tmp6" does not exist on datanode1
LINE 1: DELETE FROM Tmp6 WHERE b > 20;
^
ALTER TABLE Tmp3 VALIDATE CONSTRAINT b_le_20; -- succeeds
-- An already validated constraint must not be revalidated
CREATE FUNCTION boo(int) RETURNS int IMMUTABLE STRICT LANGUAGE plpgsql AS $$ BEGIN RAISE NOTICE 'boo: %', $1; RETURN $1; END; $$;
INSERT INTO Tmp7 VALUES (8, 18);
ERROR: relation "Tmp7" does not exist on datanode1
LINE 1: INSERT INTO Tmp7 VALUES (8, 18);
^
set client_min_messages=WARNING;
ALTER TABLE Tmp7 ADD CONSTRAINT identity CHECK (b = boo(b));
ERROR: relation "Tmp7" does not exist
reset client_min_messages;
ALTER TABLE Tmp3 ADD CONSTRAINT IDENTITY check (b = boo(b)) NOT VALID;
set client_min_messages=WARNING;
ALTER TABLE Tmp3 VALIDATE CONSTRAINT identity;
reset client_min_messages;
-- Try (and fail) to create constraint from Tmp5(a) to Tmp4(a) - unique constraint on
-- Tmp4 is a,b
ALTER TABLE Tmp5 add constraint tmpconstr foreign key(a) references Tmp4(a) match full;
ERROR: there is no unique constraint matching given keys for referenced table "Tmp4"
DROP TABLE Tmp7;
ERROR: table "Tmp7" does not exist
DROP TABLE Tmp6;
ERROR: table "Tmp6" does not exist
DROP TABLE Tmp5;
DROP TABLE Tmp4;
DROP TABLE Tmp3;
DROP TABLE Tmp2;
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
-- exclusion until validated
set constraint_exclusion TO 'partition';
create table NV_Parent (d date);
create table NV_Child_2010 () Inherits (NV_Parent);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
create table NV_Child_2011 () Inherits (NV_Parent);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
alter table NV_Child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;
ERROR: relation "NV_Child_2010" does not exist
alter table NV_Child_2011 add check (d between '2011-01-01'::date and '2011-12-31'::date) not valid;
ERROR: relation "NV_Child_2011" does not exist
explain (costs off) select * from NV_Parent where d between '2011-08-01' and '2011-08-31';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on "NV_Parent"
Filter: ((d >= '2011-08-01 00:00:00'::timestamp without time zone) AND (d <= '2011-08-31 00:00:00'::timestamp without time zone))
(2 rows)
create table NV_Child_2009 (check (d between '2009-01-01'::date and '2009-12-31'::date)) Inherits (NV_Parent);
ERROR: inherits is not support in B-format database, it conflicts with multi-relation update
explain (costs off) select * from NV_Parent where d between '2011-08-01'::date and '2011-08-31'::date;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on "NV_Parent"
Filter: ((d >= ('2011-08-01')::date) AND (d <= ('2011-08-31')::date))
(2 rows)
explain (costs off) select * from NV_Parent where d between '2009-08-01'::date and '2009-08-31'::date;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on "NV_Parent"
Filter: ((d >= ('2009-08-01')::date) AND (d <= ('2009-08-31')::date))
(2 rows)
-- after validation, the constraint should be used
alter table NV_Child_2011 VALIDATE CONSTRAINT nv_child_2011_d_check;
ERROR: relation "NV_Child_2011" does not exist
explain (costs off) select * from NV_Parent where d between '2009-08-01'::date and '2009-08-31'::date;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on "NV_Parent"
Filter: ((d >= ('2009-08-01')::date) AND (d <= ('2009-08-31')::date))
(2 rows)
-- Foreign key adding test with mixed types
-- Note: these tables are TEMP to avoid name conflicts when this test
-- is run in parallel with foreign_key.sql.
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
INSERT INTO PKTABLE VALUES(42);
CREATE TABLE FKTABLE (ftest1 inet);
-- This next should fail, because int=inet does not exist
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE;
ERROR: foreign key constraint "FKTABLE_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
-- This should also fail for the same reason, but here we
-- give the column name
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE(ptest1);
ERROR: foreign key constraint "FKTABLE_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: inet and integer.
DROP TABLE FKTABLE;
-- This should succeed, even though they are different types,
-- because int=int8 exists and is a member of the integer opfamily
CREATE TABLE FKTABLE (ftest1 int8);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE;
-- Check it actually works
INSERT INTO FKTABLE VALUES(42); -- should succeed
INSERT INTO FKTABLE VALUES(43); -- should fail
ERROR: insert or update on table "FKTABLE" violates foreign key constraint "FKTABLE_ftest1_fkey"
DETAIL: Key (ftest1)=(43) is not present in table "PKTABLE".
DROP TABLE FKTABLE;
-- This should fail, because we'd have to cast numeric to int which is
-- not an implicit coercion (or use numeric=numeric, but that's not part
-- of the integer opfamily)
CREATE TABLE FKTABLE (ftest1 numeric);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE;
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
-- On the other hand, this should work because int implicitly promotes to
-- numeric, and we allow promotion on the FK side
CREATE TABLE PKTABLE (ptest1 numeric PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
INSERT INTO PKTABLE VALUES(42);
CREATE TABLE FKTABLE (ftest1 int);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references PKTABLE;
-- Check it actually works
INSERT INTO FKTABLE VALUES(42); -- should succeed
INSERT INTO FKTABLE VALUES(43); -- should fail
ERROR: insert or update on table "FKTABLE" violates foreign key constraint "FKTABLE_ftest1_fkey"
DETAIL: Key (ftest1)=(43) is not present in table "PKTABLE".
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
CREATE TABLE PKTABLE (ptest1 int, ptest2 inet,
PRIMARY KEY(ptest1, ptest2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "PKTABLE_pkey" for table "PKTABLE"
-- This should fail, because we just chose really odd types
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2) references PKTABLE;
ERROR: foreign key constraint "FKTABLE_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
DROP TABLE FKTABLE;
-- Again, so should this...
CREATE TABLE FKTABLE (ftest1 cidr, ftest2 timestamp);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
references PKTABLE(ptest1, ptest2);
ERROR: foreign key constraint "FKTABLE_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest1" are of incompatible types: cidr and integer.
DROP TABLE FKTABLE;
-- This fails because we mixed up the column ordering
CREATE TABLE FKTABLE (ftest1 int, ftest2 inet);
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1, ftest2)
references PKTABLE(ptest2, ptest1);
ERROR: foreign key constraint "FKTABLE_ftest1_fkey" cannot be implemented
DETAIL: Key columns "ftest1" and "ptest2" are of incompatible types: integer and inet.
-- As does this...
ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest2, ftest1)
references PKTABLE(ptest1, ptest2);
ERROR: foreign key constraint "FKTABLE_ftest2_fkey" cannot be implemented
DETAIL: Key columns "ftest2" and "ptest1" are of incompatible types: inet and integer.
-- temp tables should go away by themselves, need not drop them.
CREATE SCHEMA test_rename;
SET current_schema TO test_rename;
DROP TABLE IF EXISTS T2;
NOTICE: table "T2" does not exist, skipping
create table T2(AA int);
set dolphin.lower_case_table_names = 1;
-- rename to "t2"
ALTER TABLE "T2" RENAME TO T2;
ALTER TABLE IF EXISTS "T3" RENAME TO T3;
NOTICE: relation "T3" does not exist, skipping
create table "T3"(AA int);
-- rename to "t3"
ALTER TABLE IF EXISTS "T3" RENAME TO T3;
SELECT relname FROM pg_class WHERE (relname = 'T2' OR relname = 'T3' OR relname = 't2' OR relname = 't3') AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = 'test_rename') ORDER BY 1;
relname
---------
t2
t3
(2 rows)
set dolphin.lower_case_table_names = 0;
ALTER TABLE t2 RENAME TO T2;
ALTER TABLE IF EXISTS t3 RENAME TO T3;
SELECT relname FROM pg_class WHERE (relname = 'T2' OR relname = 'T3' OR relname = 't2' OR relname = 't3') AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = 'test_rename') ORDER BY 1;
relname
---------
T2
T3
(2 rows)
DROP TABLE T2;
DROP TABLE T3;
SET current_schema TO public;
DROP SCHEMA test_rename CASCADE;
-- test modify column type without constraints
drop table if exists user;
NOTICE: table "user" does not exist, skipping
create table user(id int, user_name VARCHAR(255) NOT NULL);
show create table user;
Table | Create Table
-------+-----------------------------------------------------------------------------------------------
user | SET search_path = public; +
| CREATE TABLE user ( +
| id integer, +
| user_name character varying(255) CHARACTER SET "UTF8" COLLATE utf8mb4_general_ci NOT NULL+
| ) +
| CHARACTER SET = "UTF8" COLLATE = "utf8mb4_general_ci" +
| WITH (orientation=row, compression=no);
(1 row)
ALTER TABLE user MODIFY user_name VARCHAR(45);
show create table user;
Table | Create Table
-------+-------------------------------------------------------------------------------------
user | SET search_path = public; +
| CREATE TABLE user ( +
| id integer, +
| user_name character varying(45) CHARACTER SET "UTF8" COLLATE utf8mb4_general_ci+
| ) +
| CHARACTER SET = "UTF8" COLLATE = "utf8mb4_general_ci" +
| WITH (orientation=row, compression=no);
(1 row)
drop table user;