Postgresql CONSTRAINT on column to global set of string/text values

Question

I have a set of multiple tables in a single database which is roughly using a table inheritance pattern. The "class" is stored where required in a given table as a varchar column. Most tables have only one possible value but there are a few tables that can validly have any value. I also have a configuration table that basically lists all valid "class" names.

For the sake of maintainability and data consistency, I want to be able to impose a constraint on the various tables that can prevents operations from inserting records that reference a "class" that doesn't exist but I want the valid list to be "global" to the DB and I want it to be as performant as possible.

My intuition is to apply the constraint with something like class_name IN ('car', 'bike', 'fruit'). So far I've explored trying a CHECK CONSTRAINT with a subquery from the configuration table which isn't valid SQL and tried getting a stored FUNCTION to return the required set but that doesn't seem to be a kind of return type PostgreSQL functions support. I also tried assigning the set to a "global" config variable via ALTER DATABASE db SET abc.xyz = 1 but again, a set of values for IN doesn't appear to be valid SQL.

I know I could achieve this with a FOREIGN KEY but that means deletion of the configuration records impacts the values stored in the other tables and I'm concerned about performance.

I'm aware of the ENUM data type, but I want to store the "class" name as a varchar because text is just easier to work with.

Is there an approach that I might have missed that could be both "global" (i.e. maintained in one location in the DB only), stored as plain text and relatively performant?

user330315 · Accepted Answer · 2022-04-19 05:35:27Z

4

You can use a domain with a check constraint:

create domain class_name_domain as text
  constraint check_name check (value in ('car', 'bike'));
  
create table the_table
(
  id         integer primary key, 
  class_name class_name_domain
);

answered Apr 19, 2022 at 5:35

user330315

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

2 Comments

arkore Over a year ago

This looks like exactly what I need. I've marked it as the correct answer even though I haven't tested it because reading the docs indicates it does just what I need for this purpose and is SQL standard, not just PostgreSQL. If only I knew what was the right keyword to search for. Thanks!

arkore Over a year ago

Further feedback, after testing it seems that there is a little bit of a complication when updating a domain. But otherwise seems to do just what I needed. For future folk, it seems you need to drop the constraint (not the domain) then reapply it to update the check condition. i.e.

ALTER DOMAIN class_name_domain DROP CONSTRAINT check_name; ALTER DOMAIN class_name_domain ADD CONSTRAINT check_name CHECK(VALUE IN ('car', 'bike', 'boat', 'rocket'));

Collectives™ on Stack Overflow

Postgresql CONSTRAINT on column to global set of string/text values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related