Home Constraint in array character elements in PostgreSQL
Reply: 1

Constraint in array character elements in PostgreSQL

SONewbiee
1#
SONewbiee Published in 2017-12-07 19:45:32Z

In PostgreSQL 10.1 I create the following table:

CREATE TABLE nationality_cumulative_criteria
(
    id             smallint NOT NULL,
    country_set    character varying(2)[] NOT NULL,
    number         smallint NOT NULL
);

I wish to add a constraint (exclude) which will not allow, for example, "US" to appear in different tuples but only in one.

I have already checked and found only cases with ranges and functions but not with arrays of character elements.

Tia

Joe Love
2#
Joe Love Reply to 2017-12-07 20:10:46Z

To do exactly what you said, do the following:

 create unique index on nationality_cumulative_criteria 
 (('US'=any(country_set)))
 where 'US'=any(country_set);

This will allow 'US' (specifically) to only be used once in the entire table

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305005 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO