skip to Main Content

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?

2

Answers


  1. 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. 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