Skip to content

Commit a9e0839

Browse files
tglsfdctglsfdc
committed
Create crosstype comparison operators for date vs. timestamp and date
vs. timestamptz. This allows use of indexes for expressions like datecol >= date 'today' - interval '1 month' which were formerly not indexable without casting the righthand side down from timestamp to date.
1 parent 2e5fe48 commit a9e0839

File tree

9 files changed

+601
-100
lines changed

9 files changed

+601
-100
lines changed

‎src/backend/utils/adt/date.c

Lines changed: 417 additions & 68 deletions
Large diffs are not rendered by default.

‎src/backend/utils/adt/timestamp.c

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.98 2003/12/25 03:36:23 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.99 2004/02/14 20:16:17 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1296,7 +1296,7 @@ SetEpochTimestamp(void)
12961296
*
12971297
* collate invalid timestamp at the end
12981298
*/
1299-
static int
1299+
int
13001300
timestamp_cmp_internal(Timestamp dt1, Timestamp dt2)
13011301
{
13021302
#ifdef HAVE_INT64_TIMESTAMP
@@ -1703,7 +1703,7 @@ timestamp_mi(PG_FUNCTION_ARGS)
17031703
}
17041704

17051705

1706-
/* timestamp_pl_span()
1706+
/* timestamp_pl_interval()
17071707
* Add a interval to a timestamp data type.
17081708
* Note that interval has provisions for qualitative year/month
17091709
* units, so try to do the right thing with them.
@@ -1713,7 +1713,7 @@ timestamp_mi(PG_FUNCTION_ARGS)
17131713
* Lastly, add in the "quantitative time".
17141714
*/
17151715
Datum
1716-
timestamp_pl_span(PG_FUNCTION_ARGS)
1716+
timestamp_pl_interval(PG_FUNCTION_ARGS)
17171717
{
17181718
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
17191719
Interval *span = PG_GETARG_INTERVAL_P(1);
@@ -1764,7 +1764,7 @@ timestamp_pl_span(PG_FUNCTION_ARGS)
17641764
}
17651765

17661766
Datum
1767-
timestamp_mi_span(PG_FUNCTION_ARGS)
1767+
timestamp_mi_interval(PG_FUNCTION_ARGS)
17681768
{
17691769
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
17701770
Interval *span = PG_GETARG_INTERVAL_P(1);
@@ -1773,13 +1773,13 @@ timestamp_mi_span(PG_FUNCTION_ARGS)
17731773
tspan.month = -span->month;
17741774
tspan.time = -span->time;
17751775

1776-
return DirectFunctionCall2(timestamp_pl_span,
1776+
return DirectFunctionCall2(timestamp_pl_interval,
17771777
TimestampGetDatum(timestamp),
17781778
PointerGetDatum(&tspan));
17791779
}
17801780

17811781

1782-
/* timestamptz_pl_span()
1782+
/* timestamptz_pl_interval()
17831783
* Add a interval to a timestamp with time zone data type.
17841784
* Note that interval has provisions for qualitative year/month
17851785
* units, so try to do the right thing with them.
@@ -1789,7 +1789,7 @@ timestamp_mi_span(PG_FUNCTION_ARGS)
17891789
* Lastly, add in the "quantitative time".
17901790
*/
17911791
Datum
1792-
timestamptz_pl_span(PG_FUNCTION_ARGS)
1792+
timestamptz_pl_interval(PG_FUNCTION_ARGS)
17931793
{
17941794
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
17951795
Interval *span = PG_GETARG_INTERVAL_P(1);
@@ -1844,7 +1844,7 @@ timestamptz_pl_span(PG_FUNCTION_ARGS)
18441844
}
18451845

18461846
Datum
1847-
timestamptz_mi_span(PG_FUNCTION_ARGS)
1847+
timestamptz_mi_interval(PG_FUNCTION_ARGS)
18481848
{
18491849
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
18501850
Interval *span = PG_GETARG_INTERVAL_P(1);
@@ -1853,7 +1853,7 @@ timestamptz_mi_span(PG_FUNCTION_ARGS)
18531853
tspan.month = -span->month;
18541854
tspan.time = -span->time;
18551855

1856-
return DirectFunctionCall2(timestamptz_pl_span,
1856+
return DirectFunctionCall2(timestamptz_pl_interval,
18571857
TimestampGetDatum(timestamp),
18581858
PointerGetDatum(&tspan));
18591859
}

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.218 2004/02/12 23:41:03 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.219 2004/02/14 20:16:17 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200402121
56+
#define CATALOG_VERSION_NO 200402141
5757

5858
#endif

‎src/include/catalog/pg_amop.h

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@
2323
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
2424
* Portions Copyright (c) 1994, Regents of the University of California
2525
*
26-
* $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.57 2003/11/29 22:40:58 pgsql Exp $
26+
* $PostgreSQL: pgsql/src/include/catalog/pg_amop.h,v 1.58 2004/02/14 20:16:17 tgl Exp $
2727
*
2828
* NOTES
2929
* the genbki.sh script reads this file and generates .bki
@@ -301,6 +301,18 @@ DATA(insert ( 434 0 2 f 1096 ));
301301
DATA(insert ( 434 0 3 f 1093 ));
302302
DATA(insert ( 434 0 4 f 1098 ));
303303
DATA(insert ( 434 0 5 f 1097 ));
304+
/* crosstype operators vs timestamp */
305+
DATA(insert ( 434 1114 1 f 2345 ));
306+
DATA(insert ( 434 1114 2 f 2346 ));
307+
DATA(insert ( 434 1114 3 f 2347 ));
308+
DATA(insert ( 434 1114 4 f 2348 ));
309+
DATA(insert ( 434 1114 5 f 2349 ));
310+
/* crosstype operators vs timestamptz */
311+
DATA(insert ( 434 1184 1 f 2358 ));
312+
DATA(insert ( 434 1184 2 f 2359 ));
313+
DATA(insert ( 434 1184 3 f 2360 ));
314+
DATA(insert ( 434 1184 4 f 2361 ));
315+
DATA(insert ( 434 1184 5 f 2362 ));
304316

305317
/*
306318
* btree time_ops
@@ -331,6 +343,12 @@ DATA(insert ( 2039 0 2 f 2063 ));
331343
DATA(insert ( 2039 0 3 f 2060 ));
332344
DATA(insert ( 2039 0 4 f 2065 ));
333345
DATA(insert ( 2039 0 5 f 2064 ));
346+
/* crosstype operators vs date */
347+
DATA(insert ( 2039 1082 1 f 2371 ));
348+
DATA(insert ( 2039 1082 2 f 2372 ));
349+
DATA(insert ( 2039 1082 3 f 2373 ));
350+
DATA(insert ( 2039 1082 4 f 2374 ));
351+
DATA(insert ( 2039 1082 5 f 2375 ));
334352

335353
/*
336354
* btree timestamptz_ops
@@ -341,6 +359,12 @@ DATA(insert ( 1998 0 2 f 1323 ));
341359
DATA(insert ( 1998 0 3 f 1320 ));
342360
DATA(insert ( 1998 0 4 f 1325 ));
343361
DATA(insert ( 1998 0 5 f 1324 ));
362+
/* crosstype operators vs date */
363+
DATA(insert ( 1998 1082 1 f 2384 ));
364+
DATA(insert ( 1998 1082 2 f 2385 ));
365+
DATA(insert ( 1998 1082 3 f 2386 ));
366+
DATA(insert ( 1998 1082 4 f 2387 ));
367+
DATA(insert ( 1998 1082 5 f 2388 ));
344368

345369
/*
346370
* btree interval_ops

‎src/include/catalog/pg_amproc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,7 +19,7 @@
1919
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
2020
* Portions Copyright (c) 1994, Regents of the University of California
2121
*
22-
* $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.46 2003/11/29 22:40:58 pgsql Exp $
22+
* $PostgreSQL: pgsql/src/include/catalog/pg_amproc.h,v 1.47 2004/02/14 20:16:17 tgl Exp $
2323
*
2424
* NOTES
2525
* the genbki.sh script reads this file and generates .bki
@@ -91,6 +91,8 @@ DATA(insert ( 428 0 1 1954 ));
9191
DATA(insert ( 429 0 1 358 ));
9292
DATA(insert ( 432 0 1 926 ));
9393
DATA(insert ( 434 0 1 1092 ));
94+
DATA(insert ( 434 1114 1 2344 ));
95+
DATA(insert ( 434 1184 1 2357 ));
9496
DATA(insert ( 1970 0 1 354 ));
9597
DATA(insert ( 1970 701 1 2194 ));
9698
DATA(insert ( 1972 0 1 355 ));
@@ -114,10 +116,12 @@ DATA(insert ( 1991 0 1 404 ));
114116
DATA(insert ( 1994 0 1 360 ));
115117
DATA(insert ( 1996 0 1 1107 ));
116118
DATA(insert ( 1998 0 1 1314 ));
119+
DATA(insert ( 1998 1082 1 2383 ));
117120
DATA(insert ( 2000 0 1 1358 ));
118121
DATA(insert ( 2002 0 1 1672 ));
119122
DATA(insert ( 2003 0 1 360 ));
120123
DATA(insert ( 2039 0 1 2045 ));
124+
DATA(insert ( 2039 1082 1 2370 ));
121125
DATA(insert ( 2095 0 1 2166 ));
122126
DATA(insert ( 2096 0 1 2166 ));
123127
DATA(insert ( 2097 0 1 2180 ));

‎src/include/catalog/pg_operator.h

Lines changed: 34 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
11-
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.123 2003/12/01 21:52:37 momjian Exp $
11+
* $PostgreSQL: pgsql/src/include/catalog/pg_operator.h,v 1.124 2004/02/14 20:16:17 tgl Exp $
1212
*
1313
* NOTES
1414
* the genbki.sh script reads this file and generates .bki
@@ -535,9 +535,9 @@ DATA(insert OID = 1322 ( "<" PGNSP PGUID b f 1184 1184 16 1324 1325 0 0 0 0
535535
DATA(insert OID = 1323 ( "<=" PGNSP PGUID b f 1184 1184 16 1325 1324 0 0 0 0 timestamptz_le scalarltsel scalarltjoinsel ));
536536
DATA(insert OID = 1324 ( ">" PGNSP PGUID b f 1184 1184 16 1322 1323 0 0 0 0 timestamptz_gt scalargtsel scalargtjoinsel ));
537537
DATA(insert OID = 1325 ( ">=" PGNSP PGUID b f 1184 1184 16 1323 1322 0 0 0 0 timestamptz_ge scalargtsel scalargtjoinsel ));
538-
DATA(insert OID = 1327 ( "+" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_pl_span - - ));
538+
DATA(insert OID = 1327 ( "+" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_pl_interval - - ));
539539
DATA(insert OID = 1328 ( "-" PGNSP PGUID b f 1184 1184 1186 0 0 0 0 0 0 timestamptz_mi - - ));
540-
DATA(insert OID = 1329 ( "-" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_mi_span - - ));
540+
DATA(insert OID = 1329 ( "-" PGNSP PGUID b f 1184 1186 1184 0 0 0 0 0 0 timestamptz_mi_interval - - ));
541541

542542
/* interval operators */
543543
DATA(insert OID = 1330 ( "=" PGNSP PGUID b t 1186 1186 16 1330 1331 1332 1332 1332 1334 interval_eq eqsel eqjoinsel ));
@@ -791,9 +791,9 @@ DATA(insert OID = 2062 ( "<" PGNSP PGUID b f 1114 1114 16 2064 2065 0 0 0 0
791791
DATA(insert OID = 2063 ( "<=" PGNSP PGUID b f 1114 1114 16 2065 2064 0 0 0 0 timestamp_le scalarltsel scalarltjoinsel ));
792792
DATA(insert OID = 2064 ( ">" PGNSP PGUID b f 1114 1114 16 2062 2063 0 0 0 0 timestamp_gt scalargtsel scalargtjoinsel ));
793793
DATA(insert OID = 2065 ( ">=" PGNSP PGUID b f 1114 1114 16 2063 2062 0 0 0 0 timestamp_ge scalargtsel scalargtjoinsel ));
794-
DATA(insert OID = 2066 ( "+" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_pl_span - - ));
794+
DATA(insert OID = 2066 ( "+" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_pl_interval - - ));
795795
DATA(insert OID = 2067 ( "-" PGNSP PGUID b f 1114 1114 1186 0 0 0 0 0 0 timestamp_mi - - ));
796-
DATA(insert OID = 2068 ( "-" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_mi_span - - ));
796+
DATA(insert OID = 2068 ( "-" PGNSP PGUID b f 1114 1186 1114 0 0 0 0 0 0 timestamp_mi_interval - - ));
797797

798798
/* character-by-character (not collation order) comparison operators for character types */
799799

@@ -818,6 +818,35 @@ DATA(insert OID = 2335 ( "~>=~" PGNSP PGUID b f 19 19 16 2333 2332 0 0 0 0 name_
818818
DATA(insert OID = 2336 ( "~>~" PGNSP PGUID b f 19 19 16 2332 2333 0 0 0 0 name_pattern_gt scalargtsel scalargtjoinsel ));
819819
DATA(insert OID = 2337 ( "~<>~" PGNSP PGUID b f 19 19 16 2337 2334 0 0 0 0 name_pattern_ne neqsel neqjoinsel ));
820820

821+
/* crosstype operations for date vs. timestamp and timestamptz */
822+
823+
DATA(insert OID = 2345 ( "<" PGNSP PGUID b f 1082 1114 16 2375 2348 0 0 0 0 date_lt_timestamp scalarltsel scalarltjoinsel ));
824+
DATA(insert OID = 2346 ( "<=" PGNSP PGUID b f 1082 1114 16 2374 2349 0 0 0 0 date_le_timestamp scalarltsel scalarltjoinsel ));
825+
DATA(insert OID = 2347 ( "=" PGNSP PGUID b f 1082 1114 16 2373 2350 0 0 0 0 date_eq_timestamp eqsel eqjoinsel ));
826+
DATA(insert OID = 2348 ( ">=" PGNSP PGUID b f 1082 1114 16 2372 2345 0 0 0 0 date_ge_timestamp scalargtsel scalargtjoinsel ));
827+
DATA(insert OID = 2349 ( ">" PGNSP PGUID b f 1082 1114 16 2371 2346 0 0 0 0 date_gt_timestamp scalargtsel scalargtjoinsel ));
828+
DATA(insert OID = 2350 ( "<>" PGNSP PGUID b f 1082 1114 16 2376 2347 0 0 0 0 date_ne_timestamp neqsel neqjoinsel ));
829+
830+
DATA(insert OID = 2358 ( "<" PGNSP PGUID b f 1082 1184 16 2388 2361 0 0 0 0 date_lt_timestamptz scalarltsel scalarltjoinsel ));
831+
DATA(insert OID = 2359 ( "<=" PGNSP PGUID b f 1082 1184 16 2387 2362 0 0 0 0 date_le_timestamptz scalarltsel scalarltjoinsel ));
832+
DATA(insert OID = 2360 ( "=" PGNSP PGUID b f 1082 1184 16 2386 2363 0 0 0 0 date_eq_timestamptz eqsel eqjoinsel ));
833+
DATA(insert OID = 2361 ( ">=" PGNSP PGUID b f 1082 1184 16 2385 2358 0 0 0 0 date_ge_timestamptz scalargtsel scalargtjoinsel ));
834+
DATA(insert OID = 2362 ( ">" PGNSP PGUID b f 1082 1184 16 2384 2359 0 0 0 0 date_gt_timestamptz scalargtsel scalargtjoinsel ));
835+
DATA(insert OID = 2363 ( "<>" PGNSP PGUID b f 1082 1184 16 2389 2360 0 0 0 0 date_ne_timestamptz neqsel neqjoinsel ));
836+
837+
DATA(insert OID = 2371 ( "<" PGNSP PGUID b f 1114 1082 16 2349 2374 0 0 0 0 timestamp_lt_date scalarltsel scalarltjoinsel ));
838+
DATA(insert OID = 2372 ( "<=" PGNSP PGUID b f 1114 1082 16 2348 2375 0 0 0 0 timestamp_le_date scalarltsel scalarltjoinsel ));
839+
DATA(insert OID = 2373 ( "=" PGNSP PGUID b f 1114 1082 16 2347 2376 0 0 0 0 timestamp_eq_date eqsel eqjoinsel ));
840+
DATA(insert OID = 2374 ( ">=" PGNSP PGUID b f 1114 1082 16 2346 2371 0 0 0 0 timestamp_ge_date scalargtsel scalargtjoinsel ));
841+
DATA(insert OID = 2375 ( ">" PGNSP PGUID b f 1114 1082 16 2345 2372 0 0 0 0 timestamp_gt_date scalargtsel scalargtjoinsel ));
842+
DATA(insert OID = 2376 ( "<>" PGNSP PGUID b f 1114 1082 16 2350 2373 0 0 0 0 timestamp_ne_date neqsel neqjoinsel ));
843+
844+
DATA(insert OID = 2384 ( "<" PGNSP PGUID b f 1184 1082 16 2362 2387 0 0 0 0 timestamptz_lt_date scalarltsel scalarltjoinsel ));
845+
DATA(insert OID = 2385 ( "<=" PGNSP PGUID b f 1184 1082 16 2361 2388 0 0 0 0 timestamptz_le_date scalarltsel scalarltjoinsel ));
846+
DATA(insert OID = 2386 ( "=" PGNSP PGUID b f 1184 1082 16 2360 2389 0 0 0 0 timestamptz_eq_date eqsel eqjoinsel ));
847+
DATA(insert OID = 2387 ( ">=" PGNSP PGUID b f 1184 1082 16 2359 2384 0 0 0 0 timestamptz_ge_date scalargtsel scalargtjoinsel ));
848+
DATA(insert OID = 2388 ( ">" PGNSP PGUID b f 1184 1082 16 2358 2385 0 0 0 0 timestamptz_gt_date scalargtsel scalargtjoinsel ));
849+
DATA(insert OID = 2389 ( "<>" PGNSP PGUID b f 1184 1082 16 2363 2386 0 0 0 0 timestamptz_ne_date neqsel neqjoinsel ));
821850

822851

823852
/*

0 commit comments

Comments
 (0)