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

Postgresql – Check many values are not equal in a single statement

I’m creating a constraint in a table so that none of 8 columns must be equal to each other.

I wanted to do it like this:

alter table mytable
    add constraint no_duplicate_values
        check (col_1 <> col_2 <> col__3 <> col__4 <> col__5 <> col__6 <> col__7 <> col__8);

But of course this doesn’t work because <> only accepts two operands, left and right.

Is there an elegant, single-statement alternative that’s equivalent?

Tags:

2

Answers


  1. 0

    To do that you need to use logical AND operator. There is no elegant way BTW, as far as I know. Could you try this one:

           CHECK (
        col_1 <> col_2 AND
        col_1 <> col_3 AND
        col_1 <> col_4 AND
        col_1 <> col_5 AND
        col_1 <> col_6 AND
        col_1 <> col_7 AND
        col_1 <> col_8 AND
        col_2 <> col_3 AND
        col_2 <> col_4 AND
        col_2 <> col_5 AND
        col_2 <> col_6 AND
        col_2 <> col_7 AND
        col_2 <> col_8 AND
        col_3 <> col_4 AND
        col_3 <> col_5 AND
        col_3 <> col_6 AND
        col_3 <> col_7 AND
        col_3 <> col_8 AND
        col_4 <> col_5 AND
        col_4 <> col_6 AND
        col_4 <> col_7 AND
        col_4 <> col_8 AND
        col_5 <> col_6 AND
        col_5 <> col_7 AND
        col_5 <> col_8 AND
        col_6 <> col_7 AND
        col_6 <> col_8 AND
        col_7 <> col_8
    )
    Login or Signup to reply.
  2. 0

    I would suggest a simple helper function for a more or less generic solution.

    create or replace function row_unique(jr jsonb) returns boolean language sql as 
$$
 select count(value) = count(distinct value) from jsonb_each_text(jr);
$$;

    and then

    alter table mytable add constraint no_duplicate_values
check (row_unique(to_jsonb(mytable)));

    Demo

    However, as Thorsten Kettner comments, it may be worth revisiting your data design.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search