several columns with each value unique in all (each) of these columns ; treated by 1 transaction-CodePudding

I am searching the mean to do, in Postgresql, that each value in each one of several columns is unique in all (each) of these columns.

Example, with 2 columns :

col_1  col_2
--------------
 a      b         # ok
 c      d         # ok
 e                # ok
 f      a         # forbidden
 b                # forbidden
        b         # forbidden

I need that each writing in these columns is treated by 1 transaction, especially (for some row) :

copy col_2 in col_1 and delete col_2

Has someone an idea about ?

CodePudding user response：

This probably should be a comment, but then it is too long and I cannot format the code example there.
You cannot get a unique constraint nor index across multiple columns. You may be able to with a trigger, but even there it is not simple:

create or replace function unique_over_2col() 
  returns trigger 
 language plpgsql
as $$
begin 
    if exists ( select null 
                  from test 
                 where new.col_1 = col_1
                    or new.col_1 = col_2 
                    or new.col_2 = col_1 
                    or new.col_2 = col_2
              )
        then 
           return null; 
        else 
           return new;
    end if; 
end; 
$$; 

create trigger test_biur
    before insert or update 
     on <your table name here> 
     for each row 
         execute function unique_over_2col();

Your trigger will specifically have to compare every new.column against every existing column. The above just does so with the 2 columns you mentioned and that leads to 4 comparisons. Your several columns will expand this dramatically. I'll repeat the advice by @Bergi normalize your schema.

BTW: please explain copy col_2 in col_1 and delete col_2 it is totally meaningless. Perhaps it would be better to explain the business issue you are faced with rather than how you are trying to solve it.

CodePudding user response：

A bit ugly but working solution:

CREATE TABLE tablename (col1 integer, col2 integer);

CREATE OR REPLACE FUNCTION pr_tablename_insertuniqueonly()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
    DECLARE
        v_new_values integer[] =  ARRAY[NEW.col1, NEW.col2];
    BEGIN
        IF (NEW.col1=NEW.col2) THEN
               RETURN null;
        END IF;
        IF EXISTS(SELECT 1 FROM tablename t WHERE t.col1 = ANY(v_new_values) OR t.col2 = ANY(v_new_values)) THEN
              RETURN null;
        ELSE
              RETURN NEW;
        END IF;
    RETURN NEW;
    END;
    $$;


CREATE OR REPLACE TRIGGER tr_iine_tablename BEFORE INSERT ON tablename FOR EACH ROW EXECUTE PROCEDURE pr_tablename_insertuniqueonly();

stack=# insert into tablename values (1,1);
INSERT 0 0
stack=# insert into tablename values (1,2);
INSERT 0 1
stack=# insert into tablename values (3,2);
INSERT 0 0
stack=# insert into tablename values (3,1);
INSERT 0 0
stack=# insert into tablename values (3,4);
INSERT 0 1
stack=# select * from tablename;
col1 | col2
1 | 2
3 | 4
(2 rows)