How to add a composite constraint when a column is false

Question

I tried both

1)

ALTER TABLE artikal_normativi
  ADD CONSTRAINT "artikal_normativi_UIX" 
  UNIQUE(artikal_proizvod_id, artikal_materijal_id) 
WHERE deleted = false;

2)

CREATE UNIQUE INDEX artikal_normativi_UIX
  ON artikal_normativi
  USING btree(artikal_proizvod_id, artikal_materijal_id) 
  WHERE deleted = false;

ADD CONSTRAINT artikal_normativi_uix_constraint UNIQUE USING INDEX artikal_normativi_uix;

Both give me errors.

Error for the first statement:

ERROR:  syntax error at or near "WHERE"
LINE 2: ...UNIQUE(artikal_proizvod_id, artikal_materijal_id) WHERE dele...
                                                             ^
********** Error **********

ERROR: syntax error at or near "WHERE"

And the second statement:

ERROR:  syntax error at or near "USING"
LINE 1: ...i ADD CONSTRAINT artikal_normativi_uix_constraint USING INDE...
                                                             ^

********** Error **********

ERROR: syntax error at or near "USING"
SQL state: 42601
Character: 79

Answer 1

You cannot have UNIQUE constraint with partial index.

From create table we can see in UNIQUE constraint definition that inline index syntax is:

UNIQUE ( column_name [, ... ] ) index_parameters |

And then index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

So no place for partial index (WHERE condition).

In your second attempt you forgot about ALTER TABLE artikal_normativi, so:

ALTER TABLE artikal_normativi
ADD CONSTRAINT artikal_normativi_uix_constraint UNIQUE USING INDEX artikal_normativi_uix;

But then you will have different problem, and that is limitation of constraints, including UNIQUE:

From description of ADD table_constraint_using_index

The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering. These restrictions ensure that the index is equivalent to one that would be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.