By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,259 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

Arrays and Indices / Foreign Keys

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a
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

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.