Arrays and Indices / Foreign Keys

[Alex]

Hi,
I am using Arrays to store a list of values. Is it possible to

a) index array fields
b) to create a foreign key constraint from the array

Thanks
Alex

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Joe Conway]

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

[Alex]

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

[Joe Conway]

Alex wrote:

Joe, that is good news.
When will be 7.4 availbable?
Beta2 is just starting. There isn't a firm date for the 7.4 release that
I'm aware of, but start looking for it in mid-September.
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 ?

I was afraid that's what you were after. I think the answer is no, at
least not with standard RI constraints. You might be able to do
something with a custom trigger though.

Joe
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org