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.

Answer 1

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

Answer 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.