How can I enforce a constraint only if a column is not null in Postgresql?

Question

I would like a solution to enforce a constraint only if a column is not null. I can't seem to find a way to do this in the documentation.

create table mytable(
  table_identifier_a INTEGER,
  table_identifier_b INTEGER,
  table_value1,...)

Do to the nature of the data, I will have identifier b and a value when the table is created. After we receive additional data, I will be able to populate identifier a. At this point I would like to ensure a unique key of (identifier_a, value1) but only if identifier_a exists.

Hopefully that makes sense, Any one have any ideas?

Answer 1

Ummm. Unique constraints don't prevent multiple NULL values.

CREATE TABLE mytable (
    table_identifier_a   INTEGER    NULL,
    table_identifier_b   INTEGER    NOT NULL,
    table_value1         INTEGER    NOT NULL,

    UNIQUE(table_identifier_a, table_identifier_b)
);

Note that we can insert muliple NULLs into it, even when identifier_b matches:

test=# INSERT INTO mytable values(NULL, 1, 2);
INSERT 0 1
test=# INSERT INTO mytable values(NULL, 1, 2);
INSERT 0 1
test=# select * from mytable;
 table_identifier_a | table_identifier_b | table_value1 
--------------------+--------------------+--------------
                    |                  1 |            2
                    |                  1 |            2
(2 rows)

But we can't create duplicate (a,b) pairs:

test=# update mytable set table_identifier_a = 3;
ERROR:  duplicate key value violates unique constraint "mytable_table_identifier_a_key"

Of course, you do have an issue: Your table has no primary key. You probably have a data model problem. But you didn't provide enough details to fix that.

Answer 2

If it is feasible to complete the entire operation within one transaction, it is possible to change the time which postgres evaluates the constraint, i.e.:

START;
SET CONSTRAINTS <...> DEFERRED;
<SOME INSERT/UPDATE/DELETE>
COMMIT;

In this case, the constraint is evaluated at commit. See: Postgres 7.4 Doc - Set constraints or Postgres 8.3 Doc