How to get unique values from 2 columns in PostgreSQL

Question

i have a column "id_a" and a column "id_b".

"id_a" is an integer[] type.

"id_b" is an int type.

I have to find a way to verify that there are no more values of id_a with the same id_b

For Example:
id_a {3,4,5}    id_b 18

Other Examples:

ERROR:
id_a{3,4,5}  id_b 18 --> because i have already the value of b with the same values of a 

NO ERROR:
id_a{3,4,5}  id_b 19

ANOTHER ERROR:
id_a{3}      id_b 19

NO ERROR:
id_a{6}      id_b 18

@a_horse_with_no_name I would like to create a constrains, I had thought about using UNIQUE but i can't figure out how to use it on different types. — , Nov 06 '20 at 09:00
@LaurenzAlbe so i should change the type of my column "Id_a" to from Integer [] to int? and for example have values like these? ROW 1: name: abc id_a 3 id_b 18 ROW 2: name: cba id_a 4 id_b 18 — , Nov 06 '20 at 09:06
@a_horse_with_no_name I am currently using version 12.4, sorry for the wrong tags — , Nov 06 '20 at 09:13

score 2 · Accepted Answer · answered Nov 06 '20 at 09:04

you can create an exclusion constraint if you install the btree_gist extension:

create table data (id_a int[], id_b int);

alter table data
  add constraint check_ids 
  exclude using gist (id_a with &&, id_b with =);

Initial row - no problem

insert into data (id_a, id_b) values ('{3,4,5}', 18);

Running the above again yields an error as expected.

The following works:

insert into data (id_a, id_b) values ('{3,4,5}', 19);

And then:

insert into data (id_a, id_b) values ('{3}', 19);

will result in an error

ERROR: conflicting key value violates exclusion constraint "check_ids"

Online example

Oh, wow. I didn't know you could have a GiST index on arrays. — Laurenz Albe, Nov 06 '20 at 09:42

score 0 · Answer 2 · answered Nov 06 '20 at 09:04

You can use an exclusion constraint, like so:

create table mytable (
    id_a int[],
    id_b int,
    exclude using gist(id_a with &&, id_b with =)
);

This prevents array overlaps on id_a for a given id_b.

Demo on DB Fiddle:

insert into mytable values ('{3,4,5}', 18);
-- 1 rows affected

insert into mytable values ('{3,4,5}', 18);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_id_a_id_b_excl"
-- DETAIL:  Key (id_a, id_b)=({3,4,5}, 18) conflicts with existing key (id_a, id_b)=({3,4,5}, 18).

insert into mytable values ('{3,4,5}', 19);
-- 1 rows affected

insert into mytable values ('{3}', 19);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_id_a_id_b_excl"
-- DETAIL:  Key (id_a, id_b)=({3}, 19) conflicts with existing key (id_a, id_b)=({3,4,5}, 19).

insert into mytable values ('{6}', 18);
-- 1 rows affected

How to get unique values from 2 columns in PostgreSQL

2 Answers2