Postgres having unique Constraint on Array Column And Text Column

Question

create table my_table(
id varchar(255) not null primary key,
text_column varchar(255) not null,
array_column text[] not null
);

My table state is

id|text_column|array_column|
--+-----------+------------+
1 |Abcd       |{a,b}       |
2 |Abcd       |{a}         |
3 |Xyz        |{a,b}       |

I would want this to fail

insert into my_table values ('4', 'Abcd', '{"b", "a"}');
insert into my_table values ('5', 'Abcd', '{"a", "b"}');

I am trying to impose the unique constraint on text_column and array_column.

Array_column is not sorted.

Also is it better way to do?

@LaurenzAlbe, I have updated it. Sorry for incomplete details — Patan
– Patan, Commented Dec 5, 2022 at 9:10

Laurenz Albe · Accepted Answer · 2022-12-05 09:31:34Z

1

You could create an auxiliary function that sorts the elements of an array and use that in a unique index:

CREATE FUNCTION array_sort(anyarray) RETURNS anyarray
   IMMUTABLE LANGUAGE sql AS
'SELECT array_agg(a.e ORDER BY a.e) FROM unnest($1) AS a(e)';

CREATE UNIQUE INDEX ON my_table (text_column, array_sort(array_column));

answered Dec 5, 2022 at 9:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

6 Comments

Patan Over a year ago

Thank you. I am trying to put sort as trigger, by passing column name. Just may be out of this context, can we pass column names dynamically to sort?

Patan Over a year ago

CREATE OR REPLACE FUNCTION my_table_sort_array() RETURNS trigger AS $BODY$ DECLARE column_name text := TG_ARGV[0]; BEGIN NEW.column_name = sort_array(column_name); RETURN NEW; END; $BODY$ LANGUAGE 'plpgsql';

Laurenz Albe Over a year ago

Sure, that should work in a BEFORE trigger.

Patan Over a year ago

I tried, giving this error record "new" has no field "column_name". Its not recognizing the column name

Laurenz Albe Over a year ago

Your trigger is wrong. But it should work in principle. And no, I am not going to fix your trigger in a comment. That is a different question.

|

Collectives™ on Stack Overflow

Postgres having unique Constraint on Array Column And Text Column

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related