PostgreSQL array of elements that each are a foreign key
Solution 1
No, this is not possible.
PostgreSQL is a relational DBMS, operating most efficiently on properly normalized data models. Arrays are - by definition, they are ordered sets - not relational data structures and the SQL standard therefore does not support defining foreign keys on array elements, and neither does PostgreSQL.
You can, however, build a perfectly fine database with array elements linking to primary keys in other tables. Those array elements, however, can not be declared to be foreign keys and the DBMS will therefore not maintain referential integrity.
Solution 2
It may soon be possible to do this: https://commitfest.postgresql.org/17/1252/ - Mark Rofail has been doing some excellent work on this patch!
The patch will (once complete) allow
CREATE TABLE PKTABLEFORARRAY (
ptest1 float8 PRIMARY KEY,
ptest2 text
);
CREATE TABLE FKTABLEFORARRAY (
ftest1 int[],
FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY,
ftest2 int
);
However, author currently needs help to rebase the patch (beyond my own ability) so anyone reading this who knows Postgres internals please help if you can.
Zach
Updated on December 17, 2021Comments
-
Zach over 2 years
I am attempting to create a DB for my app and one thing I'd like to find the best way of doing is creating a one-to-many relationship between my
Users
andItems
tables.I know I can make a third table,
ReviewedItems
, and have the columns be aUser
id and anItem
id, but I'd like to know if it's possible to make a column inUsers
, let's sayreviewedItems
, which is an integer array containing foreign keys toItems
that theUser
has reviewed.If PostgreSQL can do this, please let me know! If not, I'll just go down my third table route.
-
a_horse_with_no_name over 7 yearsYou can define a constraint trigger that would check that. But I'm not sure if it works reliably in all cases.
-
Luan Huynh over 7 years@a_horse_with_no_name: could you give an example about
reliably in all cases
? You mean sometimes trigger can be failed ? Thanks. -
a_horse_with_no_name over 7 years@LuanHuynh: I don't recall the (technical) details, but the last time this was discussed on the mailing list someone mentioned that a corresponding constraint trigger might not catch all cases - but that might no longer be true
-
Zach over 7 yearsThanks, I guess I'll just make a relationship table for them
-
Victor about 6 yearsvery HOT indeed...
CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int )
-
yoonghm almost 6 yearsThe patch is awaiting author to follow up. Until the feature is available, you will have to use trigger to check integrity which may be slow.
-
OrangeDog over 5 years@a_horse_with_no_name can you post an answer with an example of such a constraint?
-
Teocali almost 5 yearsshame, it doesn't seems to have been included :(
-
Muhammad Asim over 4 yearsdoes it increase performance somehow ? Or should someone stick to the third table approach ?
-
M Imam Pratama almost 4 years2020, still unsupported: postgresql.org/docs/13/unsupported-features-sql-standard.html
-
karianpour over 3 yearsThis is really useful.
-
Jean Monet over 3 yearsworkaround / re-design: having a separate many-to-many table
-
Phil Frost almost 3 yearsApparently, sql:2016 does support this behavior. Unfortunately the standard isn't freely available, but you can see it referenced in both Microsoft's documentation and PostgreSQL's list of unsupported standard features.
-
user45392 almost 3 yearsPer the patch email thread, as of July 2021 it appears that this "still requires some major surgery." So we could still have a while.
-
m4heshd about 2 yearsHoly moly it's 2022 and still unsupported.
-
Lalit Fauzdar about 2 yearsIf it's still unsupported, why these upvotes? Am I missing something?
-
John O almost 2 years@LalitFauzdar Mostly because it's awesome. You might be missing how awesome this would be.
-
Moshe Katz almost 2 years@JohnO It would definitely be awesome, but upvotes are really supposed to be for useful answers and this answer isn't currently useful because the behavior it describes is still not implemented. As of right now, there is only one truly correct answer to the question: "you can't do it". (I upvoted this one too, but that probably wasn't really the right thing to do.)