Postgresql add check for empty array

Question

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?

Oto Shavadze · Accepted Answer · 2020-03-30 13:06:35Z

2

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 <> '{}' )
);

answered Mar 30, 2020 at 13:06

Oto Shavadze

43.3k55 gold badges168 silver badges248 bronze badges

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

2 Comments

chatzich Over a year ago

I am applying this creation and it is not throwing an error!

Oto Shavadze Over a year ago

@chatzich - Show query, from which you expect error throwing

Gordon Linoff · Accepted Answer · 2020-03-30 12:38:39Z

2

You can add a check constraint:

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

answered Mar 30, 2020 at 12:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

chatzich Over a year ago

Must I use alter table or can I add the check to the table creation?

Collectives™ on Stack Overflow

Postgresql add check for empty array

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related