CODEWITHSUNDEEP
sqlpostgresql
chatzich
chatzich

Reputation: 1133

Postgresql add check for empty array

Is there any way to add a limitation rule to a table 

CREATE TABLE table1
(
    id                        serial not null primary key,
    values1                   smallint[] not null DEFAULT ARRAY [12, 20],
    values2                   smallint[] not null DEFAULT ARRAY [],

    CONSTRAINT constraint_check_error CHECK (values1 NOT ( IS NULL OR values1 = '{}') AND NOT (values2 IS NULL OR values2 = '{}') )
);

to avoid the the tables values1and values2 being empty?

Upvotes: 1

Views: 786

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42863

Don't need NOT ( IS NULL... in constraint, because you already have not null in your table definiton, you can do like this:

CREATE TABLE table1
(
    id                        serial not null primary key,
    values1                   smallint[] not null DEFAULT ARRAY [12, 20],
    values2                   smallint[] not null DEFAULT '{}',
    CONSTRAINT values1_check CHECK ( values1 <> '{}' ),
    CONSTRAINT values2_check CHECK ( values2 <> '{}' )
);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can add a check constraint:

alter table table1 add constraint check_table1_arrays
    check (cardinality(values1) > 0 and cardinality(values2) > 0);

Upvotes: 2

Related Questions