0006-Revert-Use-daterange-and-YMD-in-without_overlaps-tes.patch

From 016ae755c47e6a6406816557df7330027dd138d8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 15 May 2024 10:34:40 +0200
Subject: [PATCH 06/10] Revert "Use daterange and YMD in without_overlaps tests
 instead of tsrange."

This reverts commit a88c800deb6ff3fe5e5dccd56f771d2132f74a9b.
---
 .../regress/expected/without_overlaps.out     | 76 +++++++++----------
 src/test/regress/sql/without_overlaps.sql     | 42 +++++-----
 2 files changed, 56 insertions(+), 62 deletions(-)

diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index ea5591a3b38..726e94102bf 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -3,13 +3,12 @@
 -- We leave behind several tables to test pg_dump etc:
 -- temporal_rng, temporal_rng2,
 -- temporal_fk_rng2rng.
-SET datestyle TO ISO, YMD;
 --
 -- test input parser
 --
 -- PK with no columns just WITHOUT OVERLAPS:
 CREATE TABLE temporal_rng (
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
 );
 ERROR:  constraint using WITHOUT OVERLAPS needs at least two columns
@@ -35,7 +34,7 @@ CREATE TABLE temporal_rng (
 	-- use an int4range instead of an int.
 	-- (The rangetypes regression test uses the same trick.)
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng
@@ -43,7 +42,7 @@ CREATE TABLE temporal_rng (
   Column  |   Type    | Collation | Nullable | Default 
 ----------+-----------+-----------+----------+---------
  id       | int4range |           | not null | 
- valid_at | daterange |           | not null | 
+ valid_at | tsrange   |           | not null | 
 Indexes:
     "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 
@@ -64,7 +63,7 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
 CREATE TABLE temporal_rng2 (
 	id1 int4range,
 	id2 int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng2
@@ -73,7 +72,7 @@ CREATE TABLE temporal_rng2 (
 ----------+-----------+-----------+----------+---------
  id1      | int4range |           | not null | 
  id2      | int4range |           | not null | 
- valid_at | daterange |           | not null | 
+ valid_at | tsrange   |           | not null | 
 Indexes:
     "temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
 
@@ -116,7 +115,7 @@ Indexes:
 
 -- UNIQUE with no columns just WITHOUT OVERLAPS:
 CREATE TABLE temporal_rng3 (
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS)
 );
 ERROR:  constraint using WITHOUT OVERLAPS needs at least two columns
@@ -139,7 +138,7 @@ HINT:  You must specify an operator class for the index or define a default oper
 -- UNIQUE with one column plus a range:
 CREATE TABLE temporal_rng3 (
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng3
@@ -147,7 +146,7 @@ CREATE TABLE temporal_rng3 (
   Column  |   Type    | Collation | Nullable | Default 
 ----------+-----------+-----------+----------+---------
  id       | int4range |           |          | 
- valid_at | daterange |           |          | 
+ valid_at | tsrange   |           |          | 
 Indexes:
     "temporal_rng3_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS)
 
@@ -168,7 +167,7 @@ DROP TABLE temporal_rng3;
 CREATE TABLE temporal_rng3 (
 	id1 int4range,
 	id2 int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng3
@@ -177,7 +176,7 @@ CREATE TABLE temporal_rng3 (
 ----------+-----------+-----------+----------+---------
  id1      | int4range |           |          | 
  id2      | int4range |           |          | 
- valid_at | daterange |           |          | 
+ valid_at | tsrange   |           |          | 
 Indexes:
     "temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
 
@@ -210,7 +209,7 @@ DROP TYPE textrange2;
 DROP TABLE temporal_rng;
 CREATE TABLE temporal_rng (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 ALTER TABLE temporal_rng
 	ADD CONSTRAINT temporal_rng_pk
@@ -218,7 +217,7 @@ ALTER TABLE temporal_rng
 -- PK with USING INDEX (not possible):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 ALTER TABLE temporal3
@@ -232,7 +231,7 @@ DROP TABLE temporal3;
 -- UNIQUE with USING INDEX (not possible):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 ALTER TABLE temporal3
@@ -246,7 +245,7 @@ DROP TABLE temporal3;
 -- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at);
 ALTER TABLE temporal3
@@ -259,7 +258,7 @@ CREATE TABLE temporal3 (
 	id int4range
 );
 ALTER TABLE temporal3
-	ADD COLUMN valid_at daterange,
+	ADD COLUMN valid_at tsrange,
 	ADD CONSTRAINT temporal3_pk
 	PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
 DROP TABLE temporal3;
@@ -268,7 +267,7 @@ CREATE TABLE temporal3 (
 	id int4range
 );
 ALTER TABLE temporal3
-	ADD COLUMN valid_at daterange,
+	ADD COLUMN valid_at tsrange,
 	ADD CONSTRAINT temporal3_uq
 	UNIQUE (id, valid_at WITHOUT OVERLAPS);
 DROP TABLE temporal3;
@@ -276,17 +275,17 @@ DROP TABLE temporal3;
 -- test PK inserts
 --
 -- okay:
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-01-02', '2018-02-03'));
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-03-03', '2018-04-04'));
-INSERT INTO temporal_rng VALUES ('[2,2]', daterange('2018-01-01', '2018-01-05'));
-INSERT INTO temporal_rng VALUES ('[3,3]', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
 -- should fail:
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
 ERROR:  conflicting key value violates exclusion constraint "temporal_rng_pk"
-DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)) conflicts with existing key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)).
-INSERT INTO temporal_rng VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+DETAIL:  Key (id, valid_at)=([1,2), ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")) conflicts with existing key (id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018")).
+INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
 ERROR:  null value in column "id" of relation "temporal_rng" violates not-null constraint
-DETAIL:  Failing row contains (null, [2018-01-01,2018-01-05)).
+DETAIL:  Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")).
 INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
 ERROR:  null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
 DETAIL:  Failing row contains ([3,4), null).
@@ -312,7 +311,7 @@ DROP TABLE temporal3;
 --
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 );
 ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL;
@@ -340,22 +339,22 @@ INSERT INTO temporal_partitioned VALUES
 SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
   id   |        valid_at         | name  
 -------+-------------------------+-------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+ [3,4) | [01-01-2000,01-01-2010) | three
 (3 rows)
 
 SELECT * FROM tp1 ORDER BY id, valid_at;
   id   |        valid_at         | name 
 -------+-------------------------+------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
 (2 rows)
 
 SELECT * FROM tp2 ORDER BY id, valid_at;
   id   |        valid_at         | name  
 -------+-------------------------+-------
- [3,4) | [2000-01-01,2010-01-01) | three
+ [3,4) | [01-01-2000,01-01-2010) | three
 (1 row)
 
 DROP TABLE temporal_partitioned;
@@ -375,23 +374,22 @@ INSERT INTO temporal_partitioned VALUES
 SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
   id   |        valid_at         | name  
 -------+-------------------------+-------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
- [3,4) | [2000-01-01,2010-01-01) | three
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+ [3,4) | [01-01-2000,01-01-2010) | three
 (3 rows)
 
 SELECT * FROM tp1 ORDER BY id, valid_at;
   id   |        valid_at         | name 
 -------+-------------------------+------
- [1,2) | [2000-01-01,2000-02-01) | one
- [1,2) | [2000-02-01,2000-03-01) | one
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
 (2 rows)
 
 SELECT * FROM tp2 ORDER BY id, valid_at;
   id   |        valid_at         | name  
 -------+-------------------------+-------
- [3,4) | [2000-01-01,2010-01-01) | three
+ [3,4) | [01-01-2000,01-01-2010) | three
 (1 row)
 
 DROP TABLE temporal_partitioned;
-RESET datestyle;
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index bb8b85aa25f..c8e8ab994a0 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -4,8 +4,6 @@
 -- temporal_rng, temporal_rng2,
 -- temporal_fk_rng2rng.
 
-SET datestyle TO ISO, YMD;
-
 --
 -- test input parser
 --
@@ -13,7 +11,7 @@
 -- PK with no columns just WITHOUT OVERLAPS:
 
 CREATE TABLE temporal_rng (
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
 );
 
@@ -39,7 +37,7 @@ CREATE TABLE temporal_rng (
 	-- use an int4range instead of an int.
 	-- (The rangetypes regression test uses the same trick.)
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng
@@ -51,7 +49,7 @@ CREATE TABLE temporal_rng (
 CREATE TABLE temporal_rng2 (
 	id1 int4range,
 	id2 int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng2
@@ -81,7 +79,7 @@ CREATE TABLE temporal_mltrng (
 -- UNIQUE with no columns just WITHOUT OVERLAPS:
 
 CREATE TABLE temporal_rng3 (
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS)
 );
 
@@ -104,7 +102,7 @@ CREATE TABLE temporal_rng3 (
 
 CREATE TABLE temporal_rng3 (
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng3
@@ -116,7 +114,7 @@ CREATE TABLE temporal_rng3 (
 CREATE TABLE temporal_rng3 (
 	id1 int4range,
 	id2 int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
 );
 \d temporal_rng3
@@ -142,7 +140,7 @@ CREATE TABLE temporal_rng3 (
 DROP TABLE temporal_rng;
 CREATE TABLE temporal_rng (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 ALTER TABLE temporal_rng
 	ADD CONSTRAINT temporal_rng_pk
@@ -151,7 +149,7 @@ CREATE TABLE temporal_rng (
 -- PK with USING INDEX (not possible):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 ALTER TABLE temporal3
@@ -162,7 +160,7 @@ CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 -- UNIQUE with USING INDEX (not possible):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 ALTER TABLE temporal3
@@ -173,7 +171,7 @@ CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
 -- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint):
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange
+	valid_at tsrange
 );
 CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at);
 ALTER TABLE temporal3
@@ -186,7 +184,7 @@ CREATE TABLE temporal3 (
 	id int4range
 );
 ALTER TABLE temporal3
-	ADD COLUMN valid_at daterange,
+	ADD COLUMN valid_at tsrange,
 	ADD CONSTRAINT temporal3_pk
 	PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
 DROP TABLE temporal3;
@@ -196,7 +194,7 @@ CREATE TABLE temporal3 (
 	id int4range
 );
 ALTER TABLE temporal3
-	ADD COLUMN valid_at daterange,
+	ADD COLUMN valid_at tsrange,
 	ADD CONSTRAINT temporal3_uq
 	UNIQUE (id, valid_at WITHOUT OVERLAPS);
 DROP TABLE temporal3;
@@ -206,14 +204,14 @@ CREATE TABLE temporal3 (
 --
 
 -- okay:
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-01-02', '2018-02-03'));
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-03-03', '2018-04-04'));
-INSERT INTO temporal_rng VALUES ('[2,2]', daterange('2018-01-01', '2018-01-05'));
-INSERT INTO temporal_rng VALUES ('[3,3]', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
 
 -- should fail:
-INSERT INTO temporal_rng VALUES ('[1,1]', daterange('2018-01-01', '2018-01-05'));
-INSERT INTO temporal_rng VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
 INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
 
 --
@@ -241,7 +239,7 @@ CREATE TABLE temporal3 (
 
 CREATE TABLE temporal3 (
 	id int4range,
-	valid_at daterange,
+	valid_at tsrange,
 	CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 );
 
@@ -290,5 +288,3 @@ CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,3]', '[4,4
 SELECT * FROM tp1 ORDER BY id, valid_at;
 SELECT * FROM tp2 ORDER BY id, valid_at;
 DROP TABLE temporal_partitioned;
-
-RESET datestyle;
-- 
2.44.0