PostgreSQL How to put constraint on column of type arrays

Question

I have two tables in Postgresql.

table_1 contains sources

Sources
A_src 
B_src
C_src

table_2 is as follows

Result;Result_sources
2.3; {A_src;C_src}
43; {B_src;C_src}
11; {A_src;B_src;C_src}

How can I write a constraint on table_2.Result_sources which enables its array entries to only come from table_1.Sources column

I think you need trigger for it, you cant use check(other table) or FK constraints (different data types) — Vao Tsun
– Vao Tsun, Commented Dec 6, 2017 at 9:54

Laurenz Albe · Accepted Answer · 2017-12-06 10:32:12Z

1

You cannot do that, and a trigger is no substitute since you cannot avoid race conditions unless you use SERIALIZABLE transactions.

This problem begs for normalization: create a table that implements and m-to-n relationship between table_1 and table_2.

answered Dec 6, 2017 at 10:32

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

1 Answer 1