Re: Arrays and Indices / Foreign Keys

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