42601 ERROR: syntax error at or near "NULLS" using unique NULLS NOT DISTINCT on PostgreSQL 14.4 Ubuntu

user_3577143 0
Viewed 81

I have a situation where I want my composite field null check to be UNIQUE. So I'm using the UNIQUE NULLS NOT DISTINCT syntax as follows:

create table codes
(
    id       serial primary key,
    code     text not null,
    sub_code text null,
    unique nulls not distinct (code,sub_code)
);

In this example the code can be entered again if it has a sub_code but only one version of codes can exist without a sub_code

It looks like PostgreSQL is rejecting the word nulls. Any ideas why? Is there a configuration to turn this syntax on?

unique indexing null postgresql
1 Answers

You have to use the CONSTRAINT keyword like this:

create table codes
(
    id       serial primary key,
    code     text not null,
    sub_code text null,
    CONSTRAINT uq_sub_code
        UNIQUE NULLS NOT DISTINCT (sub_code)
);
user_102307410
Related