PostgreSQL array of elements that each are a foreign key

Question

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 and Items tables.

I know I can make a third table, ReviewedItems, and have the columns be a User id and an Item id, but I'd like to know if it's possible to make a column in Users, let's say reviewedItems, which is an integer array containing foreign keys to Items that the User has reviewed.

If PostgreSQL can do this, please let me know! If not, I'll just go down my third table route.

There have been patches to add this feature to Postgres, see blog.2ndquadrant.com/… (2012) and postgresql.org/message-id/… (2017). They haven't been accepted as yet, but hopefully one day. — Simon Kissane
– Simon Kissane, Commented Sep 10, 2017 at 0:26

reducing activity · Accepted Answer · 2025-10-29 12:14:56Z

196

Please note that this answer describes a feature that has not yet been implemented (Dec 2024).

There is some abandoned work on that, see: https://commitfest.postgresql.org/17/1252/

The patch would have (once complete) allowed

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, there seems to be not an active work, with no activity in 2020s.

edited Oct 29 at 12:14

reducing activity

2,3974 gold badges45 silver badges75 bronze badges

answered May 21, 2018 at 1:57

Jarym

2,4962 gold badges17 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Victor Over a year ago

very HOT indeed...

CREATE TABLE FKTABLEFORARRAY ( ftest1 int[], FOREIGN KEY (EACH ELEMENT OF ftest1) REFERENCES PKTABLEFORARRAY, ftest2 int )

Teocali Over a year ago

shame, it doesn't seems to have been included :(

M Imam Pratama Over a year ago

2020, still unsupported: postgresql.org/docs/13/unsupported-features-sql-standard.html

user45392 Over a year ago

Per the patch email thread, as of July 2021 it appears that this "still requires some major surgery." So we could still have a while.

Moshe Katz Over a year ago

@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.)

|

Patrick · Accepted Answer · 2023-01-01 16:39:52Z

179

No, this is not possible.

PostgreSQL is a relational DBMS, operating most efficiently on properly normalized data models. Arrays are not relational data structures - by definition they are sets - and while the SQL standard supports defining foreign keys on array elements, PostgreSQL currently does not support it. There is an (dormant? no activity on commitfest since February 2021) effort to implement this - see this answer to this same question - so the functionality might one day be supported.

For the time being 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. Using an appropriate set of triggers (both on the referenced and referencing tables, as a change in either would have to trigger a check and possible update on the other) one would in principle be able to implement referential integrity over the array elements but the performance is unlikely to be stellar (because indexes would not be used, for instance).

edited Jan 1, 2023 at 16:39

answered Dec 9, 2016 at 6:57

Patrick

33k7 gold badges73 silver badges102 bronze badges

8 Comments

user330315 Over a year ago

You can define a constraint trigger that would check that. But I'm not sure if it works reliably in all cases.

user330315 Over a year ago

@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

OrangeDog Over a year ago

@a_horse_with_no_name can you post an answer with an example of such a constraint?

Jean Monet Over a year ago

workaround / re-design: having a separate many-to-many table

Phil Frost Over a year ago

Apparently, 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.

|

Collectives™ on Stack Overflow

PostgreSQL array of elements that each are a foreign key

2 Answers 2

12 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related