Postgres unique index on text array

Question

How to add a unique index on text array column.

I have a column in my Postgres table which contains sections.

 +----+-----------+
 | id |  sections |
 |----|-----------|
 |  1 |['A', 'B'] |
 +----+-----------+
 |  2 |['A', 'A'] |
 +----+-----------+

As you can see for id 2 I can insert two sections with the same text. I do not want to add duplicate text. I do not want duplicate sections in my column. Is there a way I can add an index on text array.

I saw the examples for int array but can't find anything for text array

I do not want to create the new function. I want to use the existing function in Postgres.

You can't create a unique index over an array to prevent overlapping array elements. Normalize your model, then it is really eas — user330315
– user330315, Commented Oct 3, 2018 at 11:03
You would need a trigger for that, but it would be costly. Normalize your model as @a_horse_with_no_name mentioned. — Kamil Gosciminski
– Kamil Gosciminski, Commented Oct 3, 2018 at 11:07
Do you want to disallow duplicate values in a single row? As in the example above you don't allow to insert ['A', 'A'] into row 2 but ['A', 'B'] would be allowed. Or do you want to disallow adding a row with array containing A altogether given that there is a row with array having A? — Roman-Stop RU aggression in UA
– Roman-Stop RU aggression in UA, Commented Oct 3, 2018 at 11:12
I want to disallow duplicate values in the single row. EDIT: Actually both will work. — nicolom
– nicolom, Commented Oct 3, 2018 at 11:15

Ravindra Singh Ratnawat · Accepted Answer · 2018-10-11 17:25:26Z

2

You can append into the sections column and unnest with distinct element like this:

update class set sections = array(
    select distinct unnest(
        array_append(
            (select section from class where id = 2), 'A'))
    where id = 2)

answered Oct 11, 2018 at 17:25

Ravindra Singh Ratnawat

4064 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roman Tkachuk · Accepted Answer · 2018-10-09 20:44:21Z

0

I like arays and do not always it good to normalize tables :-)

CREATE OR REPLACE FUNCTION is_not_unique(a int[]) RETURNS bool AS $f$
   SELECT array_upper(a, 1) = array_upper(
               (
                SELECT array_agg(DISTINCT u)
                  FROM unnest(a) AS u
          ), 1);
$f$ LANGUAGE sql;

CREATE TEMP TABLE a (a int[], CHECK (is_not_unique(a)));

Test it:

# INSERT INTO a VALUES (ARRAY[1]);
INSERT 0 1
# INSERT INTO a VALUES (ARRAY[1, 2]);
INSERT 0 1
# INSERT INTO a VALUES (ARRAY[1, 1]);
ERROR:  new row for relation "a" violates check constraint "a_a_check"
DETAIL:  Failing row contains ({1,1}).

answered Oct 9, 2018 at 20:44

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

2 Comments

nicolom Over a year ago

For integer, I achieved the solution before this question. I am looking for a text array solution. Thanks for the help :)

Roman Tkachuk Over a year ago

The same for text

Collectives™ on Stack Overflow

Postgres unique index on text array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related