Re: How to create unique constraint on NULL columns

>> How to create constraint so that NULL values are treated equal and second
>> insert is rejected ?
>
> Rethink your data design --- this behavior is required by the SQL
> standard.

I have a table of users permissions by departments

CREATE TABLE permission (
  id serial,
  user_id CHAR(10) NOT NULL REFERENCES user,
  permission_id CHAR(10) NOT NULL  REFERENCES privilege,
  department_id CHAR(10)  REFERENCES department ,
  UNIQUE ( user_id, permission_id, department_id ) )

permission_id is a permission name:  Invoice, Waybill etc.

department _id is a code of department whose documents user is authorized to
access.

if department _id  is NULL, user has access to all departments data.

By this design it is meaningless to have two records with same user_id and
permission_id both having department_id NULL

So I want that Postgres does not allow to insert them.

How I should rethink this data design to be implemented in CREATE TABLE
statement ?

Andrus.