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