0

I have a PostgreSQL table containing a bunch of foreign keys pointing to another table:

create table foo (
    hourly00 uuid references hourly(id),
    hourly01 uuid references hourly(id),
    -- etc.
    hourly23 uuid references hourly(id),
    -- omit rest
);

Admittedly, this looks silly (and I am lucky to have only 24 of such keys), but I have not found anything better.

In the meantime, I stumbled on PostgreSQL documentation about arrays (I did not know about PostgreSQL arrays before). I got an idea and I tried this:

create table foo (
    hourly uuid[24] references hourly(id)
);

which, of course, immediately resulted in an error:

ERROR:  foreign key constraint "main_hourly_fkey" cannot be implemented
DETAIL:  Key columns "hourly" and "id" are of incompatible types: uuid[] and uuid.

But then I got another idea — to ask about my situation on StackOverflow. So my question is: does PostgreSQL provide means for doing something like this, i.e. for having an array of foreign keys in a table? Is it perhaps doable with some PL/*? Or perhaps with some other database system? I am willing to do my own research, but I have no idea as to where to look.

toomas
  • 520
  • 5
  • 15
  • I don't think that it possible. But you may use an array of IDs (integer I assume) and check if that IDs is valid during INSERT/UPDATE by using a trigger. – endo64 Jul 23 '22 at 19:52
  • 2
    "*Admittedly, this looks silly, but I have not found anything better.*" - create an extra table consisting of `hour smallint CHECK (hour BETWEEN 0 AND 23), hourly_id uuid references hourly, foo_id uuid references foo, PRIMARY KEY (hour, hourly_id, foo_id)` – Bergi Jul 23 '22 at 21:58
  • ""*Does PostgreSQL provide means for doing something like this*" - no, this is the main limitation of using arrays in denormalised schemas. You can do a lot with triggers, sure, but Postgres does not provide this out of the box. – Bergi Jul 23 '22 at 22:01
  • 1
    Does this answer your question? [PostgreSQL array of elements that each are a foreign key](https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key) – Daniel Morell Jan 06 '23 at 16:40

0 Answers0