Joe, that is good news. When will be 7.4 availbable?
Also,
what i actually wanted is to ckeck that if lets say ARRAY[1,2,3]
is inserted but 1,2 or 3 (the the entire array) is not present in the reference table.
Will that be available too ?
Alex
Joe Conway wrote:
Alex wrote:
a) index array fields
It is possible in 7.4 beta, but not before.
b) to create a foreign key constraint from the array
Same answer. In 7.4:
# CREATE TABLE t1 (id int[] PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
# INSERT INTO t1 VALUES(ARRAY[1,2,3]);
INSERT 2028925 1
# CREATE TABLE t2 (id int, fk int[] REFERENCES t1(id));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
# INSERT INTO t2 VALUES(1, ARRAY[1,2,3]);
INSERT 2028935 1
# INSERT INTO t2 VALUES(2, ARRAY[3,4,5]);
ERROR: insert or update on "t2" violates foreign key constraint "$1"
DETAIL: Key (fk)=({3,4,5}) is not present in "t1".
Joe
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
