Postgres check constraint on `TEXT[]` using a normalized solution

Question

Similar to other questions asked but haven't found a normalized solution for TEXT[]:

Postgres check constraint in text array for the validity of the values

does not use another table as a check

https://dba.stackexchange.com/questions/250659/constrain-array-values-to-an-allowed-set-of-values

same solution as above but mentions a normalized solution

Check if value exists in postgres array for partitioning via check constraint

similar

Postgres ENUM data type or CHECK CONSTRAINT?

great answer and is a normalized solution but only for TEXT, not TEXT[]

I have two tables articles and valid_tags. valid_tags holds text values that are only allowed. When an article is INSERTed the tags TEXT[] column must be an array of valid tag values. I need to check those values against the valid_tags table.

CREATE TABLE articles (
  tags TEXT[]
);

CREATE TABLE valid_tags (
    name TEXT
);

I'm looking for a very similar solution as Postgres ENUM data type or CHECK CONSTRAINT? but with the constraint that column color_id is TEXT[].

If you want relational constraints you should follow relational practices. Normalise your tables. — Richard Huxton
– Richard Huxton, Commented May 31, 2023 at 16:14

Laurenz Albe · Accepted Answer · 2023-05-31 16:26:14Z

1

The normalized and recommended solution is not to use an array, but a junction table between your tables. That would solve the problem implicitly.

Note that while you can write a check constraint that seems to do what you want, that check constraint is illegal and will break your database sooner or later. Don't do it.

answered May 31, 2023 at 16:26

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgres check constraint on `TEXT[]` using a normalized solution

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related