Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit

SELECT 1

CREATE TABLE test ( arr int ARRAY, unique (arr) );

CREATE TABLE

CREATE FUNCTION test_insert_trig_func()
RETURNS trigger AS $$
BEGIN
    NEW.arr := ARRAY(SELECT unnest(NEW.arr) ORDER BY 1);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert_trig
BEFORE INSERT ON test
FOR EACH ROW
EXECUTE PROCEDURE test_insert_trig_func()
;

CREATE FUNCTION

CREATE TRIGGER

INSERT INTO test VALUES ('{1, 2}');

INSERT 0 1

INSERT INTO test VALUES ('{2, 1}');

ERROR:  duplicate key value violates unique constraint "test_arr_key"
DETAIL:  Key (arr)=({1,2}) already exists.

SELECT * FROM test;

arr

{1,2}

SELECT 1