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?

Loop

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.

Dubose

The Data model problem is not mine, its the customers :)! Thanks. – Loop

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

Aletaaletha

Answer 3

Actually, I would probably break this out into Two tables. You're modeling two different kinds of things. The first one is the initial version, which is only partial, and the second is the whole thing. Once the information needed to bring the first kind of thing to the second, move the row from one table to the other.

Erida

Answer 4

You could handle this using a trigger instead of a constraint.

Apteral

Answer 5

If I were you, I'd split the table into two tables, and possibly create view which combines them as needed.

Teniacide