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?

Eric Leschinski · Accepted Answer · 2013-04-30 21:37:32Z

9

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.

edited Apr 30, 2013 at 21:37

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Feb 24, 2009 at 2:06

derobert

51.4k15 gold badges98 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sheldon Ross Over a year ago

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

Dana the Sane · Accepted Answer · 2009-02-23 22:54:58Z

1

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

answered Feb 23, 2009 at 22:54

Dana the Sane

15.2k8 gold badges61 silver badges81 bronze badges

Comments

SingleNegationElimination · Accepted Answer · 2009-02-24 02:41:23Z

1

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.

answered Feb 24, 2009 at 2:41

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Comments

Brian Fisher · Accepted Answer · 2009-02-23 22:44:04Z

0

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

answered Feb 23, 2009 at 22:44

Brian Fisher

24.1k15 gold badges80 silver badges82 bronze badges

Comments

Troels Arvin · Accepted Answer · 2009-02-23 22:48:16Z

0

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

answered Feb 23, 2009 at 22:48

Troels Arvin

6,4312 gold badges28 silver badges31 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related