Re: How to enforce uniqueness when NULL values are present?

Christian Schröder wrote:
> Peter Eisentraut wrote:
>
>> A first step in that direction would be to rethink the apparently
>> troublesome use of null values.
>>
> ....Some of the limits are
> only valid after a given date, whereas other limits are valid all the
> time. How would you put this information into one or more tables? Of
> course, I could use a special date to indicate that a limit is valid all
> the time (e.g. 1970-01-01), but I don't think that this is better design
> than representing this with a NULL value.

I disagree. Using "-infinity" fits your defined needs unambiguously,
except that you have to use "timestamp" data type rather than just "date"

See "Table 8.13. Special Date/Time Inputs": ..."-infinity ... earlier
than all other time stamps"

Example:

CREATE TABLE my_table
(
  id int4 not null,
  the_date timestamp,
PRIMARY KEY (id, the_date)
) WITHOUT OIDS;

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"my_table_pkey" for table "my_table"

INSERT INTO my_table VALUES (1, '-infinity');

Query returned successfully: 1 rows affected, 47 ms execution time.

INSERT INTO my_table VALUES (1, '-infinity');

ERROR:  duplicate key violates unique constraint "my_table_pkey"