unique date field postgresql default value

Question

I have a date column which I want to be unique once populated, but want the date field to be ignored if it is not populated.

In MySQL the way this is accomplished is to set the date column to "not null" and give it a default value of '0000-00-00' - this allows all other fields in the unique index to be "checked" even if the date column is not populated yet.

This does not work in PosgreSQL because '0000-00-00' is not a valid date, so you cannot store it in a date field (this makes sense to me).

At first glance, leaving the field nullable seemed like an option, but this creates a problem:

=> create table uniq_test(NUMBER bigint not null, date DATE, UNIQUE(number, date));
CREATE TABLE
=> insert into uniq_test(number) values(1);
INSERT 0 1
=> insert into uniq_test(number) values(1);
INSERT 0 1
=> insert into uniq_test(number) values(1);
INSERT 0 1
=> insert into uniq_test(number) values(1);
INSERT 0 1
=> select * from uniq_test;
 number | date 
--------+------
      1 | 
      1 | 
      1 | 
      1 | 
(4 rows)

NULL apparently "isn't equal to itself" and so it does not count towards constraints.

If I add an additional unique constraint only on the number field, it checks only number and not date and so I cannot have two numbers with different dates.

I could select a default date that is a 'valid date' (but outside working scope) to get around this, and could (in fact) get away with that for the current project, but there are actually cases I might be encountering in the next few years where it will not in fact be evident that the date is a non-real date just because it is "a long time ago" or "in the future."

The advantage the '0000-00-00' mechanic had for me was precisely that this date isn't real and therefore indicated a non-populated entry (where 'non-populated' was a valid uniqueness attribute). When I look around for solutions to this on the internet, most of what I find is "just use NULL" and "storing zeros is stupid."

TL;DR Is there a PostgreSQL best practice for needing to include "not populated" as a possible value in a unique constraint including a date field?

You could use -infinity as the default value.

user330315
– user330315

2017-03-27 20:38:19 +00:00
Commented Mar 27, 2017 at 20:38 — user330315
– user330315, Commented Mar 27, 2017 at 20:38

Clodoaldo Neto · Accepted Answer · 2017-03-27 20:41:32Z

3

Not clear what you want. This is my guess:

create table uniq_test (number bigint not null, date date);

create unique index i1 on uniq_test (number, date)
where date is not null;

create unique index i2 on uniq_test (number)
where date is null;

There will be an unique constraint for not null dates and another one for null dates effectively turning the (number, date) tuples into distinct values.

Check partial index

edited Mar 27, 2017 at 20:41

answered Mar 27, 2017 at 20:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Maxwell Evans Over a year ago

Thanks, that sorts it - I was pretty much completely unaware of partial indexes. I guess I can chalk that up to one more knowledge gap filled.

Vao Tsun · Accepted Answer · 2017-03-27 20:51:33Z

0

It's not a best practice, but you can do it such way:

t=# create table so35(i int, d date);
CREATE TABLE
t=# create unique index i35 on so35(i, coalesce(d,'-infinity'));
CREATE INDEX
t=# insert into so35 (i) select 1;
INSERT 0 1
t=# insert into so35 (i) select 2;
INSERT 0 1
t=# insert into so35 (i) select 2;
ERROR:  duplicate key value violates unique constraint "i35"
DETAIL:  Key (i, (COALESCE(d, '-infinity'::date)))=(2, -infinity) already exists.
STATEMENT:  insert into so35 (i) select 2;

edited Mar 27, 2017 at 20:51

answered Mar 27, 2017 at 20:23

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

3 Comments

user330315 Over a year ago

I would prefer -infinity over 0001-01-01

user330315 Over a year ago

I find it more reasonable - it documents the intended behaviour better (in my opinion). I don't tink if there are any performance advantages (or disadvantages)

Vao Tsun Over a year ago

I thought to make it look close to OP's mentioned "'0000-00-00' mechanic", but you are right - -infinity makes more sence

Collectives™ on Stack Overflow

unique date field postgresql default value

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related