-- Basic one column, two table setup
CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
--- This next should fail, because text=int does not exist
-CREATE TABLE FKTABLE (ftest1 text REFERENCES pktable);
+-- This next should fail, because inet=int does not exist
+CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- This should also fail for the same reason, but here we
-- give the column name
-CREATE TABLE FKTABLE (ftest1 text REFERENCES pktable(ptest1));
+CREATE TABLE FKTABLE (ftest1 inet REFERENCES pktable(ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- This should succeed, even though they are different types
-- because varchar=int does exist
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
DROP TABLE PKTABLE;
-- Two columns, two tables
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2));
+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 datetime, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable);
ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
You will have to retype this query using an explicit cast
-- This fails because we mixed up the column ordering
-CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
+CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- As does this...
-CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
+CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- And again..
-CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
+CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest2, ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
+ERROR: Unable to identify an operator '=' for types 'integer' and 'inet'
You will have to retype this query using an explicit cast
-- This works...
-CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
+CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest2, ftest1) REFERENCES pktable(ptest2, ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
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"
-- As does this
-CREATE TABLE FKTABLE (ftest1 int, ftest2 text, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
+CREATE TABLE FKTABLE (ftest1 int, ftest2 inet, FOREIGN KEY(ftest1, ftest2) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
DROP TABLE FKTABLE;
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
DROP TABLE PKTABLE;
-- Two columns, same table
-- Make sure this still works...
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
+CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
DROP TABLE PKTABLE;
-- And this,
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
+CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
DROP TABLE PKTABLE;
-- This shouldn't (mixed up columns)
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
+CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest3,
ptest4) REFERENCES pktable(ptest2, ptest1));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
+ERROR: Unable to identify an operator '=' for types 'integer' and 'inet'
You will have to retype this query using an explicit cast
-- Nor should this... (same reason, we have 4,3 referencing 1,2 which mismatches types
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
+CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable(ptest1, ptest2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-- Not this one either... Same as the last one except we didn't defined the columns being referenced.
-CREATE TABLE PKTABLE (ptest1 int, ptest2 text, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
+CREATE TABLE PKTABLE (ptest1 int, ptest2 inet, ptest3 int, ptest4 inet, PRIMARY KEY(ptest1, ptest2), FOREIGN KEY(ptest4,
ptest3) REFERENCES pktable);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
--
-- Now some cases with inheritance
ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable
-- fails (1,1) is being referenced (twice)
update pktable set base1=3 where base1=1;
-ERROR: <unnamed> referential integrity violation - key in pktable still referenced from pktable
+ERROR: <unnamed> referential integrity violation - key referenced from pktable not found in pktable
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
delete from pktable where base2=2;
delete from pktable where base1=2;
drop table pktable_base;
-- 2 columns (2 tables), mismatched types
create table pktable_base(base1 int not null);
-create table pktable(ptest1 text, primary key(base1, ptest1)) inherits (pktable_base);
+create table pktable(ptest1 inet, primary key(base1, ptest1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
-- just generally bad types (with and without column references on the referenced table)
create table fktable(ftest1 cidr, ftest2 int[], foreign key (ftest1, ftest2) references pktable);
ERROR: Unable to identify an operator '=' for types 'cidr' and 'integer'
You will have to retype this query using an explicit cast
-- let's mix up which columns reference which
-create table fktable(ftest1 int, ftest2 text, foreign key(ftest2, ftest1) references pktable);
+create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-create table fktable(ftest1 int, ftest2 text, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
+create table fktable(ftest1 int, ftest2 inet, foreign key(ftest2, ftest1) references pktable(base1, ptest1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-create table fktable(ftest1 int, ftest2 text, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
+create table fktable(ftest1 int, ftest2 inet, foreign key(ftest1, ftest2) references pktable(ptest1, base1));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
+ERROR: Unable to identify an operator '=' for types 'integer' and 'inet'
You will have to retype this query using an explicit cast
drop table pktable;
drop table pktable_base;
-- 2 columns (1 table), mismatched types
create table pktable_base(base1 int not null, base2 int);
-create table pktable(ptest1 text, ptest2 text[], primary key(base1, ptest1), foreign key(base2, ptest2) references
+create table pktable(ptest1 inet, ptest2 inet[], primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(base1, ptest1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text[]' and 'text'
+ERROR: Unable to identify an operator '=' for types 'inet[]' and 'inet'
You will have to retype this query using an explicit cast
-create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(base2, ptest2) references
+create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(base2, ptest2) references
pktable(ptest1, base1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'integer' and 'text'
+ERROR: Unable to identify an operator '=' for types 'integer' and 'inet'
You will have to retype this query using an explicit cast
-create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(ptest2, base2) references
+create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
pktable(base1, ptest1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
-create table pktable(ptest1 text, ptest2 text, primary key(base1, ptest1), foreign key(ptest2, base2) references
+create table pktable(ptest1 inet, ptest2 inet, primary key(base1, ptest1), foreign key(ptest2, base2) references
pktable(base1, ptest1)) inherits (pktable_base);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
-ERROR: Unable to identify an operator '=' for types 'text' and 'integer'
+ERROR: Unable to identify an operator '=' for types 'inet' and 'integer'
You will have to retype this query using an explicit cast
drop table pktable;
ERROR: table "pktable" does not exist