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"
