1

In my show_instance table, I have a column called dates with type daterange.

CREATE TABLE public.show_instance (
    id integer NOT NULL,
    show_id integer NOT NULL,
    dates daterange NOT NULL
);

One of the values for dates is both inclusive and exclusive of day 2010-01-01.

INSERT INTO show_instance(id, show_id, dates)
VALUES
(1, 3, '[2010-01-01, 2010-01-01)'), 
(2, 4, '[2010-04-03, 2010-04-04)'),
(3, 5, '[2010-05-29, 2010-05-30)');

This insert statement throws an error:
ERROR: null value in column "id" of relation "show_instance" violates not-null constraint
DETAIL: Failing row contains (null, 3, empty)

When all rows are selected, the daterange for the row is 'empty.'

SELECT show_id FROM show_instance WHERE daterange('2010-01-01', '2010-05-30') <@dates;
 show_id 
---------
(0 rows)

horse_show_db=# SELECT * FROM show_instance;
 id | show_id |          dates          
----+---------+-------------------------
  1 |       3 | empty
  2 |       4 | [2010-04-03,2010-04-04)
  3 |       5 | [2010-05-29,2010-05-30)
(3 rows)

For some reason, the 'empty' row is selected.

SELECT show_id FROM show_instance WHERE daterange('2010-01-01', '2010-05-30') @>dates;
 show_id 
---------
       3
       4
       5
(3 rows)

1. Is there a constraint that prevents 'empty' from being inserted in the row?
2. If 'empty' is not equivalent to 'null', what is it?

trouble_bucket
  • 184
  • 3
  • 12

2 Answers2

0

"empty" is a special range value, representing a range that has no points. It's different from null because you can do operations with it, like a union with another range, which returns a non-null value.

You can restrict empty values with a constraint like this:

ALTER TABLE show_instance 
ADD CONSTRAINT check_show_instance_dates_not_empty
CHECK (NOT isempty(dates));
Blue Star
  • 1,932
  • 1
  • 10
  • 11
  • The upper/lower functions still give you access to the underlying range elements - even in case of an empty sequence. – Georg Heiler Feb 24 '23 at 11:47
0

Unfortunately, empty loses the range boundary information. I.e, [2010-01-01, 2010-01-01) is empty [2010-02-01, 2010-02-01) is also empty Can we differentiate between these two empties? (for audit)

Note: [x,y) means includes x, excludes y (x<=y)

Adeeb
  • 41
  • 5