Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita88c800

Browse files
committed
Use daterange and YMD in without_overlaps tests instead of tsrange.
This makes things a lot easier to read, especially when we get to theFOREIGN KEY tests later.Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>Discussion:https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
1 parent794f10f commita88c800

File tree

2 files changed

+62
-56
lines changed

2 files changed

+62
-56
lines changed

‎src/test/regress/expected/without_overlaps.out

Lines changed: 39 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -3,12 +3,13 @@
33
-- We leave behind several tables to test pg_dump etc:
44
-- temporal_rng, temporal_rng2,
55
-- temporal_fk_rng2rng.
6+
SET datestyle TO ISO, YMD;
67
--
78
-- test input parser
89
--
910
-- PK with no columns just WITHOUT OVERLAPS:
1011
CREATE TABLE temporal_rng (
11-
valid_attsrange,
12+
valid_atdaterange,
1213
CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
1314
);
1415
ERROR: constraint using WITHOUT OVERLAPS needs at least two columns
@@ -34,15 +35,15 @@ CREATE TABLE temporal_rng (
3435
-- use an int4range instead of an int.
3536
-- (The rangetypes regression test uses the same trick.)
3637
id int4range,
37-
valid_attsrange,
38+
valid_atdaterange,
3839
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
3940
);
4041
\d temporal_rng
4142
Table "public.temporal_rng"
4243
Column | Type | Collation | Nullable | Default
4344
----------+-----------+-----------+----------+---------
4445
id | int4range | | not null |
45-
valid_at |tsrange | | not null |
46+
valid_at |daterange | | not null |
4647
Indexes:
4748
"temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
4849

@@ -63,7 +64,7 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
6364
CREATE TABLE temporal_rng2 (
6465
id1 int4range,
6566
id2 int4range,
66-
valid_attsrange,
67+
valid_atdaterange,
6768
CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
6869
);
6970
\d temporal_rng2
@@ -72,7 +73,7 @@ CREATE TABLE temporal_rng2 (
7273
----------+-----------+-----------+----------+---------
7374
id1 | int4range | | not null |
7475
id2 | int4range | | not null |
75-
valid_at |tsrange | | not null |
76+
valid_at |daterange | | not null |
7677
Indexes:
7778
"temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
7879

@@ -115,7 +116,7 @@ Indexes:
115116

116117
-- UNIQUE with no columns just WITHOUT OVERLAPS:
117118
CREATE TABLE temporal_rng3 (
118-
valid_attsrange,
119+
valid_atdaterange,
119120
CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS)
120121
);
121122
ERROR: constraint using WITHOUT OVERLAPS needs at least two columns
@@ -138,15 +139,15 @@ HINT: You must specify an operator class for the index or define a default oper
138139
-- UNIQUE with one column plus a range:
139140
CREATE TABLE temporal_rng3 (
140141
id int4range,
141-
valid_attsrange,
142+
valid_atdaterange,
142143
CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
143144
);
144145
\d temporal_rng3
145146
Table "public.temporal_rng3"
146147
Column | Type | Collation | Nullable | Default
147148
----------+-----------+-----------+----------+---------
148149
id | int4range | | |
149-
valid_at |tsrange | | |
150+
valid_at |daterange | | |
150151
Indexes:
151152
"temporal_rng3_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS)
152153

@@ -167,7 +168,7 @@ DROP TABLE temporal_rng3;
167168
CREATE TABLE temporal_rng3 (
168169
id1 int4range,
169170
id2 int4range,
170-
valid_attsrange,
171+
valid_atdaterange,
171172
CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
172173
);
173174
\d temporal_rng3
@@ -176,7 +177,7 @@ CREATE TABLE temporal_rng3 (
176177
----------+-----------+-----------+----------+---------
177178
id1 | int4range | | |
178179
id2 | int4range | | |
179-
valid_at |tsrange | | |
180+
valid_at |daterange | | |
180181
Indexes:
181182
"temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
182183

@@ -209,15 +210,15 @@ DROP TYPE textrange2;
209210
DROP TABLE temporal_rng;
210211
CREATE TABLE temporal_rng (
211212
id int4range,
212-
valid_attsrange
213+
valid_atdaterange
213214
);
214215
ALTER TABLE temporal_rng
215216
ADD CONSTRAINT temporal_rng_pk
216217
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
217218
-- PK with USING INDEX (not possible):
218219
CREATE TABLE temporal3 (
219220
id int4range,
220-
valid_attsrange
221+
valid_atdaterange
221222
);
222223
CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
223224
ALTER TABLE temporal3
@@ -231,7 +232,7 @@ DROP TABLE temporal3;
231232
-- UNIQUE with USING INDEX (not possible):
232233
CREATE TABLE temporal3 (
233234
id int4range,
234-
valid_attsrange
235+
valid_atdaterange
235236
);
236237
CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at);
237238
ALTER TABLE temporal3
@@ -245,7 +246,7 @@ DROP TABLE temporal3;
245246
-- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint):
246247
CREATE TABLE temporal3 (
247248
id int4range,
248-
valid_attsrange
249+
valid_atdaterange
249250
);
250251
CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at);
251252
ALTER TABLE temporal3
@@ -258,7 +259,7 @@ CREATE TABLE temporal3 (
258259
id int4range
259260
);
260261
ALTER TABLE temporal3
261-
ADD COLUMN valid_attsrange,
262+
ADD COLUMN valid_atdaterange,
262263
ADD CONSTRAINT temporal3_pk
263264
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
264265
DROP TABLE temporal3;
@@ -267,25 +268,25 @@ CREATE TABLE temporal3 (
267268
id int4range
268269
);
269270
ALTER TABLE temporal3
270-
ADD COLUMN valid_attsrange,
271+
ADD COLUMN valid_atdaterange,
271272
ADD CONSTRAINT temporal3_uq
272273
UNIQUE (id, valid_at WITHOUT OVERLAPS);
273274
DROP TABLE temporal3;
274275
--
275276
-- test PK inserts
276277
--
277278
-- okay:
278-
INSERT INTO temporal_rng VALUES ('[1,1]',tsrange('2018-01-02', '2018-02-03'));
279-
INSERT INTO temporal_rng VALUES ('[1,1]',tsrange('2018-03-03', '2018-04-04'));
280-
INSERT INTO temporal_rng VALUES ('[2,2]',tsrange('2018-01-01', '2018-01-05'));
281-
INSERT INTO temporal_rng VALUES ('[3,3]',tsrange('2018-01-01', NULL));
279+
INSERT INTO temporal_rng VALUES ('[1,1]',daterange('2018-01-02', '2018-02-03'));
280+
INSERT INTO temporal_rng VALUES ('[1,1]',daterange('2018-03-03', '2018-04-04'));
281+
INSERT INTO temporal_rng VALUES ('[2,2]',daterange('2018-01-01', '2018-01-05'));
282+
INSERT INTO temporal_rng VALUES ('[3,3]',daterange('2018-01-01', NULL));
282283
-- should fail:
283-
INSERT INTO temporal_rng VALUES ('[1,1]',tsrange('2018-01-01', '2018-01-05'));
284+
INSERT INTO temporal_rng VALUES ('[1,1]',daterange('2018-01-01', '2018-01-05'));
284285
ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk"
285-
DETAIL: Key (id, valid_at)=([1,2), ["Mon Jan 01 00:00:002018","Fri Jan 05 00:00:002018")) conflicts with existing key (id, valid_at)=([1,2), ["Tue Jan 02 00:00:002018","Sat Feb 03 00:00:002018")).
286-
INSERT INTO temporal_rng VALUES (NULL,tsrange('2018-01-01', '2018-01-05'));
286+
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)).
287+
INSERT INTO temporal_rng VALUES (NULL,daterange('2018-01-01', '2018-01-05'));
287288
ERROR: null value in column "id" of relation "temporal_rng" violates not-null constraint
288-
DETAIL: Failing row contains (null, ["Mon Jan 01 00:00:002018","Fri Jan 05 00:00:002018")).
289+
DETAIL: Failing row contains (null, [2018-01-01,2018-01-05)).
289290
INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
290291
ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
291292
DETAIL: Failing row contains ([3,4), null).
@@ -311,7 +312,7 @@ DROP TABLE temporal3;
311312
--
312313
CREATE TABLE temporal3 (
313314
id int4range,
314-
valid_attsrange,
315+
valid_atdaterange,
315316
CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
316317
);
317318
ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL;
@@ -339,22 +340,22 @@ INSERT INTO temporal_partitioned VALUES
339340
SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
340341
id | valid_at | name
341342
-------+-------------------------+-------
342-
[1,2) | [01-01-2000,02-01-2000) | one
343-
[1,2) | [02-01-2000,03-01-2000) | one
344-
[3,4) | [01-01-2000,01-01-2010) | three
343+
[1,2) | [2000-01-01,2000-02-01) | one
344+
[1,2) | [2000-02-01,2000-03-01) | one
345+
[3,4) | [2000-01-01,2010-01-01) | three
345346
(3 rows)
346347

347348
SELECT * FROM tp1 ORDER BY id, valid_at;
348349
id | valid_at | name
349350
-------+-------------------------+------
350-
[1,2) | [01-01-2000,02-01-2000) | one
351-
[1,2) | [02-01-2000,03-01-2000) | one
351+
[1,2) | [2000-01-01,2000-02-01) | one
352+
[1,2) | [2000-02-01,2000-03-01) | one
352353
(2 rows)
353354

354355
SELECT * FROM tp2 ORDER BY id, valid_at;
355356
id | valid_at | name
356357
-------+-------------------------+-------
357-
[3,4) | [01-01-2000,01-01-2010) | three
358+
[3,4) | [2000-01-01,2010-01-01) | three
358359
(1 row)
359360

360361
DROP TABLE temporal_partitioned;
@@ -374,22 +375,23 @@ INSERT INTO temporal_partitioned VALUES
374375
SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
375376
id | valid_at | name
376377
-------+-------------------------+-------
377-
[1,2) | [01-01-2000,02-01-2000) | one
378-
[1,2) | [02-01-2000,03-01-2000) | one
379-
[3,4) | [01-01-2000,01-01-2010) | three
378+
[1,2) | [2000-01-01,2000-02-01) | one
379+
[1,2) | [2000-02-01,2000-03-01) | one
380+
[3,4) | [2000-01-01,2010-01-01) | three
380381
(3 rows)
381382

382383
SELECT * FROM tp1 ORDER BY id, valid_at;
383384
id | valid_at | name
384385
-------+-------------------------+------
385-
[1,2) | [01-01-2000,02-01-2000) | one
386-
[1,2) | [02-01-2000,03-01-2000) | one
386+
[1,2) | [2000-01-01,2000-02-01) | one
387+
[1,2) | [2000-02-01,2000-03-01) | one
387388
(2 rows)
388389

389390
SELECT * FROM tp2 ORDER BY id, valid_at;
390391
id | valid_at | name
391392
-------+-------------------------+-------
392-
[3,4) | [01-01-2000,01-01-2010) | three
393+
[3,4) | [2000-01-01,2010-01-01) | three
393394
(1 row)
394395

395396
DROP TABLE temporal_partitioned;
397+
RESET datestyle;

‎src/test/regress/sql/without_overlaps.sql

Lines changed: 23 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -4,14 +4,16 @@
44
-- temporal_rng, temporal_rng2,
55
-- temporal_fk_rng2rng.
66

7+
SET datestyle TO ISO, YMD;
8+
79
--
810
-- test input parser
911
--
1012

1113
-- PK with no columns just WITHOUT OVERLAPS:
1214

1315
CREATETABLEtemporal_rng (
14-
valid_attsrange,
16+
valid_atdaterange,
1517
CONSTRAINT temporal_rng_pkPRIMARY KEY (valid_at WITHOUT OVERLAPS)
1618
);
1719

@@ -37,7 +39,7 @@ CREATE TABLE temporal_rng (
3739
-- use an int4range instead of an int.
3840
-- (The rangetypes regression test uses the same trick.)
3941
id int4range,
40-
valid_attsrange,
42+
valid_atdaterange,
4143
CONSTRAINT temporal_rng_pkPRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
4244
);
4345
\d temporal_rng
@@ -49,7 +51,7 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
4951
CREATETABLEtemporal_rng2 (
5052
id1 int4range,
5153
id2 int4range,
52-
valid_attsrange,
54+
valid_atdaterange,
5355
CONSTRAINT temporal_rng2_pkPRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
5456
);
5557
\d temporal_rng2
@@ -79,7 +81,7 @@ CREATE TABLE temporal_mltrng (
7981
-- UNIQUE with no columns just WITHOUT OVERLAPS:
8082

8183
CREATETABLEtemporal_rng3 (
82-
valid_attsrange,
84+
valid_atdaterange,
8385
CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS)
8486
);
8587

@@ -102,7 +104,7 @@ CREATE TABLE temporal_rng3 (
102104

103105
CREATETABLEtemporal_rng3 (
104106
id int4range,
105-
valid_attsrange,
107+
valid_atdaterange,
106108
CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
107109
);
108110
\d temporal_rng3
@@ -114,7 +116,7 @@ DROP TABLE temporal_rng3;
114116
CREATETABLEtemporal_rng3 (
115117
id1 int4range,
116118
id2 int4range,
117-
valid_attsrange,
119+
valid_atdaterange,
118120
CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
119121
);
120122
\d temporal_rng3
@@ -140,7 +142,7 @@ DROP TYPE textrange2;
140142
DROPTABLE temporal_rng;
141143
CREATETABLEtemporal_rng (
142144
id int4range,
143-
valid_attsrange
145+
valid_atdaterange
144146
);
145147
ALTERTABLE temporal_rng
146148
ADDCONSTRAINT temporal_rng_pk
@@ -149,7 +151,7 @@ ALTER TABLE temporal_rng
149151
-- PK with USING INDEX (not possible):
150152
CREATETABLEtemporal3 (
151153
id int4range,
152-
valid_attsrange
154+
valid_atdaterange
153155
);
154156
CREATEINDEXidx_temporal3_uqON temporal3 USING gist (id, valid_at);
155157
ALTERTABLE temporal3
@@ -160,7 +162,7 @@ DROP TABLE temporal3;
160162
-- UNIQUE with USING INDEX (not possible):
161163
CREATETABLEtemporal3 (
162164
id int4range,
163-
valid_attsrange
165+
valid_atdaterange
164166
);
165167
CREATEINDEXidx_temporal3_uqON temporal3 USING gist (id, valid_at);
166168
ALTERTABLE temporal3
@@ -171,7 +173,7 @@ DROP TABLE temporal3;
171173
-- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint):
172174
CREATETABLEtemporal3 (
173175
id int4range,
174-
valid_attsrange
176+
valid_atdaterange
175177
);
176178
CREATEUNIQUE INDEXidx_temporal3_uqON temporal3 (id, valid_at);
177179
ALTERTABLE temporal3
@@ -184,7 +186,7 @@ CREATE TABLE temporal3 (
184186
id int4range
185187
);
186188
ALTERTABLE temporal3
187-
ADD COLUMN valid_attsrange,
189+
ADD COLUMN valid_atdaterange,
188190
ADDCONSTRAINT temporal3_pk
189191
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
190192
DROPTABLE temporal3;
@@ -194,7 +196,7 @@ CREATE TABLE temporal3 (
194196
id int4range
195197
);
196198
ALTERTABLE temporal3
197-
ADD COLUMN valid_attsrange,
199+
ADD COLUMN valid_atdaterange,
198200
ADDCONSTRAINT temporal3_uq
199201
UNIQUE (id, valid_at WITHOUT OVERLAPS);
200202
DROPTABLE temporal3;
@@ -204,14 +206,14 @@ DROP TABLE temporal3;
204206
--
205207

206208
-- okay:
207-
INSERT INTO temporal_rngVALUES ('[1,1]',tsrange('2018-01-02','2018-02-03'));
208-
INSERT INTO temporal_rngVALUES ('[1,1]',tsrange('2018-03-03','2018-04-04'));
209-
INSERT INTO temporal_rngVALUES ('[2,2]',tsrange('2018-01-01','2018-01-05'));
210-
INSERT INTO temporal_rngVALUES ('[3,3]',tsrange('2018-01-01',NULL));
209+
INSERT INTO temporal_rngVALUES ('[1,1]',daterange('2018-01-02','2018-02-03'));
210+
INSERT INTO temporal_rngVALUES ('[1,1]',daterange('2018-03-03','2018-04-04'));
211+
INSERT INTO temporal_rngVALUES ('[2,2]',daterange('2018-01-01','2018-01-05'));
212+
INSERT INTO temporal_rngVALUES ('[3,3]',daterange('2018-01-01',NULL));
211213

212214
-- should fail:
213-
INSERT INTO temporal_rngVALUES ('[1,1]',tsrange('2018-01-01','2018-01-05'));
214-
INSERT INTO temporal_rngVALUES (NULL,tsrange('2018-01-01','2018-01-05'));
215+
INSERT INTO temporal_rngVALUES ('[1,1]',daterange('2018-01-01','2018-01-05'));
216+
INSERT INTO temporal_rngVALUES (NULL,daterange('2018-01-01','2018-01-05'));
215217
INSERT INTO temporal_rngVALUES ('[3,3]',NULL);
216218

217219
--
@@ -239,7 +241,7 @@ DROP TABLE temporal3;
239241

240242
CREATETABLEtemporal3 (
241243
id int4range,
242-
valid_attsrange,
244+
valid_atdaterange,
243245
CONSTRAINT temporal3_pkPRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
244246
);
245247

@@ -288,3 +290,5 @@ SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
288290
SELECT*FROM tp1ORDER BY id, valid_at;
289291
SELECT*FROM tp2ORDER BY id, valid_at;
290292
DROPTABLE temporal_partitioned;
293+
294+
RESET datestyle;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp