Materialize/test/sqllogictest/range.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

#
# meta

# Ensure range sorts of null correctly
query B
SELECT ARRAY['(,)']::int4range[] < ARRAY[NULL]::int4range[];
----
true

#
# int4range

query T
select '[0,100)'::int4range;
----
[0,100)

query T
select pg_typeof('[0,100)'::int4range);
----
int4range

query error invalid input syntax for type range
select '栬艷^Ằ['::int4range

query T
select 'empty'::int4range;
----
empty

query T
select pg_typeof('empty'::int4range);
----
int4range

query T
select null::int4range
----
NULL

query error integer out of range
SELECT '[2,2147483647]'::int4range;

query error integer out of range
SELECT '(2147483647,2147483647]'::int4range;

# Parameterized input on
#   xjoin(["(","["],
#       xjoin([null,-1,0,1],
#           xjoin(
#               [null,-1,0,1],
#               ["),"]"]
#           )
#       )
#   )

query T
SELECT '(,)'::int4range;
----
(,)

query T
SELECT '(,-1)'::int4range;
----
(,-1)

query T
SELECT '(,0)'::int4range;
----
(,0)

query T
SELECT '(,1)'::int4range;
----
(,1)

query T
SELECT '(,]'::int4range;
----
(,)

query T
SELECT '(,-1]'::int4range;
----
(,0)

query T
SELECT '(,0]'::int4range;
----
(,1)

query T
SELECT '(,1]'::int4range;
----
(,2)

query T
SELECT '(-1,)'::int4range;
----
[0,)

query T
SELECT '(-1,-1)'::int4range;
----
empty

query T
SELECT '(-1,0)'::int4range;
----
empty

query T
SELECT '(-1,1)'::int4range;
----
[0,1)

query T
SELECT '(-1,]'::int4range;
----
[0,)

query T
SELECT '(-1,-1]'::int4range;
----
empty

query T
SELECT '(-1,0]'::int4range;
----
[0,1)

query T
SELECT '(-1,1]'::int4range;
----
[0,2)

query T
SELECT '(0,)'::int4range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1)'::int4range;

query T
SELECT '(0,0)'::int4range;
----
empty

query T
SELECT '(0,1)'::int4range;
----
empty

query T
SELECT '(0,]'::int4range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1]'::int4range;

query T
SELECT '(0,0]'::int4range;
----
empty

query T
SELECT '(0,1]'::int4range;
----
[1,2)

query T
SELECT '(1,)'::int4range;
----
[2,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1)'::int4range;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0)'::int4range;

query T
SELECT '(1,1)'::int4range;
----
empty

query T
SELECT '(1,]'::int4range;
----
[2,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1]'::int4range;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0]'::int4range;

query T
SELECT '(1,1]'::int4range;
----
empty

query T
SELECT '[,)'::int4range;
----
(,)

query T
SELECT '[,-1)'::int4range;
----
(,-1)

query T
SELECT '[,0)'::int4range;
----
(,0)

query T
SELECT '[,1)'::int4range;
----
(,1)

query T
SELECT '[,]'::int4range;
----
(,)

query T
SELECT '[,-1]'::int4range;
----
(,0)

query T
SELECT '[,0]'::int4range;
----
(,1)

query T
SELECT '[,1]'::int4range;
----
(,2)

query T
SELECT '[-1,)'::int4range;
----
[-1,)

query T
SELECT '[-1,-1)'::int4range;
----
empty

query T
SELECT '[-1,0)'::int4range;
----
[-1,0)

query T
SELECT '[-1,1)'::int4range;
----
[-1,1)

query T
SELECT '[-1,]'::int4range;
----
[-1,)

query T
SELECT '[-1,-1]'::int4range;
----
[-1,0)

query T
SELECT '[-1,0]'::int4range;
----
[-1,1)

query T
SELECT '[-1,1]'::int4range;
----
[-1,2)

query T
SELECT '[0,)'::int4range;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1)'::int4range;

query T
SELECT '[0,0)'::int4range;
----
empty

query T
SELECT '[0,1)'::int4range;
----
[0,1)

query T
SELECT '[0,]'::int4range;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1]'::int4range;

query T
SELECT '[0,0]'::int4range;
----
[0,1)

query T
SELECT '[0,1]'::int4range;
----
[0,2)

query T
SELECT '[1,)'::int4range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1)'::int4range;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0)'::int4range;

query T
SELECT '[1,1)'::int4range;
----
empty

query T
SELECT '[1,]'::int4range;
----
[1,)

# Range bound errors
query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1]'::int4range;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0]'::int4range;

# Whitespace handling
query T
SELECT
	DISTINCT column1::int4range
FROM
	(
		VALUES
			('   empty'),
			('empty  '),
			('  [1,)'),
			('[  1,)'),
			('[1  ,)'),
			('[  1  ,)'),
            ('[1,  2)'),
			('[1,2  )'),
			('[1,  2  )'),
			('[1,)  '),
			('  (,1)'),
			('(,  1)'),
			('  (,)  ')
	) t;
----
empty
(,)
(,1)
[1,)
[1,2)

# Input errors
query error invalid input syntax for type range
SELECT '[1,  )'::int4range

query error invalid input syntax for type range
SELECT '(  ,1)'::int4range

query error invalid input syntax for type range
SELECT '(  ,  )'::int4range

query error invalid input syntax for type range
SELECT 'emptyy'::int4range;

query error invalid input syntax for type range
SELECT ''::int4range;

query error invalid input syntax for type range
SELECT '1'::int4range;

query error invalid input syntax for type range
SELECT 'd'::int4range;

query error invalid input syntax for type range
SELECT ','::int4range;

query error invalid input syntax for type range
SELECT ')'::int4range;

query error invalid input syntax for type range
SELECT '{'::int4range;

query error invalid input syntax for type range
SELECT '('::int4range;

query error invalid input syntax for type range
SELECT '['::int4range;

query error invalid input syntax for type range
SELECT '(1)'::int4range;

query error invalid input syntax for type range
SELECT '[1]'::int4range;

query error invalid input syntax for type range
SELECT '(1,'::int4range;

query error invalid input syntax for type range
SELECT '[1,'::int4range;

query error invalid input syntax for type range
SELECT '(1,1'::int4range;

query error invalid input syntax for type range
SELECT '[1,1'::int4range;

query error invalid input syntax for type range
SELECT '(1,1]1'::int4range;

query error invalid input syntax for type range
SELECT '[1,1]a'::int4range;

query error invalid input syntax for type range
SELECT '(1,1]]'::int4range;

query error invalid input syntax for type range
SELECT '[1,1]}'::int4range;

query error invalid input syntax for type range
SELECT '(1,1]  ]'::int4range;

query error invalid input syntax for type range
SELECT '[1,1]  }'::int4range;

statement ok
CREATE TABLE int4range_values (a int4range);

statement ok
INSERT INTO int4range_values VALUES
    (null),
    ('empty'),
    ('[,1)'::int4range),
    ('[,1]'::int4range),
    ('[,)'::int4range),
    ('[,]'::int4range),
    ('(,1)'::int4range),
    ('(,1]'::int4range),
    ('(,)'::int4range),
    ('(,]'::int4range),
    ('[-1,1)'::int4range),
    ('[-1,1]'::int4range),
    ('(-1,1)'::int4range),
    ('(-1,1]'::int4range),
    ('[0,0)'::int4range),
    ('[0,0]'::int4range),
    ('(0,0)'::int4range),
    ('(0,0]'::int4range),
    ('[1,)'::int4range),
    ('[1,]'::int4range),
    ('(1,)'::int4range),
    ('(1,]'::int4range);

query T
SELECT a AS t FROM int4range_values ORDER BY a;
----
empty
empty
empty
empty
(,1)
(,1)
(,2)
(,2)
(,)
(,)
(,)
(,)
[-1,1)
[-1,2)
[0,1)
[0,1)
[0,2)
[1,)
[1,)
[2,)
[2,)
NULL

query T
SELECT a AS t FROM int4range_values EXCEPT SELECT column1 FROM (VALUES
    (int4range(null,1)),
    (int4range(null,1, '[]')),
    (int4range(null,null)),
    (int4range(null,null, '[]')),
    (int4range(null,1, '()')),
    (int4range(null,1, '(]')),
    (int4range(null,null, '()')),
    (int4range(null,null,'(]')),
    (int4range(-1,1)),
    (int4range(-1,1, '[]')),
    (int4range(-1,1, '()')),
    (int4range(-1,1,'(]')),
    (int4range(0,0)),
    (int4range(0,0, '[]')),
    (int4range(0,0,'()')),
    (int4range(0,0,'(]')),
    (int4range(1,null)),
    (int4range(1,null, '[]')),
    (int4range(1,null,'()')),
    (int4range(1,null,'(]'))
) t;
----
NULL

query error operator does not exist: int4range @> numeric
SELECT int4range(-1,1) @> 0.1;

query error operator does not exist: int4range @> double precision
SELECT int4range(-1,1) @> 0.1::float;

query error range constructor flags argument must not be null
SELECT int4range(1,2,null);

query error range constructor flags argument must not be null
SELECT int4range(null,null,null);

#
# int4range upper, lower
query TT
SELECT DISTINCT lower(a), upper(a) FROM int4range_values
ORDER BY 1, 2;
----
-1
1
-1
2
0
1
0
2
1
NULL
2
NULL
NULL
1
NULL
2
NULL
NULL


# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	int4range_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= int4range(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM int4range_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

query error invalid range bound flags
SELECT int4range(null,null,' (]');

query error invalid range bound flags
SELECT int4range(null,null,'(] ');

query error invalid range bound flags
SELECT int4range(null,null,'( ]');

query error invalid range bound flags
SELECT int4range(null,null,'(,]');

query error invalid range bound flags
SELECT int4range(null,null,'a()');

query error invalid range bound flags
SELECT int4range(null,null,'(a)');

query error invalid range bound flags
SELECT int4range(null,null,'()a');

query error invalid range bound flags
SELECT int4range(null,null,'(()');

query error invalid range bound flags
SELECT int4range(null,null,'())');

# Test range in list
query T
SELECT LIST['(,)', 'empty', '[-1,1]']::int4range list::text;
----
{"(,)",empty,"[-1,2)"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::int4range list::text;
----
{"(,)",empty,"[-1,2)"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[-1,1]']::_int4range;
----
{"(,)",empty,"[-1,2)"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::_int4range;
----
{"(,)",empty,"[-1,2)"}

# Test builtin functions

query B
select '(,)'::int4range = '(,)'::int4range;
----
true

query B
select '(,)'::int4range != 'empty'::int4range;
----
true

query B
select '(,)'::int4range > 'empty'::int4range;
----
true

query B
select '(,)'::int4range >= 'empty'::int4range;
----
true

query B
select '(,)'::int4range < 'empty'::int4range;
----
false

query B
select '(,)'::int4range <= 'empty'::int4range;
----
false

statement ok
CREATE TABLE int4range_test_values (v int4range);

statement ok
INSERT INTO int4range_test_values VALUES ('empty'),
('(,)'),
('(,1)'),
('(-1,)'),
('[-1,1)'),
('[-99,-50)'),
('[50,99)');

#
# int4range contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,1)
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,2)
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,)
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
[-1,1)
{empty,"[-1,1)"}
[-1,2)
{empty,"[-1,1)"}
[0,1)
{empty}
[0,2)
{empty}
[1,)
{empty,"[50,99)"}
[2,)
{empty,"[50,99)"}

#
# int4range contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
(,1)
{"(,1)","(,)"}
(,2)
{"(,)"}
(,)
{"(,)"}
[-1,1)
{"(,1)","(,)","[-1,1)"}
[-1,2)
{"(,)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,2)
{"(,)","[0,)"}
[1,)
{"(,)","[0,)"}
[2,)
{"(,)","[0,)"}

#
# int4range contained overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
(,2)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
(,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
[-1,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[-1,2)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,2)
{"(,1)","(,)","[-1,1)","[0,)"}
[1,)
{"(,)","[0,)","[50,99)"}
[2,)
{"(,)","[0,)","[50,99)"}

#
# int4range before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,1)
{"[50,99)"}
(,2)
{"[50,99)"}
[-1,1)
{"[50,99)"}
[-1,2)
{"[50,99)"}
[0,1)
{"[50,99)"}
[0,2)
{"[50,99)"}

#
# int4range after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
[-1,1)
{"[-99,-50)"}
[-1,2)
{"[-99,-50)"}
[0,1)
{"[-99,-50)"}
[0,2)
{"[-99,-50)"}
[1,)
{"(,1)","[-99,-50)","[-1,1)"}
[2,)
{"(,1)","[-99,-50)","[-1,1)"}

#
# int4range overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
(,2)
{"(,)","[0,)","[50,99)"}
(,)
{"(,)","[0,)"}
[-1,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
[-1,2)
{"(,)","[0,)","[50,99)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
[0,2)
{"(,)","[0,)","[50,99)"}
[1,)
{"(,)","[0,)"}
[2,)
{"(,)","[0,)"}

#
# int4range overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)"}
(,2)
{"(,1)","(,)"}
(,)
{"(,1)","(,)"}
[-1,1)
{"(,1)","(,)","[-99,-50)","[-1,1)"}
[-1,2)
{"(,1)","(,)","[-99,-50)","[-1,1)"}
[0,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[0,2)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[1,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[2,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}

#
# int4range adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int4range_values WHERE a IS NOT NULL
) int4range_values(a), int4range_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
[1,)
{"(,1)","[-1,1)"}

#
# int4range union

query T
SELECT null + 'empty'::int4range
----
NULL

query T
SELECT 'empty'::int4range + 'empty'::int4range
----
empty

query T
SELECT 'empty'::int4range + '(,-1)'::int4range
----
(,-1)

query T
SELECT '(,-1)'::int4range + 'empty'::int4range
----
(,-1)

query T
SELECT '(,-1)'::int4range + '(,-1)'::int4range
----
(,-1)

query T
SELECT 'empty'::int4range + '[-2,2)'::int4range
----
[-2,2)

query T
SELECT 'empty'::int4range + '[1,)'::int4range
----
[1,)

query T
SELECT 'empty'::int4range + '(,)'::int4range
----
(,)

query T
SELECT '(,-1)'::int4range + '[-2,2)'::int4range
----
(,2)

query error result of range union would not be contiguous
SELECT '(,-1)'::int4range + '[1,)'::int4range

query T
SELECT '(,-1)'::int4range + '(,)'::int4range
----
(,)

query T
SELECT '[-2,2)'::int4range + 'empty'::int4range
----
[-2,2)

query T
SELECT '[-2,2)'::int4range + '(,-1)'::int4range
----
(,2)

query T
SELECT '[-2,2)'::int4range + '[1,)'::int4range
----
[-2,)

query T
SELECT '[-2,2)'::int4range + '(,)'::int4range
----
(,)

query T
SELECT '[1,)'::int4range + 'empty'::int4range
----
[1,)

query error result of range union would not be contiguous
SELECT '[1,)'::int4range + '(,-1)'::int4range

query T
SELECT '[1,)'::int4range + '[-2,2)'::int4range
----
[-2,)

query T
SELECT '[1,)'::int4range + '(,)'::int4range
----
(,)

query T
SELECT '(,)'::int4range + 'empty'::int4range
----
(,)

query T
SELECT '(,)'::int4range + '(,-1)'::int4range
----
(,)

query T
SELECT '(,)'::int4range + '[-2,2)'::int4range
----
(,)

query T
SELECT '(,)'::int4range + '[1,)'::int4range
----
(,)

#
# int4range intersection

query T
SELECT null * 'empty'::int4range
----
NULL

query T
SELECT 'empty'::int4range * 'empty'::int4range
----
empty

query T
SELECT 'empty'::int4range * '(,-1)'::int4range
----
empty

query T
SELECT '(,-1)'::int4range * 'empty'::int4range
----
empty

query T
SELECT '(,-1)'::int4range * '(,-1)'::int4range
----
(,-1)

query T
SELECT 'empty'::int4range * '[-2,2)'::int4range
----
empty

query T
SELECT 'empty'::int4range * '[1,)'::int4range
----
empty

query T
SELECT 'empty'::int4range * '(,)'::int4range
----
empty

query T
SELECT '(,-1)'::int4range * '[-2,2)'::int4range
----
[-2,-1)

query T
SELECT '(,-1)'::int4range * '[1,)'::int4range
----
empty

query T
SELECT '(,-1)'::int4range * '(,)'::int4range
----
(,-1)

query T
SELECT '[-2,2)'::int4range * 'empty'::int4range
----
empty

query T
SELECT '[-2,2)'::int4range * '(,-1)'::int4range
----
[-2,-1)

query T
SELECT '[-2,2)'::int4range * '[1,)'::int4range
----
[1,2)

query T
SELECT '[-2,2)'::int4range * '(,)'::int4range
----
[-2,2)

query T
SELECT '[1,)'::int4range * 'empty'::int4range
----
empty

query T
SELECT '[1,)'::int4range * '(,-1)'::int4range
----
empty

query T
SELECT '[1,)'::int4range * '[-2,2)'::int4range
----
[1,2)

query T
SELECT '[1,)'::int4range * '(,)'::int4range
----
[1,)

query T
SELECT '(,)'::int4range * 'empty'::int4range
----
empty

query T
SELECT '(,)'::int4range * '(,-1)'::int4range
----
(,-1)

query T
SELECT '(,)'::int4range * '[-2,2)'::int4range
----
[-2,2)

query T
SELECT '(,)'::int4range * '[1,)'::int4range
----
[1,)

#
# int4range difference

query T
SELECT null - 'empty'::int4range
----
NULL

query T
SELECT 'empty'::int4range - 'empty'::int4range
----
empty

query T
SELECT 'empty'::int4range - '(,-1)'::int4range
----
empty

query T
SELECT '(,-1)'::int4range - 'empty'::int4range
----
(,-1)

query T
SELECT '(,-1)'::int4range - '(,-1)'::int4range
----
empty

query T
SELECT 'empty'::int4range - '[-2,2)'::int4range
----
empty

query T
SELECT 'empty'::int4range - '[1,)'::int4range
----
empty

query T
SELECT 'empty'::int4range - '(,)'::int4range
----
empty

query T
SELECT '(,-1)'::int4range - '[-2,2)'::int4range
----
(,-2)

query T
SELECT '(,-1)'::int4range - '[1,)'::int4range
----
(,-1)

query T
SELECT '(,-1)'::int4range - '(,)'::int4range
----
empty

query T
SELECT '[-2,2)'::int4range - 'empty'::int4range
----
[-2,2)

query T
SELECT '[-2,2)'::int4range - '(,-1)'::int4range
----
[-1,2)

query T
SELECT '[-2,2)'::int4range - '[1,)'::int4range
----
[-2,1)

query T
SELECT '[-2,2)'::int4range - '(,)'::int4range
----
empty

query T
SELECT '[1,)'::int4range - 'empty'::int4range
----
[1,)

query T
SELECT '[1,)'::int4range - '(,-1)'::int4range
----
[1,)

query T
SELECT '[1,)'::int4range - '[-2,2)'::int4range
----
[2,)

query T
SELECT '[1,)'::int4range - '(,)'::int4range
----
empty

query T
SELECT '(,)'::int4range - 'empty'::int4range
----
(,)

query T
SELECT '(,)'::int4range - '(,-1)'::int4range
----
[-1,)

query error result of range difference would not be contiguous
SELECT '(,)'::int4range - '[-2,2)'::int4range

query T
SELECT '(,)'::int4range - '[1,)'::int4range
----
(,1)

#
# int8range

query T
select '[0,100)'::int8range;
----
[0,100)

query T
select pg_typeof('[0,100)'::int8range);
----
int8range

query T
select 'empty'::int8range;
----
empty

query T
select pg_typeof('empty'::int8range);
----
int8range

query T
select null::int8range
----
NULL

query error bigint out of range
SELECT '[2,9223372036854775807]'::int8range;

query error bigint out of range
SELECT '(9223372036854775807,9223372036854775807]'::int8range;

# Parameterized input on
#   xjoin(["(","["],
#       xjoin([null,-1,0,1],
#           xjoin(
#               [null,-1,0,1],
#               ["),"]"]
#           )
#       )
#   )

query T
SELECT '(,)'::int8range;
----
(,)

query T
SELECT '(,-1)'::int8range;
----
(,-1)

query T
SELECT '(,0)'::int8range;
----
(,0)

query T
SELECT '(,1)'::int8range;
----
(,1)

query T
SELECT '(,]'::int8range;
----
(,)

query T
SELECT '(,-1]'::int8range;
----
(,0)

query T
SELECT '(,0]'::int8range;
----
(,1)

query T
SELECT '(,1]'::int8range;
----
(,2)

query T
SELECT '(-1,)'::int8range;
----
[0,)

query T
SELECT '(-1,-1)'::int8range;
----
empty

query T
SELECT '(-1,0)'::int8range;
----
empty

query T
SELECT '(-1,1)'::int8range;
----
[0,1)

query T
SELECT '(-1,]'::int8range;
----
[0,)

query T
SELECT '(-1,-1]'::int8range;
----
empty

query T
SELECT '(-1,0]'::int8range;
----
[0,1)

query T
SELECT '(-1,1]'::int8range;
----
[0,2)

query T
SELECT '(0,)'::int8range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1)'::int8range;

query T
SELECT '(0,0)'::int8range;
----
empty

query T
SELECT '(0,1)'::int8range;
----
empty

query T
SELECT '(0,]'::int8range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1]'::int8range;

query T
SELECT '(0,0]'::int8range;
----
empty

query T
SELECT '(0,1]'::int8range;
----
[1,2)

query T
SELECT '(1,)'::int8range;
----
[2,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1)'::int8range;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0)'::int8range;

query T
SELECT '(1,1)'::int8range;
----
empty

query T
SELECT '(1,]'::int8range;
----
[2,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1]'::int8range;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0]'::int8range;

query T
SELECT '(1,1]'::int8range;
----
empty

query T
SELECT '[,)'::int8range;
----
(,)

query T
SELECT '[,-1)'::int8range;
----
(,-1)

query T
SELECT '[,0)'::int8range;
----
(,0)

query T
SELECT '[,1)'::int8range;
----
(,1)

query T
SELECT '[,]'::int8range;
----
(,)

query T
SELECT '[,-1]'::int8range;
----
(,0)

query T
SELECT '[,0]'::int8range;
----
(,1)

query T
SELECT '[,1]'::int8range;
----
(,2)

query T
SELECT '[-1,)'::int8range;
----
[-1,)

query T
SELECT '[-1,-1)'::int8range;
----
empty

query T
SELECT '[-1,0)'::int8range;
----
[-1,0)

query T
SELECT '[-1,1)'::int8range;
----
[-1,1)

query T
SELECT '[-1,]'::int8range;
----
[-1,)

query T
SELECT '[-1,-1]'::int8range;
----
[-1,0)

query T
SELECT '[-1,0]'::int8range;
----
[-1,1)

query T
SELECT '[-1,1]'::int8range;
----
[-1,2)

query T
SELECT '[0,)'::int8range;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1)'::int8range;

query T
SELECT '[0,0)'::int8range;
----
empty

query T
SELECT '[0,1)'::int8range;
----
[0,1)

query T
SELECT '[0,]'::int8range;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1]'::int8range;

query T
SELECT '[0,0]'::int8range;
----
[0,1)

query T
SELECT '[0,1]'::int8range;
----
[0,2)

query T
SELECT '[1,)'::int8range;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1)'::int8range;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0)'::int8range;

query T
SELECT '[1,1)'::int8range;
----
empty

query T
SELECT '[1,]'::int8range;
----
[1,)

# Range bound errors
query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1]'::int8range;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0]'::int8range;

# Input errors
query error invalid input syntax for type range
SELECT '[1,  )'::int8range

query error invalid input syntax for type range
SELECT '(  ,1)'::int8range

query error invalid input syntax for type range
SELECT '(  ,  )'::int8range

query error invalid input syntax for type range
SELECT 'emptyy'::int8range;

query error invalid input syntax for type range
SELECT ''::int8range;

query error invalid input syntax for type range
SELECT '1'::int8range;

query error invalid input syntax for type range
SELECT 'd'::int8range;

query error invalid input syntax for type range
SELECT ','::int8range;

query error invalid input syntax for type range
SELECT ')'::int8range;

query error invalid input syntax for type range
SELECT '{'::int8range;

query error invalid input syntax for type range
SELECT '('::int8range;

query error invalid input syntax for type range
SELECT '['::int8range;

query error invalid input syntax for type range
SELECT '(1)'::int8range;

query error invalid input syntax for type range
SELECT '[1]'::int8range;

query error invalid input syntax for type range
SELECT '(1,'::int8range;

query error invalid input syntax for type range
SELECT '[1,'::int8range;

query error invalid input syntax for type range
SELECT '(1,1'::int8range;

query error invalid input syntax for type range
SELECT '[1,1'::int8range;

query error invalid input syntax for type range
SELECT '(1,1]1'::int8range;

query error invalid input syntax for type range
SELECT '[1,1]a'::int8range;

query error invalid input syntax for type range
SELECT '(1,1]]'::int8range;

query error invalid input syntax for type range
SELECT '[1,1]}'::int8range;

query error invalid input syntax for type range
SELECT '(1,1]  ]'::int8range;

query error invalid input syntax for type range
SELECT '[1,1]  }'::int8range;

statement ok
CREATE TABLE int8range_values (a int8range);

statement ok
INSERT INTO int8range_values VALUES
    (null),
    ('empty'),
    ('[,1)'::int8range),
    ('[,1]'::int8range),
    ('[,)'::int8range),
    ('[,]'::int8range),
    ('(,1)'::int8range),
    ('(,1]'::int8range),
    ('(,)'::int8range),
    ('(,]'::int8range),
    ('[-1,1)'::int8range),
    ('[-1,1]'::int8range),
    ('(-1,1)'::int8range),
    ('(-1,1]'::int8range),
    ('[0,0)'::int8range),
    ('[0,0]'::int8range),
    ('(0,0)'::int8range),
    ('(0,0]'::int8range),
    ('[1,)'::int8range),
    ('[1,]'::int8range),
    ('(1,)'::int8range),
    ('(1,]'::int8range);

query T
SELECT a AS t FROM int8range_values ORDER BY a;
----
empty
empty
empty
empty
(,1)
(,1)
(,2)
(,2)
(,)
(,)
(,)
(,)
[-1,1)
[-1,2)
[0,1)
[0,1)
[0,2)
[1,)
[1,)
[2,)
[2,)
NULL

query T
SELECT a AS t FROM int8range_values EXCEPT SELECT column1 FROM (VALUES
    (int8range(null,1)),
    (int8range(null,1, '[]')),
    (int8range(null,null)),
    (int8range(null,null, '[]')),
    (int8range(null,1, '()')),
    (int8range(null,1, '(]')),
    (int8range(null,null, '()')),
    (int8range(null,null,'(]')),
    (int8range(-1,1)),
    (int8range(-1,1, '[]')),
    (int8range(-1,1, '()')),
    (int8range(-1,1,'(]')),
    (int8range(0,0)),
    (int8range(0,0, '[]')),
    (int8range(0,0,'()')),
    (int8range(0,0,'(]')),
    (int8range(1,null)),
    (int8range(1,null, '[]')),
    (int8range(1,null,'()')),
    (int8range(1,null,'(]'))
) t;
----
NULL

query error operator does not exist: int8range @> numeric
SELECT int8range(-1,1) @> 0.1;

query error operator does not exist: int8range @> double precision
SELECT int8range(-1,1) @> 0.1::float;

query error range constructor flags argument must not be null
SELECT int8range(1,2,null);

query error range constructor flags argument must not be null
SELECT int8range(null,null,null);

#
# int8range upper, lower
query TT
SELECT DISTINCT lower(a), upper(a) FROM int8range_values
ORDER BY 1, 2;
----
-1
1
-1
2
0
1
0
2
1
NULL
2
NULL
NULL
1
NULL
2
NULL
NULL


# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	int8range_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= int8range(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM int8range_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

# Test range in list
query T
SELECT LIST['(,)', 'empty', '[-1,1]']::int8range list::text;
----
{"(,)",empty,"[-1,2)"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::int8range list::text;
----
{"(,)",empty,"[-1,2)"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[-1,1]']::_int8range;
----
{"(,)",empty,"[-1,2)"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::_int8range;
----
{"(,)",empty,"[-1,2)"}

# Test builtin functions

query B
select '(,)'::int8range = '(,)'::int8range;
----
true

query B
select '(,)'::int8range != 'empty'::int8range;
----
true

query B
select '(,)'::int8range > 'empty'::int8range;
----
true

query B
select '(,)'::int8range >= 'empty'::int8range;
----
true

query B
select '(,)'::int8range < 'empty'::int8range;
----
false

query B
select '(,)'::int8range <= 'empty'::int8range;
----
false

statement ok
CREATE TABLE int8range_test_values (v int8range);

statement ok
INSERT INTO int8range_test_values VALUES ('empty'),
('(,)'),
('(,1)'),
('(-1,)'),
('[-1,1)'),
('[-99,-50)'),
('[50,99)');

#
# int8range contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,1)
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,2)
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,)
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
[-1,1)
{empty,"[-1,1)"}
[-1,2)
{empty,"[-1,1)"}
[0,1)
{empty}
[0,2)
{empty}
[1,)
{empty,"[50,99)"}
[2,)
{empty,"[50,99)"}

#
# int8range contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
(,1)
{"(,1)","(,)"}
(,2)
{"(,)"}
(,)
{"(,)"}
[-1,1)
{"(,1)","(,)","[-1,1)"}
[-1,2)
{"(,)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,2)
{"(,)","[0,)"}
[1,)
{"(,)","[0,)"}
[2,)
{"(,)","[0,)"}

#
# int8range overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
(,2)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
(,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)","[50,99)"}
[-1,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[-1,2)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)"}
[0,2)
{"(,1)","(,)","[-1,1)","[0,)"}
[1,)
{"(,)","[0,)","[50,99)"}
[2,)
{"(,)","[0,)","[50,99)"}

#
# int8range before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,1)
{"[50,99)"}
(,2)
{"[50,99)"}
[-1,1)
{"[50,99)"}
[-1,2)
{"[50,99)"}
[0,1)
{"[50,99)"}
[0,2)
{"[50,99)"}

#
# int8range after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
[-1,1)
{"[-99,-50)"}
[-1,2)
{"[-99,-50)"}
[0,1)
{"[-99,-50)"}
[0,2)
{"[-99,-50)"}
[1,)
{"(,1)","[-99,-50)","[-1,1)"}
[2,)
{"(,1)","[-99,-50)","[-1,1)"}

#
# int8range overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
(,2)
{"(,)","[0,)","[50,99)"}
(,)
{"(,)","[0,)"}
[-1,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
[-1,2)
{"(,)","[0,)","[50,99)"}
[0,1)
{"(,1)","(,)","[-1,1)","[0,)","[50,99)"}
[0,2)
{"(,)","[0,)","[50,99)"}
[1,)
{"(,)","[0,)"}
[2,)
{"(,)","[0,)"}

#
# int8range overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)"}
(,2)
{"(,1)","(,)"}
(,)
{"(,1)","(,)"}
[-1,1)
{"(,1)","(,)","[-99,-50)","[-1,1)"}
[-1,2)
{"(,1)","(,)","[-99,-50)","[-1,1)"}
[0,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[0,2)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[1,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}
[2,)
{"(,1)","(,)","[-99,-50)","[-1,1)","[0,)"}

#
# int8range adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM int8range_values WHERE a IS NOT NULL
) int8range_values(a), int8range_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
[1,)
{"(,1)","[-1,1)"}

#
# int8range union

query T
SELECT null + 'empty'::int8range
----
NULL

query T
SELECT 'empty'::int8range + 'empty'::int8range
----
empty

query T
SELECT 'empty'::int8range + '(,-1)'::int8range
----
(,-1)

query T
SELECT '(,-1)'::int8range + 'empty'::int8range
----
(,-1)

query T
SELECT '(,-1)'::int8range + '(,-1)'::int8range
----
(,-1)

query T
SELECT 'empty'::int8range + '[-2,2)'::int8range
----
[-2,2)

query T
SELECT 'empty'::int8range + '[1,)'::int8range
----
[1,)

query T
SELECT 'empty'::int8range + '(,)'::int8range
----
(,)

query T
SELECT '(,-1)'::int8range + '[-2,2)'::int8range
----
(,2)

query error result of range union would not be contiguous
SELECT '(,-1)'::int8range + '[1,)'::int8range

query T
SELECT '(,-1)'::int8range + '(,)'::int8range
----
(,)

query T
SELECT '[-2,2)'::int8range + 'empty'::int8range
----
[-2,2)

query T
SELECT '[-2,2)'::int8range + '(,-1)'::int8range
----
(,2)

query T
SELECT '[-2,2)'::int8range + '[1,)'::int8range
----
[-2,)

query T
SELECT '[-2,2)'::int8range + '(,)'::int8range
----
(,)

query T
SELECT '[1,)'::int8range + 'empty'::int8range
----
[1,)

query error result of range union would not be contiguous
SELECT '[1,)'::int8range + '(,-1)'::int8range

query T
SELECT '[1,)'::int8range + '[-2,2)'::int8range
----
[-2,)

query T
SELECT '[1,)'::int8range + '(,)'::int8range
----
(,)

query T
SELECT '(,)'::int8range + 'empty'::int8range
----
(,)

query T
SELECT '(,)'::int8range + '(,-1)'::int8range
----
(,)

query T
SELECT '(,)'::int8range + '[-2,2)'::int8range
----
(,)

query T
SELECT '(,)'::int8range + '[1,)'::int8range
----
(,)

#
# int8range intersection

query T
SELECT null * 'empty'::int8range
----
NULL

query T
SELECT 'empty'::int8range * 'empty'::int8range
----
empty

query T
SELECT 'empty'::int8range * '(,-1)'::int8range
----
empty

query T
SELECT '(,-1)'::int8range * 'empty'::int8range
----
empty

query T
SELECT '(,-1)'::int8range * '(,-1)'::int8range
----
(,-1)

query T
SELECT 'empty'::int8range * '[-2,2)'::int8range
----
empty

query T
SELECT 'empty'::int8range * '[1,)'::int8range
----
empty

query T
SELECT 'empty'::int8range * '(,)'::int8range
----
empty

query T
SELECT '(,-1)'::int8range * '[-2,2)'::int8range
----
[-2,-1)

query T
SELECT '(,-1)'::int8range * '[1,)'::int8range
----
empty

query T
SELECT '(,-1)'::int8range * '(,)'::int8range
----
(,-1)

query T
SELECT '[-2,2)'::int8range * 'empty'::int8range
----
empty

query T
SELECT '[-2,2)'::int8range * '(,-1)'::int8range
----
[-2,-1)

query T
SELECT '[-2,2)'::int8range * '[1,)'::int8range
----
[1,2)

query T
SELECT '[-2,2)'::int8range * '(,)'::int8range
----
[-2,2)

query T
SELECT '[1,)'::int8range * 'empty'::int8range
----
empty

query T
SELECT '[1,)'::int8range * '(,-1)'::int8range
----
empty

query T
SELECT '[1,)'::int8range * '[-2,2)'::int8range
----
[1,2)

query T
SELECT '[1,)'::int8range * '(,)'::int8range
----
[1,)

query T
SELECT '(,)'::int8range * 'empty'::int8range
----
empty

query T
SELECT '(,)'::int8range * '(,-1)'::int8range
----
(,-1)

query T
SELECT '(,)'::int8range * '[-2,2)'::int8range
----
[-2,2)

query T
SELECT '(,)'::int8range * '[1,)'::int8range
----
[1,)

#
# int8range difference

query T
SELECT null - 'empty'::int8range
----
NULL

query T
SELECT 'empty'::int8range - 'empty'::int8range
----
empty

query T
SELECT 'empty'::int8range - '(,-1)'::int8range
----
empty

query T
SELECT '(,-1)'::int8range - 'empty'::int8range
----
(,-1)

query T
SELECT '(,-1)'::int8range - '(,-1)'::int8range
----
empty

query T
SELECT 'empty'::int8range - '[-2,2)'::int8range
----
empty

query T
SELECT 'empty'::int8range - '[1,)'::int8range
----
empty

query T
SELECT 'empty'::int8range - '(,)'::int8range
----
empty

query T
SELECT '(,-1)'::int8range - '[-2,2)'::int8range
----
(,-2)

query T
SELECT '(,-1)'::int8range - '[1,)'::int8range
----
(,-1)

query T
SELECT '(,-1)'::int8range - '(,)'::int8range
----
empty

query T
SELECT '[-2,2)'::int8range - 'empty'::int8range
----
[-2,2)

query T
SELECT '[-2,2)'::int8range - '(,-1)'::int8range
----
[-1,2)

query T
SELECT '[-2,2)'::int8range - '[1,)'::int8range
----
[-2,1)

query T
SELECT '[-2,2)'::int8range - '(,)'::int8range
----
empty

query T
SELECT '[1,)'::int8range - 'empty'::int8range
----
[1,)

query T
SELECT '[1,)'::int8range - '(,-1)'::int8range
----
[1,)

query T
SELECT '[1,)'::int8range - '[-2,2)'::int8range
----
[2,)

query T
SELECT '[1,)'::int8range - '(,)'::int8range
----
empty

query T
SELECT '(,)'::int8range - 'empty'::int8range
----
(,)

query T
SELECT '(,)'::int8range - '(,-1)'::int8range
----
[-1,)

query error result of range difference would not be contiguous
SELECT '(,)'::int8range - '[-2,2)'::int8range

query T
SELECT '(,)'::int8range - '[1,)'::int8range
----
(,1)

#
# daterange

query T
select '[1970-01-01,62143-12-30)'::daterange;
----
[1970-01-01,62143-12-30)

query T
select pg_typeof('[1970-01-01,62143-12-30)'::daterange);
----
daterange

query T
select 'empty'::daterange;
----
empty

query T
select pg_typeof('empty'::daterange);
----
daterange

query T
select null::daterange
----
NULL

# Test incrementing max date
query error date out of range
SELECT daterange(('0001-01-01'::date + '262141years 11months 30days'::interval)::date, null, '()');

#parameterized input

query T
SELECT '(,)'::daterange;
----
(,)

query T
SELECT '(,1969-12-31)'::daterange;
----
(,1969-12-31)

query T
SELECT '(,1970-01-01)'::daterange;
----
(,1970-01-01)

query T
SELECT '(,1970-01-02)'::daterange;
----
(,1970-01-02)

query T
SELECT '(,]'::daterange;
----
(,)

query T
SELECT '(,1969-12-31]'::daterange;
----
(,1970-01-01)

query T
SELECT '(,1970-01-01]'::daterange;
----
(,1970-01-02)

query T
SELECT '(,1970-01-02]'::daterange;
----
(,1970-01-03)

query T
SELECT '(1969-12-31,)'::daterange;
----
[1970-01-01,)

query T
SELECT '(1969-12-31,1969-12-31)'::daterange;
----
empty

query T
SELECT '(1969-12-31,1970-01-01)'::daterange;
----
empty

query T
SELECT '(1969-12-31,1970-01-02)'::daterange;
----
[1970-01-01,1970-01-02)

query T
SELECT '(1969-12-31,]'::daterange;
----
[1970-01-01,)

query T
SELECT '(1969-12-31,1969-12-31]'::daterange;
----
empty

query T
SELECT '(1969-12-31,1970-01-01]'::daterange;
----
[1970-01-01,1970-01-02)

query T
SELECT '(1969-12-31,1970-01-02]'::daterange;
----
[1970-01-01,1970-01-03)

query T
SELECT '(1970-01-01,)'::daterange;
----
[1970-01-02,)

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01,1969-12-31)'::daterange;

query T
SELECT '(1970-01-01,1970-01-01)'::daterange;
----
empty

query T
SELECT '(1970-01-01,1970-01-02)'::daterange;
----
empty

query T
SELECT '(1970-01-01,]'::daterange;
----
[1970-01-02,)

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01,1969-12-31]'::daterange;

query T
SELECT '(1970-01-01,1970-01-01]'::daterange;
----
empty

query T
SELECT '(1970-01-01,1970-01-02]'::daterange;
----
[1970-01-02,1970-01-03)

query T
SELECT '(1970-01-02,)'::daterange;
----
[1970-01-03,)

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-02,1969-12-31)'::daterange;

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-02,1970-01-01)'::daterange;

query T
SELECT '(1970-01-02,1970-01-02)'::daterange;
----
empty

query T
SELECT '(1970-01-02,]'::daterange;
----
[1970-01-03,)

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-02,1969-12-31]'::daterange;

query error lower bound must be less than or equal to range upper bound
SELECT '(1970-01-02,1970-01-01]'::daterange;

query T
SELECT '(1970-01-02,1970-01-02]'::daterange;
----
empty

query T
SELECT '[,)'::daterange;
----
(,)

query T
SELECT '[,1969-12-31)'::daterange;
----
(,1969-12-31)

query T
SELECT '[,1970-01-01)'::daterange;
----
(,1970-01-01)

query T
SELECT '[,1970-01-02)'::daterange;
----
(,1970-01-02)

query T
SELECT '[,]'::daterange;
----
(,)

query T
SELECT '[,1969-12-31]'::daterange;
----
(,1970-01-01)

query T
SELECT '[,1970-01-01]'::daterange;
----
(,1970-01-02)

query T
SELECT '[,1970-01-02]'::daterange;
----
(,1970-01-03)

query T
SELECT '[1969-12-31,)'::daterange;
----
[1969-12-31,)

query T
SELECT '[1969-12-31,1969-12-31)'::daterange;
----
empty

query T
SELECT '[1969-12-31,1970-01-01)'::daterange;
----
[1969-12-31,1970-01-01)

query T
SELECT '[1969-12-31,1970-01-02)'::daterange;
----
[1969-12-31,1970-01-02)

query T
SELECT '[1969-12-31,]'::daterange;
----
[1969-12-31,)

query T
SELECT '[1969-12-31,1969-12-31]'::daterange;
----
[1969-12-31,1970-01-01)

query T
SELECT '[1969-12-31,1970-01-01]'::daterange;
----
[1969-12-31,1970-01-02)

query T
SELECT '[1969-12-31,1970-01-02]'::daterange;
----
[1969-12-31,1970-01-03)

query T
SELECT '[1970-01-01,)'::daterange;
----
[1970-01-01,)

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01,1969-12-31)'::daterange;

query T
SELECT '[1970-01-01,1970-01-01)'::daterange;
----
empty

query T
SELECT '[1970-01-01,1970-01-02)'::daterange;
----
[1970-01-01,1970-01-02)

query T
SELECT '[1970-01-01,]'::daterange;
----
[1970-01-01,)

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01,1969-12-31]'::daterange;

query T
SELECT '[1970-01-01,1970-01-01]'::daterange;
----
[1970-01-01,1970-01-02)

query T
SELECT '[1970-01-01,1970-01-02]'::daterange;
----
[1970-01-01,1970-01-03)

query T
SELECT '[1970-01-02,)'::daterange;
----
[1970-01-02,)

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-02,1969-12-31)'::daterange;

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-02,1970-01-01)'::daterange;

query T
SELECT '[1970-01-02,1970-01-02)'::daterange;
----
empty

query T
SELECT '[1970-01-02,]'::daterange;
----
[1970-01-02,)

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-02,1969-12-31]'::daterange;

query error lower bound must be less than or equal to range upper bound
SELECT '[1970-01-02,1970-01-01]'::daterange;

query T
SELECT '[1970-01-02,1970-01-02]'::daterange;
----
[1970-01-02,1970-01-03)

# Whitespace handling
query T
SELECT
	DISTINCT column1::daterange
FROM
	(
		VALUES
			('   empty'),
			('empty  '),
			('  [1970-01-01,)'),
			('[  1970-01-01,)'),
			('[1970-01-01  ,)'),
			('[  1970-01-01  ,)'),
('[1970-01-01,  1970-01-01)'),
			('[1970-01-01,1970-01-01  )'),
			('[1970-01-01,  1970-01-01  )'),
			('[1970-01-01,)  '),
			('  (,1970-01-01)'),
			('(,  1970-01-01)'),
			('  (,)  ')
	) t;
----
empty
(,)
(,1970-01-01)
[1970-01-01,)

# Input errors
query error invalid input syntax for type range
SELECT '[1,)'::daterange

query error invalid input syntax for type range
SELECT '(,1)'::daterange

statement ok
CREATE TABLE daterange_values (a daterange);

statement ok
INSERT INTO daterange_values VALUES
    (null),
    ('empty'),
    ('[,)'::daterange),
    ('[,1970-01-01]'::daterange),
    ('[,)'::daterange),
    ('[,]'::daterange),
    ('(,1970-01-01)'::daterange),
    ('(,1970-01-01]'::daterange),
    ('(,)'::daterange),
    ('(,]'::daterange),
    ('[1969-12-31,1970-01-01)'::daterange),
    ('[1969-12-31,1970-01-01]'::daterange),
    ('(1969-12-31,1970-01-01)'::daterange),
    ('(1969-12-31,1970-01-01]'::daterange),
    ('[1970-01-01,)'::daterange),
    ('[1970-01-01,]'::daterange),
    ('(1970-01-01,)'::daterange),
    ('(1970-01-01,]'::daterange);

query T
SELECT a AS t FROM daterange_values ORDER BY a;
----
empty
empty
(,1970-01-01)
(,1970-01-02)
(,1970-01-02)
(,)
(,)
(,)
(,)
(,)
[1969-12-31,1970-01-01)
[1969-12-31,1970-01-02)
[1970-01-01,1970-01-02)
[1970-01-01,)
[1970-01-01,)
[1970-01-02,)
[1970-01-02,)
NULL

mode cockroach

#
# daterange upper, lower
query TT rowsort
SELECT DISTINCT lower(a), upper(a) FROM daterange_values;
----
NULL        NULL
NULL        1970-01-01
NULL        1970-01-02
1970-01-01  NULL
1970-01-02  NULL
1969-12-31  1970-01-01
1969-12-31  1970-01-02
1970-01-01  1970-01-02

mode standard

# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	daterange_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= daterange(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM daterange_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

query T
SELECT a AS t FROM daterange_values EXCEPT SELECT column1 FROM (VALUES
    (daterange(null,'1970-01-01')),
    (daterange(null,'1970-01-01', '[]')),
    (daterange(null,null)),
    (daterange(null,null, '[]')),
    (daterange(null,'1970-01-01', '()')),
    (daterange(null,'1970-01-01', '(]')),
    (daterange(null,null, '()')),
    (daterange(null,null,'(]')),
    (daterange('1969-12-31','1970-01-01')),
    (daterange('1969-12-31','1970-01-01', '[]')),
    (daterange('1969-12-31','1970-01-01', '()')),
    (daterange('1969-12-31','1970-01-01','(]')),
    (daterange('1970-01-01',null)),
    (daterange('1970-01-01',null, '[]')),
    (daterange('1970-01-01',null,'()')),
    (daterange('1970-01-01',null,'(]'))
) t;
----
NULL

query error range constructor flags argument must not be null
SELECT daterange('1970-01-01','1970-01-01',null);

query error range constructor flags argument must not be null
SELECT daterange(null,null,null);


# Test range in list
query T
SELECT LIST['(,)', 'empty', '[1970-01-01,1970-01-03]']::daterange list::text;
----
{"(,)",empty,"[1970-01-01,1970-01-04)"}

query T
SELECT '{"(,)","empty","[1970-01-01,1970-01-03]"}'::daterange list::text;
----
{"(,)",empty,"[1970-01-01,1970-01-04)"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[1970-01-01,1970-01-03]']::_daterange;
----
{"(,)",empty,"[1970-01-01,1970-01-04)"}

query T
SELECT '{"(,)","empty","[1970-01-01,1970-01-03]"}'::_daterange;
----
{"(,)",empty,"[1970-01-01,1970-01-04)"}

# Test builtin functions

query B
select '(,)'::daterange = '(,)'::daterange;
----
true

query B
select '(,)'::daterange != 'empty'::daterange;
----
true

query B
select '(,)'::daterange > 'empty'::daterange;
----
true

query B
select '(,)'::daterange >= 'empty'::daterange;
----
true

query B
select '(,)'::daterange < 'empty'::daterange;
----
false

query B
select '(,)'::daterange <= 'empty'::daterange;
----
false

statement ok
CREATE TABLE daterange_test_values (v daterange);

statement ok
INSERT INTO daterange_test_values VALUES ('empty'),
('(,)'),
('(,1970-01-02)'),
('(1969-12-31,)'),
('[1969-12-31,1970-01-02)'),
('[1969-01-01,1969-01-02)'),
('[1971-01-01,1971-01-02)');

#
# daterange contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,1970-01-01)
{empty,"[1969-01-01,1969-01-02)"}
(,1970-01-02)
{empty,"(,1970-01-02)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)"}
(,)
{empty,"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1969-12-31,1970-01-01)
{empty}
[1969-12-31,1970-01-02)
{empty,"[1969-12-31,1970-01-02)"}
[1970-01-01,1970-01-02)
{empty}
[1970-01-01,)
{empty,"[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1970-01-02,)
{empty,"[1971-01-01,1971-01-02)"}

#
# daterange contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
(,1970-01-01)
{"(,1970-01-02)","(,)"}
(,1970-01-02)
{"(,1970-01-02)","(,)"}
(,)
{"(,)"}
[1969-12-31,1970-01-01)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)"}
[1969-12-31,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)"}
[1970-01-01,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
[1970-01-01,)
{"(,)","[1970-01-01,)"}
[1970-01-02,)
{"(,)","[1970-01-01,)"}

#
# daterange overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,1970-01-01)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)"}
(,1970-01-02)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
(,)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1969-12-31,1970-01-01)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)"}
[1969-12-31,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
[1970-01-01,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
[1970-01-01,)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1970-01-02,)
{"(,)","[1970-01-01,)","[1971-01-01,1971-01-02)"}

#
# daterange before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,1970-01-01)
{"[1970-01-01,)","[1971-01-01,1971-01-02)"}
(,1970-01-02)
{"[1971-01-01,1971-01-02)"}
[1969-12-31,1970-01-01)
{"[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1969-12-31,1970-01-02)
{"[1971-01-01,1971-01-02)"}
[1970-01-01,1970-01-02)
{"[1971-01-01,1971-01-02)"}

#
# daterange after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
[1969-12-31,1970-01-01)
{"[1969-01-01,1969-01-02)"}
[1969-12-31,1970-01-02)
{"[1969-01-01,1969-01-02)"}
[1970-01-01,1970-01-02)
{"[1969-01-01,1969-01-02)"}
[1970-01-01,)
{"[1969-01-01,1969-01-02)"}
[1970-01-02,)
{"(,1970-01-02)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)"}

#
# daterange overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,1970-01-01)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
(,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
(,)
{"(,)","[1970-01-01,)"}
[1969-12-31,1970-01-01)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1969-12-31,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1970-01-01,1970-01-02)
{"(,1970-01-02)","(,)","[1969-12-31,1970-01-02)","[1970-01-01,)","[1971-01-01,1971-01-02)"}
[1970-01-01,)
{"(,)","[1970-01-01,)"}
[1970-01-02,)
{"(,)","[1970-01-01,)"}

#
# daterange overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,1970-01-01)
{"(,1970-01-02)","(,)"}
(,1970-01-02)
{"(,1970-01-02)","(,)"}
(,)
{"(,1970-01-02)","(,)"}
[1969-12-31,1970-01-01)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)"}
[1969-12-31,1970-01-02)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)"}
[1970-01-01,1970-01-02)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
[1970-01-01,)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)"}
[1970-01-02,)
{"(,1970-01-02)","(,)","[1969-01-01,1969-01-02)","[1969-12-31,1970-01-02)","[1970-01-01,)"}

#
# daterange adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM daterange_values WHERE a IS NOT NULL
) daterange_values(a), daterange_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
(,1970-01-01)
{"[1970-01-01,)"}
[1969-12-31,1970-01-01)
{"[1970-01-01,)"}
[1970-01-02,)
{"(,1970-01-02)","[1969-12-31,1970-01-02)"}

#
# daterange union
query T
SELECT 'empty'::daterange + 'empty'::daterange
----
empty

query T
SELECT 'empty'::daterange + '(,1969-12-31)'::daterange
----
(,1969-12-31)

query T
SELECT '(,1969-12-31)'::daterange + 'empty'::daterange
----
(,1969-12-31)

query T
SELECT '(,1969-12-31)'::daterange + '(,1969-12-31)'::daterange
----
(,1969-12-31)

query T
SELECT 'empty'::daterange + '[1969-12-30,1970-01-03)'::daterange
----
[1969-12-30,1970-01-03)

query T
SELECT 'empty'::daterange + '[1970-01-02,)'::daterange
----
[1970-01-02,)

query T
SELECT 'empty'::daterange + '(,)'::daterange
----
(,)

query T
SELECT '(,1969-12-31)'::daterange + '[1969-12-30,1970-01-03)'::daterange
----
(,1970-01-03)

query error result of range union would not be contiguous
SELECT '(,1969-12-31)'::daterange + '[1970-01-02,)'::daterange

query T
SELECT '(,1969-12-31)'::daterange + '(,)'::daterange
----
(,)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange + 'empty'::daterange
----
[1969-12-30,1970-01-03)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange + '(,1969-12-31)'::daterange
----
(,1970-01-03)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange + '[1970-01-02,)'::daterange
----
[1969-12-30,)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange + '(,)'::daterange
----
(,)

query T
SELECT '[1970-01-02,)'::daterange + 'empty'::daterange
----
[1970-01-02,)

query error result of range union would not be contiguous
SELECT '[1970-01-02,)'::daterange + '(,1969-12-31)'::daterange

query T
SELECT '[1970-01-02,)'::daterange + '[1969-12-30,1970-01-03)'::daterange
----
[1969-12-30,)

query T
SELECT '[1970-01-02,)'::daterange + '(,)'::daterange
----
(,)

query T
SELECT '(,)'::daterange + 'empty'::daterange
----
(,)

query T
SELECT '(,)'::daterange + '(,1969-12-31)'::daterange
----
(,)

query T
SELECT '(,)'::daterange + '[1969-12-30,1970-01-03)'::daterange
----
(,)

query T
SELECT '(,)'::daterange + '[1970-01-02,)'::daterange
----
(,)

#
# daterange intersection

query T
SELECT 'empty'::daterange * 'empty'::daterange
----
empty

query T
SELECT 'empty'::daterange * '(,1969-12-31)'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange * 'empty'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange * '(,1969-12-31)'::daterange
----
(,1969-12-31)

query T
SELECT 'empty'::daterange * '[1969-12-30,1970-01-03)'::daterange
----
empty

query T
SELECT 'empty'::daterange * '[1970-01-02,)'::daterange
----
empty

query T
SELECT 'empty'::daterange * '(,)'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange * '[1969-12-30,1970-01-03)'::daterange
----
[1969-12-30,1969-12-31)

query T
SELECT '(,1969-12-31)'::daterange * '[1970-01-02,)'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange * '(,)'::daterange
----
(,1969-12-31)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange * 'empty'::daterange
----
empty

query T
SELECT '[1969-12-30,1970-01-03)'::daterange * '(,1969-12-31)'::daterange
----
[1969-12-30,1969-12-31)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange * '[1970-01-02,)'::daterange
----
[1970-01-02,1970-01-03)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange * '(,)'::daterange
----
[1969-12-30,1970-01-03)

query T
SELECT '[1970-01-02,)'::daterange * 'empty'::daterange
----
empty

query T
SELECT '[1970-01-02,)'::daterange * '(,1969-12-31)'::daterange
----
empty

query T
SELECT '[1970-01-02,)'::daterange * '[1969-12-30,1970-01-03)'::daterange
----
[1970-01-02,1970-01-03)

query T
SELECT '[1970-01-02,)'::daterange * '(,)'::daterange
----
[1970-01-02,)

query T
SELECT '(,)'::daterange * 'empty'::daterange
----
empty

query T
SELECT '(,)'::daterange * '(,1969-12-31)'::daterange
----
(,1969-12-31)

query T
SELECT '(,)'::daterange * '[1969-12-30,1970-01-03)'::daterange
----
[1969-12-30,1970-01-03)

query T
SELECT '(,)'::daterange * '[1970-01-02,)'::daterange
----
[1970-01-02,)

#
# daterange difference

query T
SELECT 'empty'::daterange - 'empty'::daterange
----
empty

query T
SELECT 'empty'::daterange - '(,1969-12-31)'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange - 'empty'::daterange
----
(,1969-12-31)

query T
SELECT '(,1969-12-31)'::daterange - '(,1969-12-31)'::daterange
----
empty

query T
SELECT 'empty'::daterange - '[1969-12-30,1970-01-03)'::daterange
----
empty

query T
SELECT 'empty'::daterange - '[1970-01-02,)'::daterange
----
empty

query T
SELECT 'empty'::daterange - '(,)'::daterange
----
empty

query T
SELECT '(,1969-12-31)'::daterange - '[1969-12-30,1970-01-03)'::daterange
----
(,1969-12-30)

query T
SELECT '(,1969-12-31)'::daterange - '[1970-01-02,)'::daterange
----
(,1969-12-31)

query T
SELECT '(,1969-12-31)'::daterange - '(,)'::daterange
----
empty

query T
SELECT '[1969-12-30,1970-01-03)'::daterange - 'empty'::daterange
----
[1969-12-30,1970-01-03)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange - '(,1969-12-31)'::daterange
----
[1969-12-31,1970-01-03)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange - '[1970-01-02,)'::daterange
----
[1969-12-30,1970-01-02)

query T
SELECT '[1969-12-30,1970-01-03)'::daterange - '(,)'::daterange
----
empty

query T
SELECT '[1970-01-02,)'::daterange - 'empty'::daterange
----
[1970-01-02,)

query T
SELECT '[1970-01-02,)'::daterange - '(,1969-12-31)'::daterange
----
[1970-01-02,)

query T
SELECT '[1970-01-02,)'::daterange - '[1969-12-30,1970-01-03)'::daterange
----
[1970-01-03,)

query T
SELECT '[1970-01-02,)'::daterange - '(,)'::daterange
----
empty

query T
SELECT '(,)'::daterange - 'empty'::daterange
----
(,)

query T
SELECT '(,)'::daterange - '(,1969-12-31)'::daterange
----
[1969-12-31,)

query error result of range difference would not be contiguous
SELECT '(,)'::daterange - '[1969-12-30,1970-01-03)'::daterange

query T
SELECT '(,)'::daterange - '[1970-01-02,)'::daterange
----
(,1970-01-02)

#
# numrange

query T
select '[0,100)'::numrange;
----
[0,100)

query T
select pg_typeof('[0,100)'::numrange);
----
numrange

query T
select 'empty'::numrange;
----
empty

query T
select pg_typeof('empty'::numrange);
----
numrange

query T
select null::numrange
----
NULL

# Parameterized input on
#   xjoin(["(","["],
#       xjoin([null,-1,0,1],
#           xjoin(
#               [null,-1,0,1],
#               ["),"]"]
#           )
#       )
#   )

query T
SELECT '(,)'::numrange;
----
(,)

query T
SELECT '(,-1)'::numrange;
----
(,-1)

query T
SELECT '(,0)'::numrange;
----
(,0)

query T
SELECT '(,1)'::numrange;
----
(,1)

query T
SELECT '(,]'::numrange;
----
(,)

query T
SELECT '(,-1]'::numrange;
----
(,-1]

query T
SELECT '(,0]'::numrange;
----
(,0]

query T
SELECT '(,1]'::numrange;
----
(,1]

query T
SELECT '(-1,)'::numrange;
----
(-1,)

query T
SELECT '(-1,-1)'::numrange;
----
empty

query T
SELECT '(-1,0)'::numrange;
----
(-1,0)

query T
SELECT '(-1,1)'::numrange;
----
(-1,1)

query T
SELECT '(-1,]'::numrange;
----
(-1,)

query T
SELECT '(-1,-1]'::numrange;
----
empty

query T
SELECT '(-1,0]'::numrange;
----
(-1,0]

query T
SELECT '(-1,1]'::numrange;
----
(-1,1]

query T
SELECT '(0,)'::numrange;
----
(0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1)'::numrange;

query T
SELECT '(0,0)'::numrange;
----
empty

query T
SELECT '(0,1)'::numrange;
----
(0,1)

query T
SELECT '(0,]'::numrange;
----
(0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(0,-1]'::numrange;

query T
SELECT '(0,0]'::numrange;
----
empty

query T
SELECT '(0,1]'::numrange;
----
(0,1]

query T
SELECT '(1,)'::numrange;
----
(1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1)'::numrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0)'::numrange;

query T
SELECT '(1,1)'::numrange;
----
empty

query T
SELECT '(1,]'::numrange;
----
(1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,-1]'::numrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1,0]'::numrange;

query T
SELECT '(1,1]'::numrange;
----
empty

query T
SELECT '[,)'::numrange;
----
(,)

query T
SELECT '[,-1)'::numrange;
----
(,-1)

query T
SELECT '[,0)'::numrange;
----
(,0)

query T
SELECT '[,1)'::numrange;
----
(,1)

query T
SELECT '[,]'::numrange;
----
(,)

query T
SELECT '[,-1]'::numrange;
----
(,-1]

query T
SELECT '[,0]'::numrange;
----
(,0]

query T
SELECT '[,1]'::numrange;
----
(,1]

query T
SELECT '[-1,)'::numrange;
----
[-1,)

query T
SELECT '[-1,-1)'::numrange;
----
empty

query T
SELECT '[-1,0)'::numrange;
----
[-1,0)

query T
SELECT '[-1,1)'::numrange;
----
[-1,1)

query T
SELECT '[-1,]'::numrange;
----
[-1,)

query T
SELECT '[-1,-1]'::numrange;
----
[-1,-1]

query T
SELECT '[-1,0]'::numrange;
----
[-1,0]

query T
SELECT '[-1,1]'::numrange;
----
[-1,1]

query T
SELECT '[0,)'::numrange;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1)'::numrange;

query T
SELECT '[0,0)'::numrange;
----
empty

query T
SELECT '[0,1)'::numrange;
----
[0,1)

query T
SELECT '[0,]'::numrange;
----
[0,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[0,-1]'::numrange;

query T
SELECT '[0,0]'::numrange;
----
[0,0]

query T
SELECT '[0,1]'::numrange;
----
[0,1]

query T
SELECT '[1,)'::numrange;
----
[1,)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1)'::numrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0)'::numrange;

query T
SELECT '[1,1)'::numrange;
----
empty

query T
SELECT '[1,]'::numrange;
----
[1,)

# Range bound errors
query error range lower bound must be less than or equal to range upper bound
SELECT '[1,-1]'::numrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1,0]'::numrange;

# Whitespace handling
query T
SELECT
	DISTINCT column1::numrange
FROM
	(
		VALUES
			('   empty'),
			('empty  '),
			('  [1,)'),
			('[  1,)'),
			('[1  ,)'),
			('[  1  ,)'),
('[1,  2)'),
			('[1,2  )'),
			('[1,  2  )'),
			('[1,)  '),
			('  (,1)'),
			('(,  1)'),
			('  (,)  ')
	) t;
----
empty
(,)
(,1)
[1,)
[1,2)

# Input errors
query error invalid input syntax for type range
SELECT '[1,  )'::numrange

query error invalid input syntax for type range
SELECT '(  ,1)'::numrange

query error invalid input syntax for type range
SELECT '(  ,  )'::numrange

query error invalid input syntax for type range
SELECT 'emptyy'::numrange;

query error invalid input syntax for type range
SELECT ''::numrange;

query error invalid input syntax for type range
SELECT '1'::numrange;

query error invalid input syntax for type range
SELECT 'd'::numrange;

query error invalid input syntax for type range
SELECT ','::numrange;

query error invalid input syntax for type range
SELECT ')'::numrange;

query error invalid input syntax for type range
SELECT '{'::numrange;

query error invalid input syntax for type range
SELECT '('::numrange;

query error invalid input syntax for type range
SELECT '['::numrange;

query error invalid input syntax for type range
SELECT '(1)'::numrange;

query error invalid input syntax for type range
SELECT '[1]'::numrange;

query error invalid input syntax for type range
SELECT '(1,'::numrange;

query error invalid input syntax for type range
SELECT '[1,'::numrange;

query error invalid input syntax for type range
SELECT '(1,1'::numrange;

query error invalid input syntax for type range
SELECT '[1,1'::numrange;

query error invalid input syntax for type range
SELECT '(1,1]1'::numrange;

query error invalid input syntax for type range
SELECT '[1,1]a'::numrange;

query error invalid input syntax for type range
SELECT '(1,1]]'::numrange;

query error invalid input syntax for type range
SELECT '[1,1]}'::numrange;

query error invalid input syntax for type range
SELECT '(1,1]  ]'::numrange;

query error invalid input syntax for type range
SELECT '[1,1]  }'::numrange;

statement ok
CREATE TABLE numrange_values (a numrange);

statement ok
INSERT INTO numrange_values VALUES
    (null),
    ('empty'),
    ('[,1)'::numrange),
    ('[,1]'::numrange),
    ('[,)'::numrange),
    ('[,]'::numrange),
    ('(,1)'::numrange),
    ('(,1]'::numrange),
    ('(,)'::numrange),
    ('(,]'::numrange),
    ('[-1,1)'::numrange),
    ('[-1,1]'::numrange),
    ('(-1,1)'::numrange),
    ('(-1,1]'::numrange),
    ('[0,0)'::numrange),
    ('[0,0]'::numrange),
    ('(0,0)'::numrange),
    ('(0,0]'::numrange),
    ('[1,)'::numrange),
    ('[1,]'::numrange),
    ('(1,)'::numrange),
    ('(1,]'::numrange);

query T
SELECT a AS t FROM numrange_values ORDER BY a;
----
empty
empty
empty
empty
(,1)
(,1)
(,1]
(,1]
(,)
(,)
(,)
(,)
[-1,1)
[-1,1]
(-1,1)
(-1,1]
[0,0]
[1,)
[1,)
(1,)
(1,)
NULL

query T
SELECT a AS t FROM numrange_values EXCEPT SELECT column1 FROM (VALUES
    (numrange(null,1)),
    (numrange(null,1, '[]')),
    (numrange(null,null)),
    (numrange(null,null, '[]')),
    (numrange(null,1, '()')),
    (numrange(null,1, '(]')),
    (numrange(null,null, '()')),
    (numrange(null,null,'(]')),
    (numrange(-1,1)),
    (numrange(-1,1, '[]')),
    (numrange(-1,1, '()')),
    (numrange(-1,1,'(]')),
    (numrange(0,0)),
    (numrange(0,0, '[]')),
    (numrange(0,0,'()')),
    (numrange(0,0,'(]')),
    (numrange(1,null)),
    (numrange(1,null, '[]')),
    (numrange(1,null,'()')),
    (numrange(1,null,'(]'))
) t;
----
NULL

query error operator does not exist: numrange @> double precision
SELECT numrange(-1.0,1.0) @> 0.1::float;

query error range constructor flags argument must not be null
SELECT numrange(1,2,null);

query error range constructor flags argument must not be null
SELECT numrange(null,null,null);

mode cockroach

#
# numrange upper, lower
query TT rowsort
SELECT DISTINCT lower(a), upper(a) FROM numrange_values;
----
NULL  NULL
NULL  1
1     NULL
0     0
-1    1

mode standard

# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	numrange_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= numrange(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM numrange_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

query error invalid range bound flags
SELECT numrange(null,null,' (]');

query error invalid range bound flags
SELECT numrange(null,null,'(] ');

query error invalid range bound flags
SELECT numrange(null,null,'( ]');

query error invalid range bound flags
SELECT numrange(null,null,'(,]');

query error invalid range bound flags
SELECT numrange(null,null,'a()');

query error invalid range bound flags
SELECT numrange(null,null,'(a)');

query error invalid range bound flags
SELECT numrange(null,null,'()a');

query error invalid range bound flags
SELECT numrange(null,null,'(()');

query error invalid range bound flags
SELECT numrange(null,null,'())');

# Test range in list
query T
SELECT LIST['(,)', 'empty', '[-1,1]']::numrange list::text;
----
{"(,)",empty,"[-1,1]"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::numrange list::text;
----
{"(,)",empty,"[-1,1]"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[-1,1]']::_numrange;
----
{"(,)",empty,"[-1,1]"}

query T
SELECT '{"(,)","empty","[-1,1]"}'::_numrange;
----
{"(,)",empty,"[-1,1]"}

# Test builtin functions

query B
select '(,)'::numrange = '(,)'::numrange;
----
true

query B
select '(,)'::numrange != 'empty'::numrange;
----
true

query B
select '(,)'::numrange > 'empty'::numrange;
----
true

query B
select '(,)'::numrange >= 'empty'::numrange;
----
true

query B
select '(,)'::numrange < 'empty'::numrange;
----
false

query B
select '(,)'::numrange <= 'empty'::numrange;
----
false

query B
select '(1,2)'::numrange < '(3,4)'::numrange;
----
true

query B
select '(1,2)'::numrange <= '(3,4)'::numrange;
----
true

query B
select '(1,2)'::numrange = '(3,4)'::numrange;
----
false

query B
select '(1,2)'::numrange >= '(3,4)'::numrange;
----
false

query B
select '(1,2)'::numrange > '(3,4)'::numrange;
----
false

query B
select '(3,4)'::numrange >= '(3,4)'::numrange;
----
true

query B
select '(3,4)'::numrange = '(3,4)'::numrange;
----
true

query B
select '(3,4)'::numrange <= '(3,4)'::numrange;
----
true

query B
select '(3,4)'::numrange < '(3,4)'::numrange;
----
false

query B
select '(3,4)'::numrange > '(3,4)'::numrange;
----
false

query B
select '(5,6)'::numrange < '(3,4)'::numrange;
----
false

query B
select '(5,6)'::numrange <= '(3,4)'::numrange;
----
false

query B
select '(5,6)'::numrange = '(3,4)'::numrange;
----
false

query B
select '(5,6)'::numrange >= '(3,4)'::numrange;
----
true

query B
select '(5,6)'::numrange > '(3,4)'::numrange;
----
true

statement ok
CREATE TABLE numrange_test_values (v numrange);

statement ok
INSERT INTO numrange_test_values VALUES
('empty'),
('(,)'),
('(,1)'),
('(-1,)'),
('[-1,1)'),
('[-99,-50)'),
('[50,99)');

#
# numrange contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,1)
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,1]
{empty,"(,1)","[-99,-50)","[-1,1)"}
(,)
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)","[50,99)"}
[-1,1)
{empty,"[-1,1)"}
[-1,1]
{empty,"[-1,1)"}
(-1,1)
{empty}
(-1,1]
{empty}
[0,0]
{empty}
[1,)
{empty,"[50,99)"}
(1,)
{empty,"[50,99)"}

#
# numrange contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)","[50,99)"}
(,1)
{"(,1)","(,)"}
(,1]
{"(,)"}
(,)
{"(,)"}
[-1,1)
{"(,1)","(,)","[-1,1)"}
[-1,1]
{"(,)"}
(-1,1)
{"(,1)","(,)","[-1,1)","(-1,)"}
(-1,1]
{"(,)","(-1,)"}
[0,0]
{"(,1)","(,)","[-1,1)","(-1,)"}
[1,)
{"(,)","(-1,)"}
(1,)
{"(,)","(-1,)"}

#
# numrange overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
(,1]
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
(,)
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)","[50,99)"}
[-1,1)
{"(,1)","(,)","[-1,1)","(-1,)"}
[-1,1]
{"(,1)","(,)","[-1,1)","(-1,)"}
(-1,1)
{"(,1)","(,)","[-1,1)","(-1,)"}
(-1,1]
{"(,1)","(,)","[-1,1)","(-1,)"}
[0,0]
{"(,1)","(,)","[-1,1)","(-1,)"}
[1,)
{"(,)","(-1,)","[50,99)"}
(1,)
{"(,)","(-1,)","[50,99)"}

#
# numrange before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,1)
{"[50,99)"}
(,1]
{"[50,99)"}
[-1,1)
{"[50,99)"}
[-1,1]
{"[50,99)"}
(-1,1)
{"[50,99)"}
(-1,1]
{"[50,99)"}
[0,0]
{"[50,99)"}

#
# numrange after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
[-1,1)
{"[-99,-50)"}
[-1,1]
{"[-99,-50)"}
(-1,1)
{"[-99,-50)"}
(-1,1]
{"[-99,-50)"}
[0,0]
{"[-99,-50)"}
[1,)
{"(,1)","[-99,-50)","[-1,1)"}
(1,)
{"(,1)","[-99,-50)","[-1,1)"}

#
# numrange overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)","[-1,1)","(-1,)","[50,99)"}
(,1]
{"(,)","(-1,)","[50,99)"}
(,)
{"(,)","(-1,)"}
[-1,1)
{"(,1)","(,)","[-1,1)","(-1,)","[50,99)"}
[-1,1]
{"(,)","(-1,)","[50,99)"}
(-1,1)
{"(,1)","(,)","[-1,1)","(-1,)","[50,99)"}
(-1,1]
{"(,)","(-1,)","[50,99)"}
[0,0]
{"(,1)","(,)","[-1,1)","(-1,)","[50,99)"}
[1,)
{"(,)","(-1,)"}
(1,)
{"(,)","(-1,)"}

#
# numrange overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,1)
{"(,1)","(,)"}
(,1]
{"(,1)","(,)"}
(,)
{"(,1)","(,)"}
[-1,1)
{"(,1)","(,)","[-99,-50)","[-1,1)"}
[-1,1]
{"(,1)","(,)","[-99,-50)","[-1,1)"}
(-1,1)
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
(-1,1]
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
[0,0]
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
[1,)
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}
(1,)
{"(,1)","(,)","[-99,-50)","[-1,1)","(-1,)"}

#
# numrange adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM numrange_values WHERE a IS NOT NULL
) numrange_values(a), numrange_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
[1,)
{"(,1)","[-1,1)"}


query T
SELECT '[1.1,1.1]'::numrange;
----
[1.1,1.1]

query T
SELECT '[1.1,1.2]'::numrange;
----
[1.1,1.2]

query T
SELECT '[1.1,1.1)'::numrange;
----
empty

query T
SELECT numrange(1.1::numeric(38,1),1.2::numeric(38,2));
----
[1.1,1.2)

query T
SELECT numrange(1.1::numeric(38,2),1.2::numeric(38,1));
----
[1.1,1.2)

query T
SELECT numrange(-1.1::numeric(38,10),1.2::numeric(38,0));
----
[-1.1,1)

query T
SELECT numrange(-1.1::numeric(38,0),1.2::numeric(38,10));
----
[-1,1.2)

query error range lower bound must be less than or equal to range upper bound
SELECT numrange(1.1::numeric(38,2),1.2::numeric(38,0));

#
# numrange union
query T
SELECT 'empty'::numrange + 'empty'::numrange
----
empty

query T
SELECT 'empty'::numrange + '(,-1]'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange + '(,-1]'::numrange
----
(,-1]

query T
SELECT 'empty'::numrange + '(,-1)'::numrange
----
(,-1)

query T
SELECT 'empty'::numrange + '(-2,2)'::numrange
----
(-2,2)

query T
SELECT 'empty'::numrange + '[-2,2]'::numrange
----
[-2,2]

query T
SELECT 'empty'::numrange + '[1,)'::numrange
----
[1,)

query T
SELECT 'empty'::numrange + '(1,)'::numrange
----
(1,)

query T
SELECT 'empty'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '(,-1]'::numrange + 'empty'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange + '(,-1)'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange + '(-2,2)'::numrange
----
(,2)

query T
SELECT '(,-1]'::numrange + '[-2,2]'::numrange
----
(,2]

query error result of range union would not be contiguous
SELECT '(,-1]'::numrange + '[1,)'::numrange

query error result of range union would not be contiguous
SELECT '(,-1]'::numrange + '(1,)'::numrange

query T
SELECT '(,-1]'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '(,-1)'::numrange + 'empty'::numrange
----
(,-1)

query T
SELECT '(,-1)'::numrange + '(,-1]'::numrange
----
(,-1]

query T
SELECT '(,-1)'::numrange + '(-2,2)'::numrange
----
(,2)

query T
SELECT '(,-1)'::numrange + '[-2,2]'::numrange
----
(,2]

query error result of range union would not be contiguous
SELECT '(,-1)'::numrange + '[1,)'::numrange

query error result of range union would not be contiguous
SELECT '(,-1)'::numrange + '(1,)'::numrange

query T
SELECT '(,-1)'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '(-2,2)'::numrange + 'empty'::numrange
----
(-2,2)

query T
SELECT '(-2,2)'::numrange + '(,-1]'::numrange
----
(,2)

query T
SELECT '(-2,2)'::numrange + '(,-1)'::numrange
----
(,2)

query T
SELECT '(-2,2)'::numrange + '[-2,2]'::numrange
----
[-2,2]

query T
SELECT '(-2,2)'::numrange + '[1,)'::numrange
----
(-2,)

query T
SELECT '(-2,2)'::numrange + '(1,)'::numrange
----
(-2,)

query T
SELECT '(-2,2)'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '[-2,2]'::numrange + 'empty'::numrange
----
[-2,2]

query T
SELECT '[-2,2]'::numrange + '(,-1]'::numrange
----
(,2]

query T
SELECT '[-2,2]'::numrange + '(,-1)'::numrange
----
(,2]

query T
SELECT '[-2,2]'::numrange + '(-2,2)'::numrange
----
[-2,2]

query T
SELECT '[-2,2]'::numrange + '[1,)'::numrange
----
[-2,)

query T
SELECT '[-2,2]'::numrange + '(1,)'::numrange
----
[-2,)

query T
SELECT '[-2,2]'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '[1,)'::numrange + 'empty'::numrange
----
[1,)

query error result of range union would not be contiguous
SELECT '[1,)'::numrange + '(,-1]'::numrange

query error result of range union would not be contiguous
SELECT '[1,)'::numrange + '(,-1)'::numrange

query T
SELECT '[1,)'::numrange + '(-2,2)'::numrange
----
(-2,)

query T
SELECT '[1,)'::numrange + '[-2,2]'::numrange
----
[-2,)

query T
SELECT '[1,)'::numrange + '(1,)'::numrange
----
[1,)

query T
SELECT '[1,)'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '(1,)'::numrange + 'empty'::numrange
----
(1,)

query error result of range union would not be contiguous
SELECT '(1,)'::numrange + '(,-1]'::numrange

query error result of range union would not be contiguous
SELECT '(1,)'::numrange + '(,-1)'::numrange

query T
SELECT '(1,)'::numrange + '(-2,2)'::numrange
----
(-2,)

query T
SELECT '(1,)'::numrange + '[-2,2]'::numrange
----
[-2,)

query T
SELECT '(1,)'::numrange + '[1,)'::numrange
----
[1,)

query T
SELECT '(1,)'::numrange + '(,)'::numrange
----
(,)

query T
SELECT '(,)'::numrange + 'empty'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '(,-1]'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '(,-1)'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '(-2,2)'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '[-2,2]'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '[1,)'::numrange
----
(,)

query T
SELECT '(,)'::numrange + '(1,)'::numrange
----
(,)

#
# numrange intersection

query T
SELECT 'empty'::numrange * 'empty'::numrange
----
empty

query T
SELECT 'empty'::numrange * '(,-1]'::numrange
----
empty

query T
SELECT '(,-1]'::numrange * 'empty'::numrange
----
empty

query T
SELECT '(,-1]'::numrange * '(,-1]'::numrange
----
(,-1]

query T
SELECT 'empty'::numrange * '(,-1)'::numrange
----
empty

query T
SELECT 'empty'::numrange * '(-2,2)'::numrange
----
empty

query T
SELECT 'empty'::numrange * '[-2,2]'::numrange
----
empty

query T
SELECT 'empty'::numrange * '[1,)'::numrange
----
empty

query T
SELECT 'empty'::numrange * '(1,)'::numrange
----
empty

query T
SELECT 'empty'::numrange * '(,)'::numrange
----
empty

query T
SELECT '(,-1]'::numrange * '(,-1)'::numrange
----
(,-1)

query T
SELECT '(,-1]'::numrange * '(-2,2)'::numrange
----
(-2,-1]

query T
SELECT '(,-1]'::numrange * '[-2,2]'::numrange
----
[-2,-1]

query T
SELECT '(,-1]'::numrange * '[1,)'::numrange
----
empty

query T
SELECT '(,-1]'::numrange * '(1,)'::numrange
----
empty

query T
SELECT '(,-1]'::numrange * '(,)'::numrange
----
(,-1]

query T
SELECT '(,-1)'::numrange * 'empty'::numrange
----
empty

query T
SELECT '(,-1)'::numrange * '(,-1]'::numrange
----
(,-1)

query T
SELECT '(,-1)'::numrange * '(-2,2)'::numrange
----
(-2,-1)

query T
SELECT '(,-1)'::numrange * '[-2,2]'::numrange
----
[-2,-1)

query T
SELECT '(,-1)'::numrange * '[1,)'::numrange
----
empty

query T
SELECT '(,-1)'::numrange * '(1,)'::numrange
----
empty

query T
SELECT '(,-1)'::numrange * '(,)'::numrange
----
(,-1)

query T
SELECT '(-2,2)'::numrange * 'empty'::numrange
----
empty

query T
SELECT '(-2,2)'::numrange * '(,-1]'::numrange
----
(-2,-1]

query T
SELECT '(-2,2)'::numrange * '(,-1)'::numrange
----
(-2,-1)

query T
SELECT '(-2,2)'::numrange * '[-2,2]'::numrange
----
(-2,2)

query T
SELECT '(-2,2)'::numrange * '[1,)'::numrange
----
[1,2)

query T
SELECT '(-2,2)'::numrange * '(1,)'::numrange
----
(1,2)

query T
SELECT '(-2,2)'::numrange * '(,)'::numrange
----
(-2,2)

query T
SELECT '[-2,2]'::numrange * 'empty'::numrange
----
empty

query T
SELECT '[-2,2]'::numrange * '(,-1]'::numrange
----
[-2,-1]

query T
SELECT '[-2,2]'::numrange * '(,-1)'::numrange
----
[-2,-1)

query T
SELECT '[-2,2]'::numrange * '(-2,2)'::numrange
----
(-2,2)

query T
SELECT '[-2,2]'::numrange * '[1,)'::numrange
----
[1,2]

query T
SELECT '[-2,2]'::numrange * '(1,)'::numrange
----
(1,2]

query T
SELECT '[-2,2]'::numrange * '(,)'::numrange
----
[-2,2]

query T
SELECT '[1,)'::numrange * 'empty'::numrange
----
empty

query T
SELECT '[1,)'::numrange * '(,-1]'::numrange
----
empty

query T
SELECT '[1,)'::numrange * '(,-1)'::numrange
----
empty

query T
SELECT '[1,)'::numrange * '(-2,2)'::numrange
----
[1,2)

query T
SELECT '[1,)'::numrange * '[-2,2]'::numrange
----
[1,2]

query T
SELECT '[1,)'::numrange * '(1,)'::numrange
----
(1,)

query T
SELECT '[1,)'::numrange * '(,)'::numrange
----
[1,)

query T
SELECT '(1,)'::numrange * 'empty'::numrange
----
empty

query T
SELECT '(1,)'::numrange * '(,-1]'::numrange
----
empty

query T
SELECT '(1,)'::numrange * '(,-1)'::numrange
----
empty

query T
SELECT '(1,)'::numrange * '(-2,2)'::numrange
----
(1,2)

query T
SELECT '(1,)'::numrange * '[-2,2]'::numrange
----
(1,2]

query T
SELECT '(1,)'::numrange * '[1,)'::numrange
----
(1,)

query T
SELECT '(1,)'::numrange * '(,)'::numrange
----
(1,)

query T
SELECT '(,)'::numrange * 'empty'::numrange
----
empty

query T
SELECT '(,)'::numrange * '(,-1]'::numrange
----
(,-1]

query T
SELECT '(,)'::numrange * '(,-1)'::numrange
----
(,-1)

query T
SELECT '(,)'::numrange * '(-2,2)'::numrange
----
(-2,2)

query T
SELECT '(,)'::numrange * '[-2,2]'::numrange
----
[-2,2]

query T
SELECT '(,)'::numrange * '[1,)'::numrange
----
[1,)

query T
SELECT '(,)'::numrange * '(1,)'::numrange
----
(1,)

#
# numrange difference

query T
SELECT 'empty'::numrange - 'empty'::numrange
----
empty

query T
SELECT 'empty'::numrange - '(,-1]'::numrange
----
empty

query T
SELECT '(,-1]'::numrange - '(,-1]'::numrange
----
empty

query T
SELECT 'empty'::numrange - '(,-1)'::numrange
----
empty

query T
SELECT 'empty'::numrange - '(-2,2)'::numrange
----
empty

query T
SELECT 'empty'::numrange - '[-2,2]'::numrange
----
empty

query T
SELECT 'empty'::numrange - '[1,)'::numrange
----
empty

query T
SELECT 'empty'::numrange - '(1,)'::numrange
----
empty

query T
SELECT 'empty'::numrange - '(,)'::numrange
----
empty

query T
SELECT '(,-1]'::numrange - 'empty'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange - '(,-1)'::numrange
----
[-1,-1]

query T
SELECT '(,-1]'::numrange - '(-2,2)'::numrange
----
(,-2]

query T
SELECT '(,-1]'::numrange - '[-2,2]'::numrange
----
(,-2)

query T
SELECT '(,-1]'::numrange - '[1,)'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange - '(1,)'::numrange
----
(,-1]

query T
SELECT '(,-1]'::numrange - '(,)'::numrange
----
empty

query T
SELECT '(,-1)'::numrange - 'empty'::numrange
----
(,-1)

query T
SELECT '(,-1)'::numrange - '(,-1]'::numrange
----
empty

query T
SELECT '(,-1)'::numrange - '(-2,2)'::numrange
----
(,-2]

query T
SELECT '(,-1)'::numrange - '[-2,2]'::numrange
----
(,-2)

query T
SELECT '(,-1)'::numrange - '[1,)'::numrange
----
(,-1)

query T
SELECT '(,-1)'::numrange - '(1,)'::numrange
----
(,-1)

query T
SELECT '(,-1)'::numrange - '(,)'::numrange
----
empty

query T
SELECT '(-2,2)'::numrange - 'empty'::numrange
----
(-2,2)

query T
SELECT '(-2,2)'::numrange - '(,-1]'::numrange
----
(-1,2)

query T
SELECT '(-2,2)'::numrange - '(,-1)'::numrange
----
[-1,2)

query T
SELECT '(-2,2)'::numrange - '[-2,2]'::numrange
----
empty

query T
SELECT '(-2,2)'::numrange - '[1,)'::numrange
----
(-2,1)

query T
SELECT '(-2,2)'::numrange - '(1,)'::numrange
----
(-2,1]

query T
SELECT '(-2,2)'::numrange - '(,)'::numrange
----
empty

query T
SELECT '[-2,2]'::numrange - 'empty'::numrange
----
[-2,2]

query T
SELECT '[-2,2]'::numrange - '(,-1]'::numrange
----
(-1,2]

query T
SELECT '[-2,2]'::numrange - '(,-1)'::numrange
----
[-1,2]

query error result of range difference would not be contiguous
SELECT '[-2,2]'::numrange - '(-2,2)'::numrange

query T
SELECT '[-2,2]'::numrange - '[1,)'::numrange
----
[-2,1)

query T
SELECT '[-2,2]'::numrange - '(1,)'::numrange
----
[-2,1]

query T
SELECT '[-2,2]'::numrange - '(,)'::numrange
----
empty

query T
SELECT '[1,)'::numrange - 'empty'::numrange
----
[1,)

query T
SELECT '[1,)'::numrange - '(,-1]'::numrange
----
[1,)

query T
SELECT '[1,)'::numrange - '(,-1)'::numrange
----
[1,)

query T
SELECT '[1,)'::numrange - '(-2,2)'::numrange
----
[2,)

query T
SELECT '[1,)'::numrange - '[-2,2]'::numrange
----
(2,)

query T
SELECT '[1,)'::numrange - '(1,)'::numrange
----
[1,1]

query T
SELECT '[1,)'::numrange - '(,)'::numrange
----
empty

query T
SELECT '(1,)'::numrange - 'empty'::numrange
----
(1,)

query T
SELECT '(1,)'::numrange - '(,-1]'::numrange
----
(1,)

query T
SELECT '(1,)'::numrange - '(,-1)'::numrange
----
(1,)

query T
SELECT '(1,)'::numrange - '(-2,2)'::numrange
----
[2,)

query T
SELECT '(1,)'::numrange - '[-2,2]'::numrange
----
(2,)

query T
SELECT '(1,)'::numrange - '[1,)'::numrange
----
empty

query T
SELECT '(1,)'::numrange - '(,)'::numrange
----
empty

query T
SELECT '(,)'::numrange - 'empty'::numrange
----
(,)

query T
SELECT '(,)'::numrange - '(,-1]'::numrange
----
(-1,)

query T
SELECT '(,)'::numrange - '(,-1)'::numrange
----
[-1,)

query error result of range difference would not be contiguous
SELECT '(,)'::numrange - '(-2,2)'::numrange

query error result of range difference would not be contiguous
SELECT '(,)'::numrange - '[-2,2]'::numrange

query T
SELECT '(,)'::numrange - '[1,)'::numrange
----
(,1)

query T
SELECT '(,)'::numrange - '(1,)'::numrange
----
(,1]

#
# tsrange

query T
select '[1970-01-01 00:00:00,1971-01-01 00:00:00)'::tsrange;
----
["1970-01-01 00:00:00","1971-01-01 00:00:00")

query T
select pg_typeof('[1970-01-01 00:00:00,1971-01-01 00:00:00)'::tsrange);
----
tsrange

query T
select 'empty'::tsrange;
----
empty

query T
select pg_typeof('empty'::tsrange);
----
tsrange

query T
select null::tsrange
----
NULL

query T
SELECT '(,)'::tsrange;
----
(,)

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange;
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1970-01-01 00:00:00)'::tsrange;
----
(,"1970-01-01 00:00:00")

query T
SELECT '(,1970-01-01 00:00:01)'::tsrange;
----
(,"1970-01-01 00:00:01")

query T
SELECT '(,]'::tsrange;
----
(,)

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange;
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1970-01-01 00:00:00]'::tsrange;
----
(,"1970-01-01 00:00:00"]

query T
SELECT '(,1970-01-01 00:00:01]'::tsrange;
----
(,"1970-01-01 00:00:01"]

query T
SELECT '(1969-12-31 11:59:59,)'::tsrange;
----
("1969-12-31 11:59:59",)

query T
SELECT '(1969-12-31 11:59:59,1969-12-31 11:59:59)'::tsrange;
----
empty

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:00)'::tsrange;
----
("1969-12-31 11:59:59","1970-01-01 00:00:00")

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:01)'::tsrange;
----
("1969-12-31 11:59:59","1970-01-01 00:00:01")

query T
SELECT '(1969-12-31 11:59:59,]'::tsrange;
----
("1969-12-31 11:59:59",)

query T
SELECT '(1969-12-31 11:59:59,1969-12-31 11:59:59]'::tsrange;
----
empty

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:00]'::tsrange;
----
("1969-12-31 11:59:59","1970-01-01 00:00:00"]

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:01]'::tsrange;
----
("1969-12-31 11:59:59","1970-01-01 00:00:01"]

query T
SELECT '(1970-01-01 00:00:00,)'::tsrange;
----
("1970-01-01 00:00:00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:00,1969-12-31 11:59:59)'::tsrange;

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:00)'::tsrange;
----
empty

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:01)'::tsrange;
----
("1970-01-01 00:00:00","1970-01-01 00:00:01")

query T
SELECT '(1970-01-01 00:00:00,]'::tsrange;
----
("1970-01-01 00:00:00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:00,1969-12-31 11:59:59]'::tsrange;

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:00]'::tsrange;
----
empty

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:01]'::tsrange;
----
("1970-01-01 00:00:00","1970-01-01 00:00:01"]

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange;
----
("1970-01-01 00:00:01",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1969-12-31 11:59:59)'::tsrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1970-01-01 00:00:00)'::tsrange;

query T
SELECT '(1970-01-01 00:00:01.1,1970-01-01 00:00:01.1)'::tsrange;
----
empty

query T
SELECT '(1970-01-01 00:00:01,]'::tsrange;
----
("1970-01-01 00:00:01",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1969-12-31 11:59:59]'::tsrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1970-01-01 00:00:00]'::tsrange;

query T
SELECT '(1970-01-01 00:00:01.1, 1970-01-01 00:00:01.1]'::tsrange;
----
empty

query T
SELECT '[,)'::tsrange;
----
(,)

query T
SELECT '[,1969-12-31 11:59:59)'::tsrange;
----
(,"1969-12-31 11:59:59")

query T
SELECT '[,1970-01-01 00:00:00)'::tsrange;
----
(,"1970-01-01 00:00:00")

query T
SELECT '[,1970-01-01 00:00:01)'::tsrange;
----
(,"1970-01-01 00:00:01")

query T
SELECT '[,]'::tsrange;
----
(,)

query T
SELECT '[,1969-12-31 11:59:59]'::tsrange;
----
(,"1969-12-31 11:59:59"]

query T
SELECT '[,1970-01-01 00:00:00]'::tsrange;
----
(,"1970-01-01 00:00:00"]

query T
SELECT '[,1970-01-01 00:00:01]'::tsrange;
----
(,"1970-01-01 00:00:01"]

query T
SELECT '[1969-12-31 11:59:59,)'::tsrange;
----
["1969-12-31 11:59:59",)

query T
SELECT '[1969-12-31 11:59:59,1969-12-31 11:59:59)'::tsrange;
----
empty

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:00)'::tsrange;
----
["1969-12-31 11:59:59","1970-01-01 00:00:00")

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:01)'::tsrange;
----
["1969-12-31 11:59:59","1970-01-01 00:00:01")

query T
SELECT '[1969-12-31 11:59:59,]'::tsrange;
----
["1969-12-31 11:59:59",)

query T
SELECT '[1969-12-31 11:59:59,1969-12-31 11:59:59]'::tsrange;
----
["1969-12-31 11:59:59","1969-12-31 11:59:59"]

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:00]'::tsrange;
----
["1969-12-31 11:59:59","1970-01-01 00:00:00"]

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:01]'::tsrange;
----
["1969-12-31 11:59:59","1970-01-01 00:00:01"]

query T
SELECT '[1970-01-01 00:00:00,)'::tsrange;
----
["1970-01-01 00:00:00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:00,1969-12-31 11:59:59)'::tsrange;

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:00)'::tsrange;
----
empty

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:01)'::tsrange;
----
["1970-01-01 00:00:00","1970-01-01 00:00:01")

query T
SELECT '[1970-01-01 00:00:00,]'::tsrange;
----
["1970-01-01 00:00:00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:00,1969-12-31 11:59:59]'::tsrange;

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:00]'::tsrange;
----
["1970-01-01 00:00:00","1970-01-01 00:00:00"]

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:01]'::tsrange;
----
["1970-01-01 00:00:00","1970-01-01 00:00:01"]

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange;
----
["1970-01-01 00:00:01",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1969-12-31 11:59:59)'::tsrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1970-01-01 00:00:00)'::tsrange;

query T
SELECT '[1970-01-01 00:00:01.1, 1970-01-01 00:00:01.1)'::tsrange;
----
empty

query T
SELECT '[1970-01-01 00:00:01,]'::tsrange;
----
["1970-01-01 00:00:01",)

# Range bound errors
query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1969-12-31 11:59:59]'::tsrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1970-01-01 00:00:00]'::tsrange;

# Whitespace handling
query T
SELECT
	DISTINCT column1::tsrange
FROM
	(
		VALUES
			('   empty'),
			('empty  '),
			('  [1970-01-01 00:00:01,)'),
			('[  1970-01-01 00:00:01,)'),
			('[1970-01-01 00:00:01  ,)'),
			('[  1970-01-01 00:00:01  ,)'),
('[1970-01-01 00:00:01,  1970-01-01 00:00:02)'),
			('[1970-01-01 00:00:01,1970-01-01 00:00:02  )'),
			('[1970-01-01 00:00:01,  1970-01-01 00:00:02  )'),
			('[1970-01-01 00:00:01,)  '),
			('  (,1970-01-01 00:00:01)'),
			('(,  1970-01-01 00:00:01)'),
			('  (,)  ')
	) t;
----
empty
(,)
(,"1970-01-01 00:00:01")
["1970-01-01 00:00:01",)
["1970-01-01 00:00:01","1970-01-01 00:00:02")

# Input errors
query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,  )'::tsrange

query error invalid input syntax for type range
SELECT '(  ,1970-01-01 00:00:01)'::tsrange

query error invalid input syntax for type range
SELECT '(  ,  )'::tsrange

query error invalid input syntax for type range
SELECT 'emptyy'::tsrange;

query error invalid input syntax for type range
SELECT ''::tsrange;

query error invalid input syntax for type range
SELECT '1'::tsrange;

query error invalid input syntax for type range
SELECT 'd'::tsrange;

query error invalid input syntax for type range
SELECT ','::tsrange;

query error invalid input syntax for type range
SELECT ')'::tsrange;

query error invalid input syntax for type range
SELECT '{'::tsrange;

query error invalid input syntax for type range
SELECT '('::tsrange;

query error invalid input syntax for type range
SELECT '['::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01)'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01]'::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01,'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,'::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01,1'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,1'::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]1'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]a'::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]]'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]}'::tsrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]  ]'::tsrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]  }'::tsrange;

statement ok
CREATE TABLE tsrange_values (a tsrange);

statement ok
INSERT INTO tsrange_values VALUES
    (null),
    ('empty'),
    ('[,1970-01-01 00:00:01)'::tsrange),
    ('[,1970-01-01 00:00:01]'::tsrange),
    ('[,)'::tsrange),
    ('[,]'::tsrange),
    ('(,1970-01-01 00:00:01)'::tsrange),
    ('(,1970-01-01 00:00:01]'::tsrange),
    ('(,)'::tsrange),
    ('(,]'::tsrange),
    ('[1969-12-31 11:59:59,1970-01-01 00:00:01)'::tsrange),
    ('[1969-12-31 11:59:59,1970-01-01 00:00:01]'::tsrange),
    ('(1969-12-31 11:59:59,1970-01-01 00:00:01)'::tsrange),
    ('(1969-12-31 11:59:59,1970-01-01 00:00:01]'::tsrange),
    ('[1970-01-01 00:00:00,1970-01-01 00:00:00)'::tsrange),
    ('[1970-01-01 00:00:00,1970-01-01 00:00:00]'::tsrange),
    ('(1970-01-01 00:00:00,1970-01-01 00:00:00)'::tsrange),
    ('(1970-01-01 00:00:00,1970-01-01 00:00:00]'::tsrange),
    ('[1970-01-01 00:00:01,)'::tsrange),
    ('[1970-01-01 00:00:01,]'::tsrange),
    ('(1970-01-01 00:00:01,)'::tsrange),
    ('(1970-01-01 00:00:01,]'::tsrange);

query T
SELECT a AS t FROM tsrange_values ORDER BY a;
----
empty
empty
empty
empty
(,"1970-01-01 00:00:01")
(,"1970-01-01 00:00:01")
(,"1970-01-01 00:00:01"]
(,"1970-01-01 00:00:01"]
(,)
(,)
(,)
(,)
["1969-12-31 11:59:59","1970-01-01 00:00:01")
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
("1969-12-31 11:59:59","1970-01-01 00:00:01")
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
["1970-01-01 00:00:01",)
["1970-01-01 00:00:01",)
("1970-01-01 00:00:01",)
("1970-01-01 00:00:01",)
NULL

query T
SELECT a AS t FROM tsrange_values EXCEPT SELECT column1 FROM (VALUES
    (tsrange(null,'1970-01-01 00:00:01')),
    (tsrange(null,'1970-01-01 00:00:01', '[]')),
    (tsrange(null,null)),
    (tsrange(null,null, '[]')),
    (tsrange(null,'1970-01-01 00:00:01', '()')),
    (tsrange(null,'1970-01-01 00:00:01', '(]')),
    (tsrange(null,null, '()')),
    (tsrange(null,null,'(]')),
    (tsrange('1969-12-31 11:59:59','1970-01-01 00:00:01')),
    (tsrange('1969-12-31 11:59:59','1970-01-01 00:00:01', '[]')),
    (tsrange('1969-12-31 11:59:59','1970-01-01 00:00:01', '()')),
    (tsrange('1969-12-31 11:59:59','1970-01-01 00:00:01','(]')),
    (tsrange('1970-01-01 00:00:00','1970-01-01 00:00:00')),
    (tsrange('1970-01-01 00:00:00','1970-01-01 00:00:00', '[]')),
    (tsrange('1970-01-01 00:00:00','1970-01-01 00:00:00','()')),
    (tsrange('1970-01-01 00:00:00','1970-01-01 00:00:00','(]')),
    (tsrange('1970-01-01 00:00:01',null)),
    (tsrange('1970-01-01 00:00:01',null, '[]')),
    (tsrange('1970-01-01 00:00:01',null,'()')),
    (tsrange('1970-01-01 00:00:01',null,'(]'))
) t;
----
NULL

query error range constructor flags argument must not be null
SELECT tsrange('1970-01-01 00:00:01','1970-01-01 00:00:01',null);

query error range constructor flags argument must not be null
SELECT tsrange(null,null,null);


#
# tsrange upper, lower
query TT
SELECT DISTINCT lower(a), upper(a) FROM tsrange_values ORDER BY 1, 2;
----
1969-12-31 11:59:59
1970-01-01 00:00:01
1970-01-01 00:00:00
1970-01-01 00:00:00
1970-01-01 00:00:01
NULL
NULL
1970-01-01 00:00:01
NULL
NULL

# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	tsrange_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= tsrange(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM tsrange_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

query error invalid range bound flags
SELECT tsrange(null,null,' (]');

query error invalid range bound flags
SELECT tsrange(null,null,'(] ');

query error invalid range bound flags
SELECT tsrange(null,null,'( ]');

query error invalid range bound flags
SELECT tsrange(null,null,'(,]');

query error invalid range bound flags
SELECT tsrange(null,null,'a()');

query error invalid range bound flags
SELECT tsrange(null,null,'(a)');

query error invalid range bound flags
SELECT tsrange(null,null,'()a');

query error invalid range bound flags
SELECT tsrange(null,null,'(()');

query error invalid range bound flags
SELECT tsrange(null,null,'())');

# Test range in list
query T
SELECT LIST['(,)', 'empty', '[1969-12-31 11:59:59,1970-01-01 00:00:01]']::tsrange list::text;
----
{"(,)",empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\"]"}

query T
SELECT '{"(,)","empty","[1969-12-31 11:59:59,1970-01-01 00:00:01]"}'::tsrange list::text;
----
{"(,)",empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\"]"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[1969-12-31 11:59:59,1970-01-01 00:00:01]']::_tsrange;
----
{"(,)",empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\"]"}

query T
SELECT '{"(,)","empty","[1969-12-31 11:59:59,1970-01-01 00:00:01]"}'::_tsrange;
----
{"(,)",empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\"]"}

# Test builtin functions

query B
select '(,)'::tsrange = '(,)'::tsrange;
----
true

query B
select '(,)'::tsrange != 'empty'::tsrange;
----
true

query B
select '(,)'::tsrange > 'empty'::tsrange;
----
true

query B
select '(,)'::tsrange >= 'empty'::tsrange;
----
true

query B
select '(,)'::tsrange < 'empty'::tsrange;
----
false

query B
select '(,)'::tsrange <= 'empty'::tsrange;
----
false

statement ok
CREATE TABLE tsrange_test_values (v tsrange);

statement ok
INSERT INTO tsrange_test_values VALUES
('empty'),
('(,)'),
('(,1970-01-01 00:00:01)'),
('(1969-12-31 11:59:59,)'),
('[1969-12-31 11:59:59,1970-01-01 00:00:01)'),
('[1969-01-01 00:00:00,1969-06-01 00:00:00)'),
('[1971-01-01 00:00:00,1971-06-01 00:00:00)');

#
# tsrange contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,"1970-01-01 00:00:01")
{empty,"(,\"1970-01-01 00:00:01\")","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
(,"1970-01-01 00:00:01"]
{empty,"(,\"1970-01-01 00:00:01\")","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
(,)
{empty,"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{empty,"[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{empty}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{empty}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{empty}
["1970-01-01 00:00:01",)
{empty,"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1970-01-01 00:00:01",)
{empty,"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}

#
# tsrange contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
(,"1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)"}
(,"1970-01-01 00:00:01"]
{"(,)"}
(,)
{"(,)"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,)"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,)","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)"}
("1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)"}

#
# tsrange overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
(,"1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
(,)
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}

#
# tsrange before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01")
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
(,"1970-01-01 00:00:01"]
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}

#
# tsrange after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")"}
["1970-01-01 00:00:01",)
{"(,\"1970-01-01 00:00:01\")","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
("1970-01-01 00:00:01",)
{"(,\"1970-01-01 00:00:01\")","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}

#
# tsrange overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
(,"1970-01-01 00:00:01"]
{"(,)","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
(,)
{"(,)","(\"1969-12-31 11:59:59\",)"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,)","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,)","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)","[\"1971-01-01 00:00:00\",\"1971-06-01 00:00:00\")"}
["1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)"}
("1970-01-01 00:00:01",)
{"(,)","(\"1969-12-31 11:59:59\",)"}

#
# tsrange overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)"}
(,"1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)"}
(,)
{"(,\"1970-01-01 00:00:01\")","(,)"}
["1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
["1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}
("1969-12-31 11:59:59","1970-01-01 00:00:01")
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
("1969-12-31 11:59:59","1970-01-01 00:00:01"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:00","1970-01-01 00:00:00"]
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
["1970-01-01 00:00:01",)
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}
("1970-01-01 00:00:01",)
{"(,\"1970-01-01 00:00:01\")","(,)","[\"1969-01-01 00:00:00\",\"1969-06-01 00:00:00\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")","(\"1969-12-31 11:59:59\",)"}

#
# tsrange adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tsrange_values WHERE a IS NOT NULL
) tsrange_values(a), tsrange_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
["1970-01-01 00:00:01",)
{"(,\"1970-01-01 00:00:01\")","[\"1969-12-31 11:59:59\",\"1970-01-01 00:00:01\")"}


query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:01.1]'::tsrange;
----
["1970-01-01 00:00:01.1","1970-01-01 00:00:01.1"]

query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:02]'::tsrange;
----
["1970-01-01 00:00:01.1","1970-01-01 00:00:02"]

query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:01.1)'::tsrange;
----
empty

query T
SELECT tsrange('1970-01-01 00:00:01.1','1970-01-01 00:00:01.2');
----
["1970-01-01 00:00:01.1","1970-01-01 00:00:01.2")

query T
SELECT tsrange('1969-12-31 11:59:59.1','1970-01-01 00:00:01.2');
----
["1969-12-31 11:59:59.1","1970-01-01 00:00:01.2")

#
# tsrange union
query T
SELECT 'empty'::tsrange + 'empty'::tsrange
----
empty

query T
SELECT 'empty'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT 'empty'::tsrange + '(,1969-12-31 11:59:59)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT 'empty'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT 'empty'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT 'empty'::tsrange + '[1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT 'empty'::tsrange + '(1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT 'empty'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + 'empty'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + '(,1969-12-31 11:59:59)'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
(,"1970-01-01 00:00:02")

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
(,"1970-01-01 00:00:02"]

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59]'::tsrange + '[1970-01-01 00:00:01,)'::tsrange

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59]'::tsrange + '(1970-01-01 00:00:01,)'::tsrange

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange + 'empty'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
(,"1970-01-01 00:00:02")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
(,"1970-01-01 00:00:02"]

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59)'::tsrange + '[1970-01-01 00:00:01,)'::tsrange

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59)'::tsrange + '(1970-01-01 00:00:01,)'::tsrange

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + 'empty'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,"1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '(,1969-12-31 11:59:59)'::tsrange
----
(,"1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '[1970-01-01 00:00:01,)'::tsrange
----
("1969-12-31 11:59:58",)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '(1970-01-01 00:00:01,)'::tsrange
----
("1969-12-31 11:59:58",)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + 'empty'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,"1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '(,1969-12-31 11:59:59)'::tsrange
----
(,"1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '[1970-01-01 00:00:01,)'::tsrange
----
["1969-12-31 11:59:58",)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '(1970-01-01 00:00:01,)'::tsrange
----
["1969-12-31 11:59:58",)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange + 'empty'::tsrange
----
["1970-01-01 00:00:01",)

query error result of range union would not be contiguous
SELECT '[1970-01-01 00:00:01,)'::tsrange + '(,1969-12-31 11:59:59]'::tsrange

query error result of range union would not be contiguous
SELECT '[1970-01-01 00:00:01,)'::tsrange + '(,1969-12-31 11:59:59)'::tsrange

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange + '(1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange + 'empty'::tsrange
----
("1970-01-01 00:00:01",)

query error result of range union would not be contiguous
SELECT '(1970-01-01 00:00:01,)'::tsrange + '(,1969-12-31 11:59:59]'::tsrange

query error result of range union would not be contiguous
SELECT '(1970-01-01 00:00:01,)'::tsrange + '(,1969-12-31 11:59:59)'::tsrange

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange + '[1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange + '(,)'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + 'empty'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '(,1969-12-31 11:59:59]'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '(,1969-12-31 11:59:59)'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '[1970-01-01 00:00:01,)'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange + '(1970-01-01 00:00:01,)'::tsrange
----
(,)

#
# tsrange intersection

query T
SELECT 'empty'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT 'empty'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange * '(,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58","1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58","1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange * '(,)'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58","1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58","1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange * '(,)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
("1969-12-31 11:59:58","1969-12-31 11:59:59"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
("1969-12-31 11:59:58","1969-12-31 11:59:59")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange * '(,)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
["1969-12-31 11:59:58","1969-12-31 11:59:59"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
["1969-12-31 11:59:58","1969-12-31 11:59:59")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange * '(,)'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
["1970-01-01 00:00:01","1970-01-01 00:00:02")

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1970-01-01 00:00:01","1970-01-01 00:00:02"]

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange * '(,)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1970-01-01 00:00:01","1970-01-01 00:00:02")

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
("1970-01-01 00:00:01","1970-01-01 00:00:02"]

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange * '(,)'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '(,)'::tsrange * 'empty'::tsrange
----
empty

query T
SELECT '(,)'::tsrange * '(,1969-12-31 11:59:59]'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,)'::tsrange * '(,1969-12-31 11:59:59)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,)'::tsrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '(,)'::tsrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '(,)'::tsrange * '[1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '(,)'::tsrange * '(1970-01-01 00:00:01,)'::tsrange
----
("1970-01-01 00:00:01",)

#
# tsrange difference

query T
SELECT 'empty'::tsrange - 'empty'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT 'empty'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - 'empty'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
["1969-12-31 11:59:59","1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
(,"1969-12-31 11:59:58"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
(,"1969-12-31 11:59:58")

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
(,"1969-12-31 11:59:59"]

query T
SELECT '(,1969-12-31 11:59:59]'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - 'empty'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
(,"1969-12-31 11:59:58"]

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
(,"1969-12-31 11:59:58")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
(,"1969-12-31 11:59:59")

query T
SELECT '(,1969-12-31 11:59:59)'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - 'empty'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
("1969-12-31 11:59:59","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
["1969-12-31 11:59:59","1970-01-01 00:00:02")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:01")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
("1969-12-31 11:59:58","1970-01-01 00:00:01"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - 'empty'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
("1969-12-31 11:59:59","1970-01-01 00:00:02"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
["1969-12-31 11:59:59","1970-01-01 00:00:02"]

query error result of range difference would not be contiguous
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:01")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
["1969-12-31 11:59:58","1970-01-01 00:00:01"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - 'empty'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
["1970-01-01 00:00:01",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
["1970-01-01 00:00:02",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
("1970-01-01 00:00:02",)

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
["1970-01-01 00:00:01","1970-01-01 00:00:01"]

query T
SELECT '[1970-01-01 00:00:01,)'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - 'empty'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
("1970-01-01 00:00:01",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange
----
["1970-01-01 00:00:02",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange
----
("1970-01-01 00:00:02",)

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tsrange - '(,)'::tsrange
----
empty

query T
SELECT '(,)'::tsrange - 'empty'::tsrange
----
(,)

query T
SELECT '(,)'::tsrange - '(,1969-12-31 11:59:59]'::tsrange
----
("1969-12-31 11:59:59",)

query T
SELECT '(,)'::tsrange - '(,1969-12-31 11:59:59)'::tsrange
----
["1969-12-31 11:59:59",)

query error result of range difference would not be contiguous
SELECT '(,)'::tsrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tsrange

query error result of range difference would not be contiguous
SELECT '(,)'::tsrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tsrange

query T
SELECT '(,)'::tsrange - '[1970-01-01 00:00:01,)'::tsrange
----
(,"1970-01-01 00:00:01")

query T
SELECT '(,)'::tsrange - '(1970-01-01 00:00:01,)'::tsrange
----
(,"1970-01-01 00:00:01"]


#
# tstzrange

query T
select '[1970-01-01 00:00:00,1971-01-01 00:00:00)'::tstzrange;
----
["1970-01-01 00:00:00+00","1971-01-01 00:00:00+00")

query T
select pg_typeof('[1970-01-01 00:00:00,1971-01-01 00:00:00)'::tstzrange);
----
tstzrange

query T
select 'empty'::tstzrange;
----
empty

query T
select pg_typeof('empty'::tstzrange);
----
tstzrange

query T
select null::tstzrange
----
NULL

query T
SELECT '(,)'::tstzrange;
----
(,)

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange;
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1970-01-01 00:00:00)'::tstzrange;
----
(,"1970-01-01 00:00:00+00")

query T
SELECT '(,1970-01-01 00:00:01)'::tstzrange;
----
(,"1970-01-01 00:00:01+00")

query T
SELECT '(,]'::tstzrange;
----
(,)

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange;
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1970-01-01 00:00:00]'::tstzrange;
----
(,"1970-01-01 00:00:00+00"]

query T
SELECT '(,1970-01-01 00:00:01]'::tstzrange;
----
(,"1970-01-01 00:00:01+00"]

query T
SELECT '(1969-12-31 11:59:59,)'::tstzrange;
----
("1969-12-31 11:59:59+00",)

query T
SELECT '(1969-12-31 11:59:59,1969-12-31 11:59:59)'::tstzrange;
----
empty

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:00)'::tstzrange;
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:00+00")

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:01)'::tstzrange;
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")

query T
SELECT '(1969-12-31 11:59:59,]'::tstzrange;
----
("1969-12-31 11:59:59+00",)

query T
SELECT '(1969-12-31 11:59:59,1969-12-31 11:59:59]'::tstzrange;
----
empty

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:00]'::tstzrange;
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:00+00"]

query T
SELECT '(1969-12-31 11:59:59,1970-01-01 00:00:01]'::tstzrange;
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]

query T
SELECT '(1970-01-01 00:00:00,)'::tstzrange;
----
("1970-01-01 00:00:00+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:00,1969-12-31 11:59:59)'::tstzrange;

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:00)'::tstzrange;
----
empty

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:01)'::tstzrange;
----
("1970-01-01 00:00:00+00","1970-01-01 00:00:01+00")

query T
SELECT '(1970-01-01 00:00:00,]'::tstzrange;
----
("1970-01-01 00:00:00+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:00,1969-12-31 11:59:59]'::tstzrange;

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:00]'::tstzrange;
----
empty

query T
SELECT '(1970-01-01 00:00:00,1970-01-01 00:00:01]'::tstzrange;
----
("1970-01-01 00:00:00+00","1970-01-01 00:00:01+00"]

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange;
----
("1970-01-01 00:00:01+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1969-12-31 11:59:59)'::tstzrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1970-01-01 00:00:00)'::tstzrange;

query T
SELECT '(1970-01-01 00:00:01.1,1970-01-01 00:00:01.1)'::tstzrange;
----
empty

query T
SELECT '(1970-01-01 00:00:01,]'::tstzrange;
----
("1970-01-01 00:00:01+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1969-12-31 11:59:59]'::tstzrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '(1970-01-01 00:00:01,1970-01-01 00:00:00]'::tstzrange;

query T
SELECT '(1970-01-01 00:00:01.1, 1970-01-01 00:00:01.1]'::tstzrange;
----
empty

query T
SELECT '[,)'::tstzrange;
----
(,)

query T
SELECT '[,1969-12-31 11:59:59)'::tstzrange;
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '[,1970-01-01 00:00:00)'::tstzrange;
----
(,"1970-01-01 00:00:00+00")

query T
SELECT '[,1970-01-01 00:00:01)'::tstzrange;
----
(,"1970-01-01 00:00:01+00")

query T
SELECT '[,]'::tstzrange;
----
(,)

query T
SELECT '[,1969-12-31 11:59:59]'::tstzrange;
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '[,1970-01-01 00:00:00]'::tstzrange;
----
(,"1970-01-01 00:00:00+00"]

query T
SELECT '[,1970-01-01 00:00:01]'::tstzrange;
----
(,"1970-01-01 00:00:01+00"]

query T
SELECT '[1969-12-31 11:59:59,)'::tstzrange;
----
["1969-12-31 11:59:59+00",)

query T
SELECT '[1969-12-31 11:59:59,1969-12-31 11:59:59)'::tstzrange;
----
empty

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:00)'::tstzrange;
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:00+00")

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:01)'::tstzrange;
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")

query T
SELECT '[1969-12-31 11:59:59,]'::tstzrange;
----
["1969-12-31 11:59:59+00",)

query T
SELECT '[1969-12-31 11:59:59,1969-12-31 11:59:59]'::tstzrange;
----
["1969-12-31 11:59:59+00","1969-12-31 11:59:59+00"]

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:00]'::tstzrange;
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:00+00"]

query T
SELECT '[1969-12-31 11:59:59,1970-01-01 00:00:01]'::tstzrange;
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]

query T
SELECT '[1970-01-01 00:00:00,)'::tstzrange;
----
["1970-01-01 00:00:00+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:00,1969-12-31 11:59:59)'::tstzrange;

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:00)'::tstzrange;
----
empty

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:01)'::tstzrange;
----
["1970-01-01 00:00:00+00","1970-01-01 00:00:01+00")

query T
SELECT '[1970-01-01 00:00:00,]'::tstzrange;
----
["1970-01-01 00:00:00+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:00,1969-12-31 11:59:59]'::tstzrange;

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:00]'::tstzrange;
----
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]

query T
SELECT '[1970-01-01 00:00:00,1970-01-01 00:00:01]'::tstzrange;
----
["1970-01-01 00:00:00+00","1970-01-01 00:00:01+00"]

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange;
----
["1970-01-01 00:00:01+00",)

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1969-12-31 11:59:59)'::tstzrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1970-01-01 00:00:00)'::tstzrange;

query T
SELECT '[1970-01-01 00:00:01.1, 1970-01-01 00:00:01.1)'::tstzrange;
----
empty

query T
SELECT '[1970-01-01 00:00:01,]'::tstzrange;
----
["1970-01-01 00:00:01+00",)

query T
SELECT '["2024-02-13 17:01:58.37848+00",)'::tstzrange;
----
["2024-02-13 17:01:58.37848+00",)

query T
SELECT '["?!?2024-02-13 17:01:58.37848+00!?!",)'::tstzrange;
----
["2024-02-13 17:01:58.37848+00",)

# Range bound errors
query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1969-12-31 11:59:59]'::tstzrange;

query error range lower bound must be less than or equal to range upper bound
SELECT '[1970-01-01 00:00:01,1970-01-01 00:00:00]'::tstzrange;

# Whitespace handling
query T
SELECT
	DISTINCT column1::tstzrange
FROM
	(
		VALUES
			('   empty'),
			('empty  '),
			('  [1970-01-01 00:00:01,)'),
			('[  1970-01-01 00:00:01,)'),
			('[1970-01-01 00:00:01  ,)'),
			('[  1970-01-01 00:00:01  ,)'),
('[1970-01-01 00:00:01,  1970-01-01 00:00:02)'),
			('[1970-01-01 00:00:01,1970-01-01 00:00:02  )'),
			('[1970-01-01 00:00:01,  1970-01-01 00:00:02  )'),
			('[1970-01-01 00:00:01,)  '),
			('  (,1970-01-01 00:00:01)'),
			('(,  1970-01-01 00:00:01)'),
			('  (,)  ')
	) t;
----
empty
(,)
(,"1970-01-01 00:00:01+00")
["1970-01-01 00:00:01+00",)
["1970-01-01 00:00:01+00","1970-01-01 00:00:02+00")

# Input errors
query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,  )'::tstzrange

query error invalid input syntax for type range
SELECT '(  ,1970-01-01 00:00:01)'::tstzrange

query error invalid input syntax for type range
SELECT '(  ,  )'::tstzrange

query error invalid input syntax for type range
SELECT 'emptyy'::tstzrange;

query error invalid input syntax for type range
SELECT ''::tstzrange;

query error invalid input syntax for type range
SELECT '1'::tstzrange;

query error invalid input syntax for type range
SELECT 'd'::tstzrange;

query error invalid input syntax for type range
SELECT ','::tstzrange;

query error invalid input syntax for type range
SELECT ')'::tstzrange;

query error invalid input syntax for type range
SELECT '{'::tstzrange;

query error invalid input syntax for type range
SELECT '('::tstzrange;

query error invalid input syntax for type range
SELECT '['::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01)'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01]'::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01,'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,'::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01,1'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01,1'::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]1'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]a'::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]]'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]}'::tstzrange;

query error invalid input syntax for type range
SELECT '(1970-01-01 00:00:01970-01-01 00:00:01.1]  ]'::tstzrange;

query error invalid input syntax for type range
SELECT '[1970-01-01 00:00:01970-01-01 00:00:01.1]  }'::tstzrange;

statement ok
CREATE TABLE tstzrange_values (a tstzrange);

statement ok
INSERT INTO tstzrange_values VALUES
    (null),
    ('empty'),
    ('[,1970-01-01 00:00:01)'::tstzrange),
    ('[,1970-01-01 00:00:01]'::tstzrange),
    ('[,)'::tstzrange),
    ('[,]'::tstzrange),
    ('(,1970-01-01 00:00:01)'::tstzrange),
    ('(,1970-01-01 00:00:01]'::tstzrange),
    ('(,)'::tstzrange),
    ('(,]'::tstzrange),
    ('[1969-12-31 11:59:59,1970-01-01 00:00:01)'::tstzrange),
    ('[1969-12-31 11:59:59,1970-01-01 00:00:01]'::tstzrange),
    ('(1969-12-31 11:59:59,1970-01-01 00:00:01)'::tstzrange),
    ('(1969-12-31 11:59:59,1970-01-01 00:00:01]'::tstzrange),
    ('[1970-01-01 00:00:00,1970-01-01 00:00:00)'::tstzrange),
    ('[1970-01-01 00:00:00,1970-01-01 00:00:00]'::tstzrange),
    ('(1970-01-01 00:00:00,1970-01-01 00:00:00)'::tstzrange),
    ('(1970-01-01 00:00:00,1970-01-01 00:00:00]'::tstzrange),
    ('[1970-01-01 00:00:01,)'::tstzrange),
    ('[1970-01-01 00:00:01,]'::tstzrange),
    ('(1970-01-01 00:00:01,)'::tstzrange),
    ('(1970-01-01 00:00:01,]'::tstzrange);

query T
SELECT a AS t FROM tstzrange_values ORDER BY a;
----
empty
empty
empty
empty
(,"1970-01-01 00:00:01+00")
(,"1970-01-01 00:00:01+00")
(,"1970-01-01 00:00:01+00"]
(,"1970-01-01 00:00:01+00"]
(,)
(,)
(,)
(,)
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
["1970-01-01 00:00:01+00",)
["1970-01-01 00:00:01+00",)
("1970-01-01 00:00:01+00",)
("1970-01-01 00:00:01+00",)
NULL

query T
SELECT a AS t FROM tstzrange_values EXCEPT SELECT column1 FROM (VALUES
    (tstzrange(null,'1970-01-01 00:00:01')),
    (tstzrange(null,'1970-01-01 00:00:01', '[]')),
    (tstzrange(null,null)),
    (tstzrange(null,null, '[]')),
    (tstzrange(null,'1970-01-01 00:00:01', '()')),
    (tstzrange(null,'1970-01-01 00:00:01', '(]')),
    (tstzrange(null,null, '()')),
    (tstzrange(null,null,'(]')),
    (tstzrange('1969-12-31 11:59:59','1970-01-01 00:00:01')),
    (tstzrange('1969-12-31 11:59:59','1970-01-01 00:00:01', '[]')),
    (tstzrange('1969-12-31 11:59:59','1970-01-01 00:00:01', '()')),
    (tstzrange('1969-12-31 11:59:59','1970-01-01 00:00:01','(]')),
    (tstzrange('1970-01-01 00:00:00','1970-01-01 00:00:00')),
    (tstzrange('1970-01-01 00:00:00','1970-01-01 00:00:00', '[]')),
    (tstzrange('1970-01-01 00:00:00','1970-01-01 00:00:00','()')),
    (tstzrange('1970-01-01 00:00:00','1970-01-01 00:00:00','(]')),
    (tstzrange('1970-01-01 00:00:01',null)),
    (tstzrange('1970-01-01 00:00:01',null, '[]')),
    (tstzrange('1970-01-01 00:00:01',null,'()')),
    (tstzrange('1970-01-01 00:00:01',null,'(]'))
) t;
----
NULL

query error range constructor flags argument must not be null
SELECT tstzrange('1970-01-01 00:00:01','1970-01-01 00:00:01',null);

query error range constructor flags argument must not be null
SELECT tstzrange(null,null,null);

#
# tstzrange upper, lower
query TT
SELECT DISTINCT lower(a), upper(a) FROM tstzrange_values ORDER BY 1, 2;
----
1969-12-31 11:59:59+00
1970-01-01 00:00:01+00
1970-01-01 00:00:00+00
1970-01-01 00:00:00+00
1970-01-01 00:00:01+00
NULL
NULL
1970-01-01 00:00:01+00
NULL
NULL

# test that lower and upper roundtrip through range constructor function
query T
SELECT
	a
FROM
	tstzrange_values
WHERE
	(NOT isempty(a))
	AND (
			a
			!= tstzrange(
					lower(a),
					upper(a),
					CASE
					WHEN lower_inc(a) THEN '['
					ELSE '('
					END
					|| CASE WHEN upper_inc(a) THEN ']' ELSE ')' END
				)
		);
----


query T
SELECT DISTINCT a FROM tstzrange_values WHERE lower_inf(a) AND upper_inf(a);
----
(,)

query error invalid range bound flags
SELECT tstzrange(null,null,' (]');

query error invalid range bound flags
SELECT tstzrange(null,null,'(] ');

query error invalid range bound flags
SELECT tstzrange(null,null,'( ]');

query error invalid range bound flags
SELECT tstzrange(null,null,'(,]');

query error invalid range bound flags
SELECT tstzrange(null,null,'a()');

query error invalid range bound flags
SELECT tstzrange(null,null,'(a)');

query error invalid range bound flags
SELECT tstzrange(null,null,'()a');

query error invalid range bound flags
SELECT tstzrange(null,null,'(()');

query error invalid range bound flags
SELECT tstzrange(null,null,'())');

# Test range in list
query T
SELECT LIST['(,)', 'empty', '[1969-12-31 11:59:59,1970-01-01 00:00:01]']::tstzrange list::text;
----
{"(,)",empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\"]"}

query T
SELECT '{"(,)","empty","[1969-12-31 11:59:59,1970-01-01 00:00:01]"}'::tstzrange list::text;
----
{"(,)",empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\"]"}

# Test range in array
query T
SELECT ARRAY['(,)', 'empty', '[1969-12-31 11:59:59,1970-01-01 00:00:01]']::_tstzrange;
----
{"(,)",empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\"]"}

query T
SELECT '{"(,)","empty","[1969-12-31 11:59:59,1970-01-01 00:00:01]"}'::_tstzrange;
----
{"(,)",empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\"]"}

# Test builtin functions

query B
select '(,)'::tstzrange = '(,)'::tstzrange;
----
true

query B
select '(,)'::tstzrange != 'empty'::tstzrange;
----
true

query B
select '(,)'::tstzrange > 'empty'::tstzrange;
----
true

query B
select '(,)'::tstzrange >= 'empty'::tstzrange;
----
true

query B
select '(,)'::tstzrange < 'empty'::tstzrange;
----
false

query B
select '(,)'::tstzrange <= 'empty'::tstzrange;
----
false

statement ok
CREATE TABLE tstzrange_test_values (v tstzrange);

statement ok
INSERT INTO tstzrange_test_values VALUES
('empty'),
('(,)'),
('(,1970-01-01 00:00:01)'),
('(1969-12-31 11:59:59,)'),
('[1969-12-31 11:59:59,1970-01-01 00:00:01)'),
('[1969-01-01 00:00:00,1969-06-01 00:00:00)'),
('[1971-01-01 00:00:00,1971-06-01 00:00:00)');

#
# tstzrange contains
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a @> v GROUP BY a ORDER BY a;
----
empty
{empty}
(,"1970-01-01 00:00:01+00")
{empty,"(,\"1970-01-01 00:00:01+00\")","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
(,"1970-01-01 00:00:01+00"]
{empty,"(,\"1970-01-01 00:00:01+00\")","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
(,)
{empty,"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{empty,"[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{empty}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{empty}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{empty}
["1970-01-01 00:00:01+00",)
{empty,"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1970-01-01 00:00:01+00",)
{empty,"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}

#
# tstzrange contained by
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a <@ v GROUP BY a ORDER BY a;
----
empty
{empty,"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
(,"1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)"}
(,"1970-01-01 00:00:01+00"]
{"(,)"}
(,)
{"(,)"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,)"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,)","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)"}
("1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)"}

#
# tstzrange overlaps
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a && v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
(,"1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
(,)
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}

#
# tstzrange before
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a << v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01+00")
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
(,"1970-01-01 00:00:01+00"]
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}

#
# tstzrange after
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a >> v GROUP BY a ORDER BY a;
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")"}
["1970-01-01 00:00:01+00",)
{"(,\"1970-01-01 00:00:01+00\")","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
("1970-01-01 00:00:01+00",)
{"(,\"1970-01-01 00:00:01+00\")","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}

#
# tstzrange overleft
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a &< v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
(,"1970-01-01 00:00:01+00"]
{"(,)","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
(,)
{"(,)","(\"1969-12-31 11:59:59+00\",)"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,)","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,)","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)","[\"1971-01-01 00:00:00+00\",\"1971-06-01 00:00:00+00\")"}
["1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)"}
("1970-01-01 00:00:01+00",)
{"(,)","(\"1969-12-31 11:59:59+00\",)"}

#
# tstzrange overright
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a &> v GROUP BY a ORDER BY a;
----
(,"1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)"}
(,"1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)"}
(,)
{"(,\"1970-01-01 00:00:01+00\")","(,)"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
["1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00")
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
("1969-12-31 11:59:59+00","1970-01-01 00:00:01+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:00+00","1970-01-01 00:00:00+00"]
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
["1970-01-01 00:00:01+00",)
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}
("1970-01-01 00:00:01+00",)
{"(,\"1970-01-01 00:00:01+00\")","(,)","[\"1969-01-01 00:00:00+00\",\"1969-06-01 00:00:00+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")","(\"1969-12-31 11:59:59+00\",)"}

#
# tstzrange adjacent
query TT
SELECT a t, array_agg(v ORDER BY v) FROM (
    SELECT DISTINCT a FROM tstzrange_values WHERE a IS NOT NULL
) tstzrange_values(a), tstzrange_test_values WHERE a -|- v GROUP BY a ORDER BY a;
----
["1970-01-01 00:00:01+00",)
{"(,\"1970-01-01 00:00:01+00\")","[\"1969-12-31 11:59:59+00\",\"1970-01-01 00:00:01+00\")"}


query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:01.1]'::tstzrange;
----
["1970-01-01 00:00:01.1+00","1970-01-01 00:00:01.1+00"]

query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:02]'::tstzrange;
----
["1970-01-01 00:00:01.1+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1970-01-01 00:00:01.1,1970-01-01 00:00:01.1)'::tstzrange;
----
empty

query T
SELECT tstzrange('1970-01-01 00:00:01.1','1970-01-01 00:00:01.2');
----
["1970-01-01 00:00:01.1+00","1970-01-01 00:00:01.2+00")

query T
SELECT tstzrange('1969-12-31 11:59:59.1','1970-01-01 00:00:01.2');
----
["1969-12-31 11:59:59.1+00","1970-01-01 00:00:01.2+00")

#
# tstzrange union
query T
SELECT 'empty'::tstzrange + 'empty'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT 'empty'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT 'empty'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT 'empty'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT 'empty'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT 'empty'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT 'empty'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + 'empty'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
(,"1970-01-01 00:00:02+00")

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
(,"1970-01-01 00:00:02+00"]

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange + 'empty'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
(,"1970-01-01 00:00:02+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
(,"1970-01-01 00:00:02+00"]

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange

query error result of range union would not be contiguous
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + 'empty'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange
----
("1969-12-31 11:59:58+00",)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange
----
("1969-12-31 11:59:58+00",)

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + 'empty'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange
----
["1969-12-31 11:59:58+00",)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange
----
["1969-12-31 11:59:58+00",)

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange + 'empty'::tstzrange
----
["1970-01-01 00:00:01+00",)

query error result of range union would not be contiguous
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange

query error result of range union would not be contiguous
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange + 'empty'::tstzrange
----
("1970-01-01 00:00:01+00",)

query error result of range union would not be contiguous
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange

query error result of range union would not be contiguous
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange + '(,)'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + 'empty'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '(,1969-12-31 11:59:59]'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '(,1969-12-31 11:59:59)'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '[1970-01-01 00:00:01,)'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange + '(1970-01-01 00:00:01,)'::tstzrange
----
(,)

#
# tstzrange intersection

query T
SELECT 'empty'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT 'empty'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange * '(,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00","1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00","1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange * '(,)'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00","1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00","1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange * '(,)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
("1969-12-31 11:59:58+00","1969-12-31 11:59:59+00"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
("1969-12-31 11:59:58+00","1969-12-31 11:59:59+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange * '(,)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
["1969-12-31 11:59:58+00","1969-12-31 11:59:59+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
["1969-12-31 11:59:58+00","1969-12-31 11:59:59+00")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange * '(,)'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
["1970-01-01 00:00:01+00","1970-01-01 00:00:02+00")

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1970-01-01 00:00:01+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange * '(,)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1970-01-01 00:00:01+00","1970-01-01 00:00:02+00")

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
("1970-01-01 00:00:01+00","1970-01-01 00:00:02+00"]

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange * '(,)'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '(,)'::tstzrange * 'empty'::tstzrange
----
empty

query T
SELECT '(,)'::tstzrange * '(,1969-12-31 11:59:59]'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,)'::tstzrange * '(,1969-12-31 11:59:59)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,)'::tstzrange * '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '(,)'::tstzrange * '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '(,)'::tstzrange * '[1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '(,)'::tstzrange * '(1970-01-01 00:00:01,)'::tstzrange
----
("1970-01-01 00:00:01+00",)

#
# tstzrange difference

query T
SELECT 'empty'::tstzrange - 'empty'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT 'empty'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - 'empty'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
["1969-12-31 11:59:59+00","1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
(,"1969-12-31 11:59:58+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
(,"1969-12-31 11:59:58+00")

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
(,"1969-12-31 11:59:59+00"]

query T
SELECT '(,1969-12-31 11:59:59]'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - 'empty'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
empty

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
(,"1969-12-31 11:59:58+00"]

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
(,"1969-12-31 11:59:58+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
(,"1969-12-31 11:59:59+00")

query T
SELECT '(,1969-12-31 11:59:59)'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - 'empty'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:02+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
empty

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:01+00")

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
("1969-12-31 11:59:58+00","1970-01-01 00:00:01+00"]

query T
SELECT '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - 'empty'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
("1969-12-31 11:59:59+00","1970-01-01 00:00:02+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
["1969-12-31 11:59:59+00","1970-01-01 00:00:02+00"]

query error result of range difference would not be contiguous
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:01+00")

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
["1969-12-31 11:59:58+00","1970-01-01 00:00:01+00"]

query T
SELECT '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - 'empty'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
["1970-01-01 00:00:01+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
["1970-01-01 00:00:02+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
("1970-01-01 00:00:02+00",)

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
["1970-01-01 00:00:01+00","1970-01-01 00:00:01+00"]

query T
SELECT '[1970-01-01 00:00:01,)'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - 'empty'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
("1970-01-01 00:00:01+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange
----
["1970-01-01 00:00:02+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange
----
("1970-01-01 00:00:02+00",)

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
empty

query T
SELECT '(1970-01-01 00:00:01,)'::tstzrange - '(,)'::tstzrange
----
empty

query T
SELECT '(,)'::tstzrange - 'empty'::tstzrange
----
(,)

query T
SELECT '(,)'::tstzrange - '(,1969-12-31 11:59:59]'::tstzrange
----
("1969-12-31 11:59:59+00",)

query T
SELECT '(,)'::tstzrange - '(,1969-12-31 11:59:59)'::tstzrange
----
["1969-12-31 11:59:59+00",)

query error result of range difference would not be contiguous
SELECT '(,)'::tstzrange - '(1969-12-31 11:59:58,1970-01-01 00:00:02)'::tstzrange

query error result of range difference would not be contiguous
SELECT '(,)'::tstzrange - '[1969-12-31 11:59:58,1970-01-01 00:00:02]'::tstzrange

query T
SELECT '(,)'::tstzrange - '[1970-01-01 00:00:01,)'::tstzrange
----
(,"1970-01-01 00:00:01+00")

query T
SELECT '(,)'::tstzrange - '(1970-01-01 00:00:01,)'::tstzrange
----
(,"1970-01-01 00:00:01+00"]