Is there a way to declare PostgreSQL array columns that prevent duplicates?

Question

In other words, is there a way to make an array column to work like a set, that is, when a duplicate value is added to it, it is simply ignored?

One possibility is to use triggers or to ensure uniqueness of elements when a SELECT is performed, but ideally I want the array to act more like a set.

I don't think that is possible. The usual way in a relational database would be to create proper 1:n relationship where you can control uniqueness. — user330315
– user330315, Commented Feb 15, 2014 at 9:03

Pavel Stehule · Accepted Answer · 2014-02-15 11:28:45Z

4

No it is not possible. But, you can use a own append function, that appends only unique values.

CREATE OR REPLACE FUNCTION append_unique(anyarray, anyelement)
RETURNS anyarray AS $$
  SELECT CASE WHEN $2 = ANY($1) THEN $1 ELSE $1 || $2 END; 
$$ LANGUAGE sql;

postgres=# SELECT append_unique(ARRAY[1,2,3], 4);
 append_unique 
---------------
 {1,2,3,4}
(1 row)

postgres=# SELECT append_unique(ARRAY[1,2,3,4], 4);
 append_unique 
---------------
 {1,2,3,4}
(1 row)

edited Feb 15, 2014 at 11:28

answered Feb 15, 2014 at 9:53

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

Clodoaldo Neto Over a year ago

In the second select I think you mean SELECT append_unique(ARRAY[1,2,3,4], 4)

Collectives™ on Stack Overflow

Is there a way to declare PostgreSQL array columns that prevent duplicates?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related