Question 225495 in PostgreSQL Question posted in
The official documentation can be found here.

In PostgreSQL how do you have multiple check constraints reference the same array of values?

Many of the columns in different tables in my database have the constraint between 1 and 3 (equivalent to any (array[1,2,3]) since they’re all integers). This may need to be changed to allow 4s in the future, so I want to make them all reference a variable.

This database has an singleton info table, so I thought I could maybe add a column to that and reference the sole value in it? I tried the code below but it just said "column privacy_options does not exist"

alter table info add privacy_options integer array;
update info set privacy_options = array [1,2,3];
alter table system_config add check (default_privacy_shown = any (info(privacy_options)));
Tags:

2

Answers


  1. 0

    You cannot use subqueries in the check constraint. But this should work for you:

    alter table system_config add check (default_privacy_shown in (1, 2, 3))

    Also, ensure the coulmn system_config.default_privacy_shown exists.

    Add it if needed before adding constraint: alter table system_config add default_privacy_shown integer.

    Login or Signup to reply.
  2. 0

    You may make a very simple helper function:

    create or replace function privacy_helper(v integer) returns boolean
immutable language sql as
$$
  select v in (1,2,3);
$$;

    and then 40+ times

    alter table the_table add check (privacy_helper(the_column));

    Any change of the target set will be done only once, in the function.

    Here is the function using info table:

    create or replace function privacy_helper(v integer) returns boolean
stable language sql as
$$
  select v = any((select privacy_options from info));
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search