From 1084ec189c0f17fa89c0ff4fd45ec9a2bde593fe Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Fri, 30 Nov 2018 14:56:54 +0300 Subject: [PATCH 5/7] Add btree distance operators --- doc/src/sgml/indices.sgml | 6 + src/backend/utils/adt/cash.c | 20 +++ src/backend/utils/adt/date.c | 113 ++++++++++++++++ src/backend/utils/adt/float.c | 41 ++++++ src/backend/utils/adt/int.c | 52 ++++++++ src/backend/utils/adt/int8.c | 44 ++++++ src/backend/utils/adt/oid.c | 14 ++ src/backend/utils/adt/timestamp.c | 103 ++++++++++++++ src/include/catalog/pg_amop.dat | 104 +++++++++++++++ src/include/catalog/pg_operator.dat | 108 +++++++++++++++ src/include/catalog/pg_proc.dat | 82 ++++++++++++ src/include/utils/datetime.h | 2 + src/include/utils/timestamp.h | 4 +- src/test/regress/expected/amutils.out | 6 +- src/test/regress/expected/date.out | 61 +++++++++ src/test/regress/expected/float4.out | 20 +++ src/test/regress/expected/float8.out | 21 +++ src/test/regress/expected/int2.out | 33 +++++ src/test/regress/expected/int4.out | 32 +++++ src/test/regress/expected/int8.out | 31 +++++ src/test/regress/expected/interval.out | 15 +++ src/test/regress/expected/money.out | 6 + src/test/regress/expected/oid.out | 13 ++ src/test/regress/expected/opr_sanity.out | 3 +- src/test/regress/expected/time.out | 16 +++ src/test/regress/expected/timestamp.out | 211 +++++++++++++++++++++++++++++ src/test/regress/expected/timestamptz.out | 214 ++++++++++++++++++++++++++++++ src/test/regress/sql/date.sql | 5 + src/test/regress/sql/float4.sql | 3 + src/test/regress/sql/float8.sql | 3 + src/test/regress/sql/int2.sql | 10 ++ src/test/regress/sql/int4.sql | 10 ++ src/test/regress/sql/int8.sql | 5 + src/test/regress/sql/interval.sql | 2 + src/test/regress/sql/money.sql | 1 + src/test/regress/sql/oid.sql | 2 + src/test/regress/sql/time.sql | 3 + src/test/regress/sql/timestamp.sql | 8 ++ src/test/regress/sql/timestamptz.sql | 8 ++ 39 files changed, 1430 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index caec484..e656a8a 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -183,6 +183,12 @@ SELECT * FROM events ORDER BY event_date <-> date '2017-05-05' LIMIT 10; which finds the ten events closest to a given target date. The ability to do this is again dependent on the particular operator class being used. + Built-in B-tree operator classes support distance ordering for data types + int2, int4, int8, + float4, float8, numeric, + timestamp with time zone, timestamp without time zone, + time with time zone, time without time zone, + date, interval, oid, money. diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c index c92e9d5..b6bfbff 100644 --- a/src/backend/utils/adt/cash.c +++ b/src/backend/utils/adt/cash.c @@ -30,6 +30,7 @@ #include "utils/numeric.h" #include "utils/pg_locale.h" +#define SAMESIGN(a,b) (((a) < 0) == ((b) < 0)) /************************************************************************* * Private routines @@ -1157,3 +1158,22 @@ int8_cash(PG_FUNCTION_ARGS) PG_RETURN_CASH(result); } + +Datum +cash_dist(PG_FUNCTION_ARGS) +{ + Cash a = PG_GETARG_CASH(0); + Cash b = PG_GETARG_CASH(1); + Cash r; + Cash ra; + + if (pg_sub_s64_overflow(a, b, &r) || + r == PG_INT64_MIN) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("money out of range"))); + + ra = Abs(r); + + PG_RETURN_CASH(ra); +} diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index cb6b5e55..237c3cb 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -562,6 +562,17 @@ date_mii(PG_FUNCTION_ARGS) PG_RETURN_DATEADT(result); } +Datum +date_dist(PG_FUNCTION_ARGS) +{ + /* we assume the difference can't overflow */ + Datum diff = DirectFunctionCall2(date_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INT32(Abs(DatumGetInt32(diff))); +} + /* * Internal routines for promoting date to timestamp and timestamp with * time zone @@ -759,6 +770,29 @@ date_cmp_timestamp(PG_FUNCTION_ARGS) } Datum +date_dist_timestamp(PG_FUNCTION_ARGS) +{ + DateADT dateVal = PG_GETARG_DATEADT(0); + Timestamp dt2 = PG_GETARG_TIMESTAMP(1); + Timestamp dt1; + + if (DATE_NOT_FINITE(dateVal) || TIMESTAMP_NOT_FINITE(dt2)) + { + Interval *r = palloc(sizeof(Interval)); + + r->day = INT_MAX; + r->month = INT_MAX; + r->time = PG_INT64_MAX; + + PG_RETURN_INTERVAL_P(r); + } + + dt1 = date2timestamp(dateVal); + + PG_RETURN_INTERVAL_P(timestamp_dist_internal(dt1, dt2)); +} + +Datum date_eq_timestamptz(PG_FUNCTION_ARGS) { DateADT dateVal = PG_GETARG_DATEADT(0); @@ -843,6 +877,30 @@ date_cmp_timestamptz(PG_FUNCTION_ARGS) } Datum +date_dist_timestamptz(PG_FUNCTION_ARGS) +{ + DateADT dateVal = PG_GETARG_DATEADT(0); + TimestampTz dt2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz dt1; + + if (DATE_NOT_FINITE(dateVal) || TIMESTAMP_NOT_FINITE(dt2)) + { + Interval *r = palloc(sizeof(Interval)); + + r->day = INT_MAX; + r->month = INT_MAX; + r->time = PG_INT64_MAX; + + PG_RETURN_INTERVAL_P(r); + } + + dt1 = date2timestamptz(dateVal); + + PG_RETURN_INTERVAL_P(timestamptz_dist_internal(dt1, dt2)); +} + + +Datum timestamp_eq_date(PG_FUNCTION_ARGS) { Timestamp dt1 = PG_GETARG_TIMESTAMP(0); @@ -927,6 +985,29 @@ timestamp_cmp_date(PG_FUNCTION_ARGS) } Datum +timestamp_dist_date(PG_FUNCTION_ARGS) +{ + Timestamp dt1 = PG_GETARG_TIMESTAMP(0); + DateADT dateVal = PG_GETARG_DATEADT(1); + Timestamp dt2; + + if (DATE_NOT_FINITE(dateVal) || TIMESTAMP_NOT_FINITE(dt1)) + { + Interval *r = palloc(sizeof(Interval)); + + r->day = INT_MAX; + r->month = INT_MAX; + r->time = PG_INT64_MAX; + + PG_RETURN_INTERVAL_P(r); + } + + dt2 = date2timestamp(dateVal); + + PG_RETURN_INTERVAL_P(timestamp_dist_internal(dt1, dt2)); +} + +Datum timestamptz_eq_date(PG_FUNCTION_ARGS) { TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); @@ -1038,6 +1119,28 @@ in_range_date_interval(PG_FUNCTION_ARGS) BoolGetDatum(less)); } +Datum +timestamptz_dist_date(PG_FUNCTION_ARGS) +{ + TimestampTz dt1 = PG_GETARG_TIMESTAMPTZ(0); + DateADT dateVal = PG_GETARG_DATEADT(1); + TimestampTz dt2; + + if (DATE_NOT_FINITE(dateVal) || TIMESTAMP_NOT_FINITE(dt1)) + { + Interval *r = palloc(sizeof(Interval)); + + r->day = INT_MAX; + r->month = INT_MAX; + r->time = PG_INT64_MAX; + + PG_RETURN_INTERVAL_P(r); + } + + dt2 = date2timestamptz(dateVal); + + PG_RETURN_INTERVAL_P(timestamptz_dist_internal(dt1, dt2)); +} /* Add an interval to a date, giving a new date. * Must handle both positive and negative intervals. @@ -1946,6 +2049,16 @@ time_part(PG_FUNCTION_ARGS) PG_RETURN_FLOAT8(result); } +Datum +time_dist(PG_FUNCTION_ARGS) +{ + Datum diff = DirectFunctionCall2(time_mi_time, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INTERVAL_P(abs_interval(DatumGetIntervalP(diff))); +} + /***************************************************************************** * Time With Time Zone ADT diff --git a/src/backend/utils/adt/float.c b/src/backend/utils/adt/float.c index cf9327f..f8c2702 100644 --- a/src/backend/utils/adt/float.c +++ b/src/backend/utils/adt/float.c @@ -3641,6 +3641,47 @@ width_bucket_float8(PG_FUNCTION_ARGS) PG_RETURN_INT32(result); } +Datum +float4_dist(PG_FUNCTION_ARGS) +{ + float4 a = PG_GETARG_FLOAT4(0); + float4 b = PG_GETARG_FLOAT4(1); + float4 r = float4_mi(a, b); + + PG_RETURN_FLOAT4(Abs(r)); +} + +Datum +float8_dist(PG_FUNCTION_ARGS) +{ + float8 a = PG_GETARG_FLOAT8(0); + float8 b = PG_GETARG_FLOAT8(1); + float8 r = float8_mi(a, b); + + PG_RETURN_FLOAT8(Abs(r)); +} + + +Datum +float48_dist(PG_FUNCTION_ARGS) +{ + float4 a = PG_GETARG_FLOAT4(0); + float8 b = PG_GETARG_FLOAT8(1); + float8 r = float8_mi(a, b); + + PG_RETURN_FLOAT8(Abs(r)); +} + +Datum +float84_dist(PG_FUNCTION_ARGS) +{ + float8 a = PG_GETARG_FLOAT8(0); + float4 b = PG_GETARG_FLOAT4(1); + float8 r = float8_mi(a, b); + + PG_RETURN_FLOAT8(Abs(r)); +} + /* ========== PRIVATE ROUTINES ========== */ #ifndef HAVE_CBRT diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c index 8149dc1..9335746 100644 --- a/src/backend/utils/adt/int.c +++ b/src/backend/utils/adt/int.c @@ -1427,3 +1427,55 @@ generate_series_step_int4(PG_FUNCTION_ARGS) /* do when there is no more left */ SRF_RETURN_DONE(funcctx); } + +Datum +int2_dist(PG_FUNCTION_ARGS) +{ + int16 a = PG_GETARG_INT16(0); + int16 b = PG_GETARG_INT16(1); + int16 r; + int16 ra; + + if (pg_sub_s16_overflow(a, b, &r) || + r == PG_INT16_MIN) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("smallint out of range"))); + + ra = Abs(r); + + PG_RETURN_INT16(ra); +} + +static int32 +int44_dist(int32 a, int32 b) +{ + int32 r; + + if (pg_sub_s32_overflow(a, b, &r) || + r == PG_INT32_MIN) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("integer out of range"))); + + return Abs(r); +} + + +Datum +int4_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(int44_dist(PG_GETARG_INT32(0), PG_GETARG_INT32(1))); +} + +Datum +int24_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(int44_dist(PG_GETARG_INT16(0), PG_GETARG_INT32(1))); +} + +Datum +int42_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT32(int44_dist(PG_GETARG_INT32(0), PG_GETARG_INT16(1))); +} diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c index 437d418..8bf8eda 100644 --- a/src/backend/utils/adt/int8.c +++ b/src/backend/utils/adt/int8.c @@ -1373,3 +1373,47 @@ generate_series_step_int8(PG_FUNCTION_ARGS) /* do when there is no more left */ SRF_RETURN_DONE(funcctx); } + +static int64 +int88_dist(int64 a, int64 b) +{ + int64 r; + + if (pg_sub_s64_overflow(a, b, &r) || + r == PG_INT64_MIN) + ereport(ERROR, + (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), + errmsg("bigint out of range"))); + + return Abs(r); +} + +Datum +int8_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(int88_dist(PG_GETARG_INT64(0), PG_GETARG_INT64(1))); +} + +Datum +int82_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(int88_dist(PG_GETARG_INT64(0), (int64) PG_GETARG_INT16(1))); +} + +Datum +int84_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(int88_dist(PG_GETARG_INT64(0), (int64) PG_GETARG_INT32(1))); +} + +Datum +int28_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(int88_dist((int64) PG_GETARG_INT16(0), PG_GETARG_INT64(1))); +} + +Datum +int48_dist(PG_FUNCTION_ARGS) +{ + PG_RETURN_INT64(int88_dist((int64) PG_GETARG_INT32(0), PG_GETARG_INT64(1))); +} diff --git a/src/backend/utils/adt/oid.c b/src/backend/utils/adt/oid.c index b0670e0..536507b 100644 --- a/src/backend/utils/adt/oid.c +++ b/src/backend/utils/adt/oid.c @@ -469,3 +469,17 @@ oidvectorgt(PG_FUNCTION_ARGS) PG_RETURN_BOOL(cmp > 0); } + +Datum +oid_dist(PG_FUNCTION_ARGS) +{ + Oid a = PG_GETARG_OID(0); + Oid b = PG_GETARG_OID(1); + Oid res; + + if (a < b) + res = b - a; + else + res = a - b; + PG_RETURN_OID(res); +} diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index b377c38..b536645 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -2672,6 +2672,86 @@ timestamp_mi(PG_FUNCTION_ARGS) PG_RETURN_INTERVAL_P(result); } +Datum +timestamp_dist(PG_FUNCTION_ARGS) +{ + Timestamp a = PG_GETARG_TIMESTAMP(0); + Timestamp b = PG_GETARG_TIMESTAMP(1); + Interval *r; + + if (TIMESTAMP_NOT_FINITE(a) || TIMESTAMP_NOT_FINITE(b)) + { + Interval *p = palloc(sizeof(Interval)); + + p->day = INT_MAX; + p->month = INT_MAX; + p->time = PG_INT64_MAX; + PG_RETURN_INTERVAL_P(p); + } + else + r = DatumGetIntervalP(DirectFunctionCall2(timestamp_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1))); + PG_RETURN_INTERVAL_P(abs_interval(r)); +} + +Interval * +timestamp_dist_internal(Timestamp a, Timestamp b) +{ + Interval *r; + + if (TIMESTAMP_NOT_FINITE(a) || TIMESTAMP_NOT_FINITE(b)) + { + r = palloc(sizeof(Interval)); + + r->day = INT_MAX; + r->month = INT_MAX; + r->time = PG_INT64_MAX; + + return r; + } + + r = DatumGetIntervalP(DirectFunctionCall2(timestamp_mi, + TimestampGetDatum(a), + TimestampGetDatum(b))); + + return abs_interval(r); +} + +Datum +timestamptz_dist(PG_FUNCTION_ARGS) +{ + TimestampTz a = PG_GETARG_TIMESTAMPTZ(0); + TimestampTz b = PG_GETARG_TIMESTAMPTZ(1); + + PG_RETURN_INTERVAL_P(timestamp_dist_internal(a, b)); +} + +Datum +timestamp_dist_timestamptz(PG_FUNCTION_ARGS) +{ + Timestamp ts1 = PG_GETARG_TIMESTAMP(0); + TimestampTz tstz2 = PG_GETARG_TIMESTAMPTZ(1); + TimestampTz tstz1; + + tstz1 = timestamp2timestamptz(ts1); + + PG_RETURN_INTERVAL_P(timestamp_dist_internal(tstz1, tstz2)); +} + +Datum +timestamptz_dist_timestamp(PG_FUNCTION_ARGS) +{ + TimestampTz tstz1 = PG_GETARG_TIMESTAMPTZ(0); + Timestamp ts2 = PG_GETARG_TIMESTAMP(1); + TimestampTz tstz2; + + tstz2 = timestamp2timestamptz(ts2); + + PG_RETURN_INTERVAL_P(timestamp_dist_internal(tstz1, tstz2)); +} + + /* * interval_justify_interval() * @@ -3541,6 +3621,29 @@ interval_avg(PG_FUNCTION_ARGS) Float8GetDatum((double) N.time)); } +Interval * +abs_interval(Interval *a) +{ + static Interval zero = {0, 0, 0}; + + if (DatumGetBool(DirectFunctionCall2(interval_lt, + IntervalPGetDatum(a), + IntervalPGetDatum(&zero)))) + a = DatumGetIntervalP(DirectFunctionCall1(interval_um, + IntervalPGetDatum(a))); + + return a; +} + +Datum +interval_dist(PG_FUNCTION_ARGS) +{ + Datum diff = DirectFunctionCall2(interval_mi, + PG_GETARG_DATUM(0), + PG_GETARG_DATUM(1)); + + PG_RETURN_INTERVAL_P(abs_interval(DatumGetIntervalP(diff))); +} /* timestamp_age() * Calculate time difference while retaining year/month fields. diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index 075a54c..ded5951 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -30,6 +30,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int2', amoprighttype => 'int2', amopstrategy => '5', amopopr => '>(int2,int2)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int2', + amoprighttype => 'int2', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int2,int2)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int24 { amopfamily => 'btree/integer_ops', amoplefttype => 'int2', @@ -47,6 +51,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int2', amoprighttype => 'int4', amopstrategy => '5', amopopr => '>(int2,int4)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int2', + amoprighttype => 'int4', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int2,int4)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int28 { amopfamily => 'btree/integer_ops', amoplefttype => 'int2', @@ -64,6 +72,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int2', amoprighttype => 'int8', amopstrategy => '5', amopopr => '>(int2,int8)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int2', + amoprighttype => 'int8', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int2,int8)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # default operators int4 { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', @@ -81,6 +93,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', amoprighttype => 'int4', amopstrategy => '5', amopopr => '>(int4,int4)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int4', + amoprighttype => 'int4', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int4,int4)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int42 { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', @@ -98,6 +114,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', amoprighttype => 'int2', amopstrategy => '5', amopopr => '>(int4,int2)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int4', + amoprighttype => 'int2', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int4,int2)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int48 { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', @@ -115,6 +135,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int4', amoprighttype => 'int8', amopstrategy => '5', amopopr => '>(int4,int8)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int4', + amoprighttype => 'int8', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int4,int8)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # default operators int8 { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', @@ -132,6 +156,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', amoprighttype => 'int8', amopstrategy => '5', amopopr => '>(int8,int8)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int8', + amoprighttype => 'int8', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int8,int8)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int82 { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', @@ -149,6 +177,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', amoprighttype => 'int2', amopstrategy => '5', amopopr => '>(int8,int2)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int8', + amoprighttype => 'int2', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int8,int2)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators int84 { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', @@ -166,6 +198,10 @@ { amopfamily => 'btree/integer_ops', amoplefttype => 'int8', amoprighttype => 'int4', amopstrategy => '5', amopopr => '>(int8,int4)', amopmethod => 'btree' }, +{ amopfamily => 'btree/integer_ops', amoplefttype => 'int8', + amoprighttype => 'int4', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(int8,int4)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # btree oid_ops @@ -179,6 +215,10 @@ amopstrategy => '4', amopopr => '>=(oid,oid)', amopmethod => 'btree' }, { amopfamily => 'btree/oid_ops', amoplefttype => 'oid', amoprighttype => 'oid', amopstrategy => '5', amopopr => '>(oid,oid)', amopmethod => 'btree' }, +{ amopfamily => 'btree/oid_ops', amoplefttype => 'oid', + amoprighttype => 'oid', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(oid,oid)', amopmethod => 'btree', + amopsortfamily => 'btree/oid_ops' }, # btree tid_ops @@ -229,6 +269,10 @@ { amopfamily => 'btree/float_ops', amoplefttype => 'float4', amoprighttype => 'float4', amopstrategy => '5', amopopr => '>(float4,float4)', amopmethod => 'btree' }, +{ amopfamily => 'btree/float_ops', amoplefttype => 'float4', + amoprighttype => 'float4', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(float4,float4)', amopmethod => 'btree', + amopsortfamily => 'btree/float_ops' }, # crosstype operators float48 { amopfamily => 'btree/float_ops', amoplefttype => 'float4', @@ -246,6 +290,10 @@ { amopfamily => 'btree/float_ops', amoplefttype => 'float4', amoprighttype => 'float8', amopstrategy => '5', amopopr => '>(float4,float8)', amopmethod => 'btree' }, +{ amopfamily => 'btree/float_ops', amoplefttype => 'float4', + amoprighttype => 'float8', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(float4,float8)', amopmethod => 'btree', + amopsortfamily => 'btree/float_ops' }, # default operators float8 { amopfamily => 'btree/float_ops', amoplefttype => 'float8', @@ -263,6 +311,10 @@ { amopfamily => 'btree/float_ops', amoplefttype => 'float8', amoprighttype => 'float8', amopstrategy => '5', amopopr => '>(float8,float8)', amopmethod => 'btree' }, +{ amopfamily => 'btree/float_ops', amoplefttype => 'float8', + amoprighttype => 'float8', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(float8,float8)', amopmethod => 'btree', + amopsortfamily => 'btree/float_ops' }, # crosstype operators float84 { amopfamily => 'btree/float_ops', amoplefttype => 'float8', @@ -280,6 +332,10 @@ { amopfamily => 'btree/float_ops', amoplefttype => 'float8', amoprighttype => 'float4', amopstrategy => '5', amopopr => '>(float8,float4)', amopmethod => 'btree' }, +{ amopfamily => 'btree/float_ops', amoplefttype => 'float8', + amoprighttype => 'float4', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(float8,float4)', amopmethod => 'btree', + amopsortfamily => 'btree/float_ops' }, # btree char_ops @@ -389,6 +445,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'date', amoprighttype => 'date', amopstrategy => '5', amopopr => '>(date,date)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'date', + amoprighttype => 'date', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(date,date)', amopmethod => 'btree', + amopsortfamily => 'btree/integer_ops' }, # crosstype operators vs timestamp { amopfamily => 'btree/datetime_ops', amoplefttype => 'date', @@ -406,6 +466,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'date', amoprighttype => 'timestamp', amopstrategy => '5', amopopr => '>(date,timestamp)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'date', + amoprighttype => 'timestamp', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(date,timestamp)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # crosstype operators vs timestamptz { amopfamily => 'btree/datetime_ops', amoplefttype => 'date', @@ -423,6 +487,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'date', amoprighttype => 'timestamptz', amopstrategy => '5', amopopr => '>(date,timestamptz)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'date', + amoprighttype => 'timestamptz', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(date,timestamptz)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # default operators timestamp { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', @@ -440,6 +508,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', amoprighttype => 'timestamp', amopstrategy => '5', amopopr => '>(timestamp,timestamp)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', + amoprighttype => 'timestamp', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamp,timestamp)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # crosstype operators vs date { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', @@ -457,6 +529,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', amoprighttype => 'date', amopstrategy => '5', amopopr => '>(timestamp,date)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', + amoprighttype => 'date', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamp,date)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # crosstype operators vs timestamptz { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', @@ -474,6 +550,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', amoprighttype => 'timestamptz', amopstrategy => '5', amopopr => '>(timestamp,timestamptz)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamp', + amoprighttype => 'timestamptz', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamp,timestamptz)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # default operators timestamptz { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', @@ -491,6 +571,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', amoprighttype => 'timestamptz', amopstrategy => '5', amopopr => '>(timestamptz,timestamptz)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', + amoprighttype => 'timestamptz', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamptz,timestamptz)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # crosstype operators vs date { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', @@ -508,6 +592,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', amoprighttype => 'date', amopstrategy => '5', amopopr => '>(timestamptz,date)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', + amoprighttype => 'date', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamptz,date)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # crosstype operators vs timestamp { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', @@ -525,6 +613,10 @@ { amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', amoprighttype => 'timestamp', amopstrategy => '5', amopopr => '>(timestamptz,timestamp)', amopmethod => 'btree' }, +{ amopfamily => 'btree/datetime_ops', amoplefttype => 'timestamptz', + amoprighttype => 'timestamp', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(timestamptz,timestamp)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # btree time_ops @@ -543,6 +635,10 @@ { amopfamily => 'btree/time_ops', amoplefttype => 'time', amoprighttype => 'time', amopstrategy => '5', amopopr => '>(time,time)', amopmethod => 'btree' }, +{ amopfamily => 'btree/time_ops', amoplefttype => 'time', + amoprighttype => 'time', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(time,time)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # btree timetz_ops @@ -579,6 +675,10 @@ { amopfamily => 'btree/interval_ops', amoplefttype => 'interval', amoprighttype => 'interval', amopstrategy => '5', amopopr => '>(interval,interval)', amopmethod => 'btree' }, +{ amopfamily => 'btree/interval_ops', amoplefttype => 'interval', + amoprighttype => 'interval', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(interval,interval)', amopmethod => 'btree', + amopsortfamily => 'btree/interval_ops' }, # btree macaddr @@ -754,6 +854,10 @@ { amopfamily => 'btree/money_ops', amoplefttype => 'money', amoprighttype => 'money', amopstrategy => '5', amopopr => '>(money,money)', amopmethod => 'btree' }, +{ amopfamily => 'btree/money_ops', amoplefttype => 'money', + amoprighttype => 'money', amopstrategy => '6', amoppurpose => 'o', + amopopr => '<->(money,money)', amopmethod => 'btree', + amopsortfamily => 'btree/money_ops' }, # btree array_ops diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat index ce23c2f..a2ae8cb 100644 --- a/src/include/catalog/pg_operator.dat +++ b/src/include/catalog/pg_operator.dat @@ -2791,6 +2791,114 @@ oprname => '-', oprleft => 'pg_lsn', oprright => 'pg_lsn', oprresult => 'numeric', oprcode => 'pg_lsn_mi' }, +# distance operators +{ oid => '4217', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int2', + oprright => 'int2', oprresult => 'int2', oprcom => '<->(int2,int2)', + oprcode => 'int2_dist'}, +{ oid => '4218', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int4', + oprright => 'int4', oprresult => 'int4', oprcom => '<->(int4,int4)', + oprcode => 'int4_dist'}, +{ oid => '4219', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int8', + oprright => 'int8', oprresult => 'int8', oprcom => '<->(int8,int8)', + oprcode => 'int8_dist'}, +{ oid => '4220', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'oid', + oprright => 'oid', oprresult => 'oid', oprcom => '<->(oid,oid)', + oprcode => 'oid_dist'}, +{ oid => '4221', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'float4', + oprright => 'float4', oprresult => 'float4', oprcom => '<->(float4,float4)', + oprcode => 'float4_dist'}, +{ oid => '4222', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'float8', + oprright => 'float8', oprresult => 'float8', oprcom => '<->(float8,float8)', + oprcode => 'float8_dist'}, +{ oid => '4223', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'money', + oprright => 'money', oprresult => 'money', oprcom => '<->(money,money)', + oprcode => 'cash_dist'}, +{ oid => '4224', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'date', + oprright => 'date', oprresult => 'int4', oprcom => '<->(date,date)', + oprcode => 'date_dist'}, +{ oid => '4225', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'time', + oprright => 'time', oprresult => 'interval', oprcom => '<->(time,time)', + oprcode => 'time_dist'}, +{ oid => '4226', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamp', + oprright => 'timestamp', oprresult => 'interval', oprcom => '<->(timestamp,timestamp)', + oprcode => 'timestamp_dist'}, +{ oid => '4227', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamptz', + oprright => 'timestamptz', oprresult => 'interval', oprcom => '<->(timestamptz,timestamptz)', + oprcode => 'timestamptz_dist'}, +{ oid => '4228', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'interval', + oprright => 'interval', oprresult => 'interval', oprcom => '<->(interval,interval)', + oprcode => 'interval_dist'}, + +# cross-type distance operators +{ oid => '4229', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int2', + oprright => 'int4', oprresult => 'int4', oprcom => '<->(int4,int2)', + oprcode => 'int24_dist'}, +{ oid => '4230', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int4', + oprright => 'int2', oprresult => 'int4', oprcom => '<->(int2,int4)', + oprcode => 'int42_dist'}, +{ oid => '4231', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int2', + oprright => 'int8', oprresult => 'int8', oprcom => '<->(int8,int2)', + oprcode => 'int28_dist'}, +{ oid => '4232', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int8', + oprright => 'int2', oprresult => 'int8', oprcom => '<->(int2,int8)', + oprcode => 'int82_dist'}, +{ oid => '4233', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int4', + oprright => 'int8', oprresult => 'int8', oprcom => '<->(int8,int4)', + oprcode => 'int48_dist'}, +{ oid => '4234', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'int8', + oprright => 'int4', oprresult => 'int8', oprcom => '<->(int4,int8)', + oprcode => 'int84_dist'}, +{ oid => '4235', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'float4', + oprright => 'float8', oprresult => 'float8', oprcom => '<->(float8,float4)', + oprcode => 'float48_dist'}, +{ oid => '4236', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'float8', + oprright => 'float4', oprresult => 'float8', oprcom => '<->(float4,float8)', + oprcode => 'float84_dist'}, +{ oid => '4237', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'date', + oprright => 'timestamp', oprresult => 'interval', oprcom => '<->(timestamp,date)', + oprcode => 'date_dist_timestamp'}, +{ oid => '4238', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamp', + oprright => 'date', oprresult => 'interval', oprcom => '<->(date,timestamp)', + oprcode => 'timestamp_dist_date'}, +{ oid => '4239', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'date', + oprright => 'timestamptz', oprresult => 'interval', oprcom => '<->(timestamptz,date)', + oprcode => 'date_dist_timestamptz'}, +{ oid => '4240', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamptz', + oprright => 'date', oprresult => 'interval', oprcom => '<->(date,timestamptz)', + oprcode => 'timestamptz_dist_date'}, +{ oid => '4241', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamp', + oprright => 'timestamptz', oprresult => 'interval', oprcom => '<->(timestamptz,timestamp)', + oprcode => 'timestamp_dist_timestamptz'}, +{ oid => '4242', descr => 'distance between', + oprname => '<->', oprcanmerge => 'f', oprcanhash => 'f', oprleft => 'timestamptz', + oprright => 'timestamp', oprresult => 'interval', oprcom => '<->(timestamp,timestamptz)', + oprcode => 'timestamptz_dist_timestamp'}, + # enum operators { oid => '3516', descr => 'equal', oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'anyenum', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 034a41e..ec9ff16 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10048,4 +10048,86 @@ proargnames => '{rootrelid,relid,parentrelid,isleaf,level}', prosrc => 'pg_partition_tree' }, +# distance functions +{ oid => '4243', + proname => 'int2_dist', prorettype => 'int2', + proargtypes => 'int2 int2', prosrc => 'int2_dist' }, +{ oid => '4244', + proname => 'int4_dist', prorettype => 'int4', + proargtypes => 'int4 int4', prosrc => 'int4_dist' }, +{ oid => '4245', + proname => 'int8_dist', prorettype => 'int8', + proargtypes => 'int8 int8', prosrc => 'int8_dist' }, +{ oid => '4246', + proname => 'oid_dist', prorettype => 'oid', + proargtypes => 'oid oid', prosrc => 'oid_dist' }, +{ oid => '4247', + proname => 'float4_dist', prorettype => 'float4', + proargtypes => 'float4 float4', prosrc => 'float4_dist' }, +{ oid => '4248', + proname => 'float8_dist', prorettype => 'float8', + proargtypes => 'float8 float8', prosrc => 'float8_dist' }, +{ oid => '4249', + proname => 'cash_dist', prorettype => 'money', + proargtypes => 'money money', prosrc => 'cash_dist' }, +{ oid => '4250', + proname => 'date_dist', prorettype => 'int4', + proargtypes => 'date date', prosrc => 'date_dist' }, +{ oid => '4251', + proname => 'time_dist', prorettype => 'interval', + proargtypes => 'time time', prosrc => 'time_dist' }, +{ oid => '4252', + proname => 'timestamp_dist', prorettype => 'interval', + proargtypes => 'timestamp timestamp', prosrc => 'timestamp_dist' }, +{ oid => '4253', + proname => 'timestamptz_dist', prorettype => 'interval', + proargtypes => 'timestamptz timestamptz', prosrc => 'timestamptz_dist' }, +{ oid => '4254', + proname => 'interval_dist', prorettype => 'interval', + proargtypes => 'interval interval', prosrc => 'interval_dist' }, + +# cross-type distance functions +{ oid => '4255', + proname => 'int24_dist', prorettype => 'int4', + proargtypes => 'int2 int4', prosrc => 'int24_dist' }, +{ oid => '4256', + proname => 'int28_dist', prorettype => 'int8', + proargtypes => 'int2 int8', prosrc => 'int28_dist' }, +{ oid => '4257', + proname => 'int42_dist', prorettype => 'int4', + proargtypes => 'int4 int2', prosrc => 'int42_dist' }, +{ oid => '4258', + proname => 'int48_dist', prorettype => 'int8', + proargtypes => 'int4 int8', prosrc => 'int48_dist' }, +{ oid => '4259', + proname => 'int82_dist', prorettype => 'int8', + proargtypes => 'int8 int2', prosrc => 'int82_dist' }, +{ oid => '4260', + proname => 'int84_dist', prorettype => 'int8', + proargtypes => 'int8 int4', prosrc => 'int84_dist' }, +{ oid => '4261', + proname => 'float48_dist', prorettype => 'float8', + proargtypes => 'float4 float8', prosrc => 'float48_dist' }, +{ oid => '4262', + proname => 'float84_dist', prorettype => 'float8', + proargtypes => 'float8 float4', prosrc => 'float84_dist' }, +{ oid => '4263', + proname => 'date_dist_timestamp', prorettype => 'interval', + proargtypes => 'date timestamp', prosrc => 'date_dist_timestamp' }, +{ oid => '4264', + proname => 'date_dist_timestamptz', prorettype => 'interval', + proargtypes => 'date timestamptz', prosrc => 'date_dist_timestamptz' }, +{ oid => '4265', + proname => 'timestamp_dist_date', prorettype => 'interval', + proargtypes => 'timestamp date', prosrc => 'timestamp_dist_date' }, +{ oid => '4266', + proname => 'timestamp_dist_timestamptz', prorettype => 'interval', + proargtypes => 'timestamp timestamptz', prosrc => 'timestamp_dist_timestamptz' }, +{ oid => '4267', + proname => 'timestamptz_dist_date', prorettype => 'interval', + proargtypes => 'timestamptz date', prosrc => 'timestamptz_dist_date' }, +{ oid => '4268', + proname => 'timestamptz_dist_timestamp', prorettype => 'interval', + proargtypes => 'timestamptz timestamp', prosrc => 'timestamptz_dist_timestamp' }, + ] diff --git a/src/include/utils/datetime.h b/src/include/utils/datetime.h index de9e9ad..6f8062d 100644 --- a/src/include/utils/datetime.h +++ b/src/include/utils/datetime.h @@ -338,4 +338,6 @@ extern TimeZoneAbbrevTable *ConvertTimeZoneAbbrevs(struct tzEntry *abbrevs, int n); extern void InstallTimeZoneAbbrevs(TimeZoneAbbrevTable *tbl); +extern Interval *abs_interval(Interval *a); + #endif /* DATETIME_H */ diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index 2b3b357..1fa56e5 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -93,9 +93,11 @@ extern Timestamp SetEpochTimestamp(void); extern void GetEpochTime(struct pg_tm *tm); extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); +extern Interval *timestamp_dist_internal(Timestamp a, Timestamp b); -/* timestamp comparison works for timestamptz also */ +/* timestamp comparison and distance works for timestamptz also */ #define timestamptz_cmp_internal(dt1,dt2) timestamp_cmp_internal(dt1, dt2) +#define timestamptz_dist_internal(dt1,dt2) timestamp_dist_internal(dt1, dt2) extern int isoweek2j(int year, int week); extern void isoweek2date(int woy, int *year, int *mon, int *mday); diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out index 4570a39..630dc6b 100644 --- a/src/test/regress/expected/amutils.out +++ b/src/test/regress/expected/amutils.out @@ -24,7 +24,7 @@ select prop, nulls_first | | | f nulls_last | | | t orderable | | | t - distance_orderable | | | f + distance_orderable | | | t returnable | | | t search_array | | | t search_nulls | | | t @@ -100,7 +100,7 @@ select prop, nulls_first | f | f | f | f | f | f | f nulls_last | t | f | f | f | f | f | f orderable | t | f | f | f | f | f | f - distance_orderable | f | f | t | f | t | f | f + distance_orderable | t | f | t | f | t | f | f returnable | t | f | f | t | t | f | f search_array | t | f | f | f | f | f | f search_nulls | t | f | t | t | t | f | t @@ -231,7 +231,7 @@ select col, prop, pg_index_column_has_property(o, col, prop) 1 | desc | f 1 | nulls_first | f 1 | nulls_last | t - 1 | distance_orderable | f + 1 | distance_orderable | t 1 | returnable | t 1 | bogus | 2 | orderable | f diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 1bcc946..b9b819c 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -1477,3 +1477,64 @@ select make_time(10, 55, 100.1); ERROR: time field value out of range: 10:55:100.1 select make_time(24, 0, 2.1); ERROR: time field value out of range: 24:00:2.1 +-- distance operators +SELECT '' AS "Fifteen", f1 <-> date '2001-02-03' AS "Distance" FROM DATE_TBL; + Fifteen | Distance +---------+---------- + | 16006 + | 15941 + | 1802 + | 1801 + | 1800 + | 1799 + | 1436 + | 1435 + | 1434 + | 308 + | 307 + | 306 + | 13578 + | 13944 + | 14311 +(15 rows) + +SELECT '' AS "Fifteen", f1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM DATE_TBL; + Fifteen | Distance +---------+--------------------------------------- + | @ 16006 days 1 hour 23 mins 45 secs + | @ 15941 days 1 hour 23 mins 45 secs + | @ 1802 days 1 hour 23 mins 45 secs + | @ 1801 days 1 hour 23 mins 45 secs + | @ 1800 days 1 hour 23 mins 45 secs + | @ 1799 days 1 hour 23 mins 45 secs + | @ 1436 days 1 hour 23 mins 45 secs + | @ 1435 days 1 hour 23 mins 45 secs + | @ 1434 days 1 hour 23 mins 45 secs + | @ 308 days 1 hour 23 mins 45 secs + | @ 307 days 1 hour 23 mins 45 secs + | @ 306 days 1 hour 23 mins 45 secs + | @ 13577 days 22 hours 36 mins 15 secs + | @ 13943 days 22 hours 36 mins 15 secs + | @ 14310 days 22 hours 36 mins 15 secs +(15 rows) + +SELECT '' AS "Fifteen", f1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM DATE_TBL; + Fifteen | Distance +---------+--------------------------------------- + | @ 16005 days 14 hours 23 mins 45 secs + | @ 15940 days 14 hours 23 mins 45 secs + | @ 1801 days 14 hours 23 mins 45 secs + | @ 1800 days 14 hours 23 mins 45 secs + | @ 1799 days 14 hours 23 mins 45 secs + | @ 1798 days 14 hours 23 mins 45 secs + | @ 1435 days 14 hours 23 mins 45 secs + | @ 1434 days 14 hours 23 mins 45 secs + | @ 1433 days 14 hours 23 mins 45 secs + | @ 307 days 14 hours 23 mins 45 secs + | @ 306 days 14 hours 23 mins 45 secs + | @ 305 days 15 hours 23 mins 45 secs + | @ 13578 days 8 hours 36 mins 15 secs + | @ 13944 days 8 hours 36 mins 15 secs + | @ 14311 days 8 hours 36 mins 15 secs +(15 rows) + diff --git a/src/test/regress/expected/float4.out b/src/test/regress/expected/float4.out index 2f47e1c..a9dddc4 100644 --- a/src/test/regress/expected/float4.out +++ b/src/test/regress/expected/float4.out @@ -257,6 +257,26 @@ SELECT '' AS five, * FROM FLOAT4_TBL; | -1.23457e-20 (5 rows) +SELECT '' AS five, f.f1, f.f1 <-> '1004.3' AS dist FROM FLOAT4_TBL f; + five | f1 | dist +------+--------------+------------- + | 0 | 1004.3 + | -34.84 | 1039.14 + | -1004.3 | 2008.6 + | -1.23457e+20 | 1.23457e+20 + | -1.23457e-20 | 1004.3 +(5 rows) + +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float8 AS dist FROM FLOAT4_TBL f; + five | f1 | dist +------+--------------+---------------------- + | 0 | 1004.3 + | -34.84 | 1039.14000015259 + | -1004.3 | 2008.59998779297 + | -1.23457e+20 | 1.23456789557014e+20 + | -1.23457e-20 | 1004.3 +(5 rows) + -- test edge-case coercions to integer SELECT '32767.4'::float4::int2; int2 diff --git a/src/test/regress/expected/float8.out b/src/test/regress/expected/float8.out index 75c0bf3..abf9ef1 100644 --- a/src/test/regress/expected/float8.out +++ b/src/test/regress/expected/float8.out @@ -404,6 +404,27 @@ SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; | 1.2345678901234e-200 | 2.3112042409018e-67 (5 rows) +-- distance +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float8 AS dist FROM FLOAT8_TBL f; + five | f1 | dist +------+----------------------+---------------------- + | 0 | 1004.3 + | 1004.3 | 0 + | -34.84 | 1039.14 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 1004.3 +(5 rows) + +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float4 AS dist FROM FLOAT8_TBL f; + five | f1 | dist +------+----------------------+---------------------- + | 0 | 1004.29998779297 + | 1004.3 | 1.22070312045253e-05 + | -34.84 | 1039.13998779297 + | 1.2345678901234e+200 | 1.2345678901234e+200 + | 1.2345678901234e-200 | 1004.29998779297 +(5 rows) + SELECT '' AS five, * FROM FLOAT8_TBL; five | f1 ------+---------------------- diff --git a/src/test/regress/expected/int2.out b/src/test/regress/expected/int2.out index 8c255b9..0edc57e 100644 --- a/src/test/regress/expected/int2.out +++ b/src/test/regress/expected/int2.out @@ -242,6 +242,39 @@ SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i; | -32767 | -16383 (5 rows) +-- distance +SELECT '' AS five, i.f1, i.f1 <-> int2 '2' AS x FROM INT2_TBL i; +ERROR: smallint out of range +SELECT '' AS four, i.f1, i.f1 <-> int2 '2' AS x FROM INT2_TBL i +WHERE f1 > -32767; + four | f1 | x +------+-------+------- + | 0 | 2 + | 1234 | 1232 + | -1234 | 1236 + | 32767 | 32765 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 <-> int4 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+------- + | 0 | 2 + | 1234 | 1232 + | -1234 | 1236 + | 32767 | 32765 + | -32767 | 32769 +(5 rows) + +SELECT '' AS five, i.f1, i.f1 <-> int8 '2' AS x FROM INT2_TBL i; + five | f1 | x +------+--------+------- + | 0 | 2 + | 1234 | 1232 + | -1234 | 1236 + | 32767 | 32765 + | -32767 | 32769 +(5 rows) + -- corner cases SELECT (-1::int2<<15)::text; text diff --git a/src/test/regress/expected/int4.out b/src/test/regress/expected/int4.out index bda7a8d..3735dbc 100644 --- a/src/test/regress/expected/int4.out +++ b/src/test/regress/expected/int4.out @@ -247,6 +247,38 @@ SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i; | -2147483647 | -1073741823 (5 rows) +SELECT '' AS five, i.f1, i.f1 <-> int2 '2' AS x FROM INT4_TBL i; +ERROR: integer out of range +SELECT '' AS four, i.f1, i.f1 <-> int2 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + four | f1 | x +------+------------+------------ + | 0 | 2 + | 123456 | 123454 + | -123456 | 123458 + | 2147483647 | 2147483645 +(4 rows) + +SELECT '' AS four, i.f1, i.f1 <-> int4 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + four | f1 | x +------+------------+------------ + | 0 | 2 + | 123456 | 123454 + | -123456 | 123458 + | 2147483647 | 2147483645 +(4 rows) + +SELECT '' AS five, i.f1, i.f1 <-> int8 '2' AS x FROM INT4_TBL i; + five | f1 | x +------+-------------+------------ + | 0 | 2 + | 123456 | 123454 + | -123456 | 123458 + | 2147483647 | 2147483645 + | -2147483647 | 2147483649 +(5 rows) + -- -- more complex expressions -- diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out index 35e3b3f..8940e81 100644 --- a/src/test/regress/expected/int8.out +++ b/src/test/regress/expected/int8.out @@ -432,6 +432,37 @@ SELECT 246::int2 + q1 AS "2plus8", 246::int2 - q1 AS "2minus8", 246::int2 * q1 A 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0 (5 rows) +-- distance +SELECT '' AS five, q2, q2 <-> int2 '123' AS dist FROM INT8_TBL i; + five | q2 | dist +------+-------------------+------------------ + | 456 | 333 + | 4567890123456789 | 4567890123456666 + | 123 | 0 + | 4567890123456789 | 4567890123456666 + | -4567890123456789 | 4567890123456912 +(5 rows) + +SELECT '' AS five, q2, q2 <-> int4 '123' AS dist FROM INT8_TBL i; + five | q2 | dist +------+-------------------+------------------ + | 456 | 333 + | 4567890123456789 | 4567890123456666 + | 123 | 0 + | 4567890123456789 | 4567890123456666 + | -4567890123456789 | 4567890123456912 +(5 rows) + +SELECT '' AS five, q2, q2 <-> int8 '123' AS dist FROM INT8_TBL i; + five | q2 | dist +------+-------------------+------------------ + | 456 | 333 + | 4567890123456789 | 4567890123456666 + | 123 | 0 + | 4567890123456789 | 4567890123456666 + | -4567890123456789 | 4567890123456912 +(5 rows) + SELECT q2, abs(q2) FROM INT8_TBL; q2 | abs -------------------+------------------ diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index f88f345..cb95adf 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -207,6 +207,21 @@ SELECT '' AS fortyfive, r1.*, r2.* | 34 years | 6 years (45 rows) +SELECT '' AS ten, f1 <-> interval '@ 2 day 3 hours' FROM INTERVAL_TBL; + ten | ?column? +-----+---------------------------- + | 2 days 02:59:00 + | 2 days -02:00:00 + | 8 days -03:00:00 + | 34 years -2 days -03:00:00 + | 3 mons -2 days -03:00:00 + | 2 days 03:00:14 + | 1 day 00:56:56 + | 6 years -2 days -03:00:00 + | 5 mons -2 days -03:00:00 + | 5 mons -2 days +09:00:00 +(10 rows) + -- Test intervals that are large enough to overflow 64 bits in comparisons CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval); INSERT INTO INTERVAL_TBL_OF (f1) VALUES diff --git a/src/test/regress/expected/money.out b/src/test/regress/expected/money.out index ab86595..fb2a489 100644 --- a/src/test/regress/expected/money.out +++ b/src/test/regress/expected/money.out @@ -123,6 +123,12 @@ SELECT m / 2::float4 FROM money_data; $61.50 (1 row) +SELECT m <-> '$123.45' FROM money_data; + ?column? +---------- + $0.45 +(1 row) + -- All true SELECT m = '$123.00' FROM money_data; ?column? diff --git a/src/test/regress/expected/oid.out b/src/test/regress/expected/oid.out index 1eab9cc..5339a48 100644 --- a/src/test/regress/expected/oid.out +++ b/src/test/regress/expected/oid.out @@ -119,4 +119,17 @@ SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 > '1234'; | 99999999 (3 rows) +SELECT '' AS eight, f1, f1 <-> oid '123' FROM OID_TBL; + eight | f1 | ?column? +-------+------------+------------ + | 1234 | 1111 + | 1235 | 1112 + | 987 | 864 + | 4294966256 | 4294966133 + | 99999999 | 99999876 + | 5 | 118 + | 10 | 113 + | 15 | 108 +(8 rows) + DROP TABLE OID_TBL; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 6072f6b..b951b75 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1801,6 +1801,7 @@ ORDER BY 1, 2, 3; 403 | 5 | *> 403 | 5 | > 403 | 5 | ~>~ + 403 | 6 | <-> 405 | 1 | = 783 | 1 | << 783 | 1 | @@ @@ -1910,7 +1911,7 @@ ORDER BY 1, 2, 3; 4000 | 26 | >> 4000 | 27 | >>= 4000 | 28 | ^@ -(123 rows) +(124 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing diff --git a/src/test/regress/expected/time.out b/src/test/regress/expected/time.out index 8e0afe6..ee74faa 100644 --- a/src/test/regress/expected/time.out +++ b/src/test/regress/expected/time.out @@ -86,3 +86,19 @@ ERROR: operator is not unique: time without time zone + time without time zone LINE 1: SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. +-- distance +SELECT f1 AS "Ten", f1 <-> time '01:23:45' AS "Distance" FROM TIME_TBL; + Ten | Distance +-------------+------------------------------- + 00:00:00 | @ 1 hour 23 mins 45 secs + 01:00:00 | @ 23 mins 45 secs + 02:03:00 | @ 39 mins 15 secs + 11:59:00 | @ 10 hours 35 mins 15 secs + 12:00:00 | @ 10 hours 36 mins 15 secs + 12:01:00 | @ 10 hours 37 mins 15 secs + 23:59:00 | @ 22 hours 35 mins 15 secs + 23:59:59.99 | @ 22 hours 36 mins 14.99 secs + 15:36:39 | @ 14 hours 12 mins 54 secs + 15:36:39 | @ 14 hours 12 mins 54 secs +(10 rows) + diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out index 4a2fabd..dcb4205 100644 --- a/src/test/regress/expected/timestamp.out +++ b/src/test/regress/expected/timestamp.out @@ -1604,3 +1604,214 @@ SELECT make_timestamp(2014,12,28,6,30,45.887); Sun Dec 28 06:30:45.887 2014 (1 row) +-- distance operators +SELECT '' AS "0", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMP_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); + 63 | Distance +----+--------------------------------------- + | @ 11356 days + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 58 secs + | @ 1453 days 6 hours 27 mins 58.6 secs + | @ 1453 days 6 hours 27 mins 58.5 secs + | @ 1453 days 6 hours 27 mins 58.4 secs + | @ 1493 days + | @ 1492 days 20 hours 55 mins 55 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1333 days 6 hours 27 mins 59 secs + | @ 231 days 18 hours 19 mins 20 secs + | @ 324 days 15 hours 45 mins 59 secs + | @ 324 days 10 hours 45 mins 58 secs + | @ 324 days 11 hours 45 mins 57 secs + | @ 324 days 20 hours 45 mins 56 secs + | @ 324 days 21 hours 45 mins 55 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 28 mins + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1333 days 5 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1452 days 6 hours 27 mins 59 secs + | @ 1451 days 6 hours 27 mins 59 secs + | @ 1450 days 6 hours 27 mins 59 secs + | @ 1449 days 6 hours 27 mins 59 secs + | @ 1448 days 6 hours 27 mins 59 secs + | @ 1447 days 6 hours 27 mins 59 secs + | @ 765901 days 6 hours 27 mins 59 secs + | @ 695407 days 6 hours 27 mins 59 secs + | @ 512786 days 6 hours 27 mins 59 secs + | @ 330165 days 6 hours 27 mins 59 secs + | @ 111019 days 6 hours 27 mins 59 secs + | @ 74495 days 6 hours 27 mins 59 secs + | @ 37971 days 6 hours 27 mins 59 secs + | @ 1447 days 6 hours 27 mins 59 secs + | @ 35077 days 17 hours 32 mins 1 sec + | @ 1801 days 6 hours 27 mins 59 secs + | @ 1800 days 6 hours 27 mins 59 secs + | @ 1799 days 6 hours 27 mins 59 secs + | @ 1495 days 6 hours 27 mins 59 secs + | @ 1494 days 6 hours 27 mins 59 secs + | @ 1493 days 6 hours 27 mins 59 secs + | @ 1435 days 6 hours 27 mins 59 secs + | @ 1434 days 6 hours 27 mins 59 secs + | @ 1130 days 6 hours 27 mins 59 secs + | @ 1129 days 6 hours 27 mins 59 secs + | @ 399 days 6 hours 27 mins 59 secs + | @ 398 days 6 hours 27 mins 59 secs + | @ 33 days 6 hours 27 mins 59 secs + | @ 32 days 6 hours 27 mins 59 secs +(63 rows) + +SELECT '' AS "0", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMP_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); + 63 | Distance +----+--------------------------------------- + | @ 11356 days 1 hour 23 mins 45 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 43 secs + | @ 1453 days 7 hours 51 mins 43.6 secs + | @ 1453 days 7 hours 51 mins 43.5 secs + | @ 1453 days 7 hours 51 mins 43.4 secs + | @ 1493 days 1 hour 23 mins 45 secs + | @ 1492 days 22 hours 19 mins 40 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1333 days 7 hours 51 mins 44 secs + | @ 231 days 16 hours 55 mins 35 secs + | @ 324 days 17 hours 9 mins 44 secs + | @ 324 days 12 hours 9 mins 43 secs + | @ 324 days 13 hours 9 mins 42 secs + | @ 324 days 22 hours 9 mins 41 secs + | @ 324 days 23 hours 9 mins 40 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 45 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1333 days 6 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1452 days 7 hours 51 mins 44 secs + | @ 1451 days 7 hours 51 mins 44 secs + | @ 1450 days 7 hours 51 mins 44 secs + | @ 1449 days 7 hours 51 mins 44 secs + | @ 1448 days 7 hours 51 mins 44 secs + | @ 1447 days 7 hours 51 mins 44 secs + | @ 765901 days 7 hours 51 mins 44 secs + | @ 695407 days 7 hours 51 mins 44 secs + | @ 512786 days 7 hours 51 mins 44 secs + | @ 330165 days 7 hours 51 mins 44 secs + | @ 111019 days 7 hours 51 mins 44 secs + | @ 74495 days 7 hours 51 mins 44 secs + | @ 37971 days 7 hours 51 mins 44 secs + | @ 1447 days 7 hours 51 mins 44 secs + | @ 35077 days 16 hours 8 mins 16 secs + | @ 1801 days 7 hours 51 mins 44 secs + | @ 1800 days 7 hours 51 mins 44 secs + | @ 1799 days 7 hours 51 mins 44 secs + | @ 1495 days 7 hours 51 mins 44 secs + | @ 1494 days 7 hours 51 mins 44 secs + | @ 1493 days 7 hours 51 mins 44 secs + | @ 1435 days 7 hours 51 mins 44 secs + | @ 1434 days 7 hours 51 mins 44 secs + | @ 1130 days 7 hours 51 mins 44 secs + | @ 1129 days 7 hours 51 mins 44 secs + | @ 399 days 7 hours 51 mins 44 secs + | @ 398 days 7 hours 51 mins 44 secs + | @ 33 days 7 hours 51 mins 44 secs + | @ 32 days 7 hours 51 mins 44 secs +(63 rows) + +SELECT '' AS "0", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMP_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); + 63 | Distance +----+---------------------------------------- + | @ 11355 days 14 hours 23 mins 45 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 43 secs + | @ 1452 days 20 hours 51 mins 43.6 secs + | @ 1452 days 20 hours 51 mins 43.5 secs + | @ 1452 days 20 hours 51 mins 43.4 secs + | @ 1492 days 14 hours 23 mins 45 secs + | @ 1492 days 11 hours 19 mins 40 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1332 days 21 hours 51 mins 44 secs + | @ 232 days 2 hours 55 mins 35 secs + | @ 324 days 6 hours 9 mins 44 secs + | @ 324 days 1 hour 9 mins 43 secs + | @ 324 days 2 hours 9 mins 42 secs + | @ 324 days 11 hours 9 mins 41 secs + | @ 324 days 12 hours 9 mins 40 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 45 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1332 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1451 days 20 hours 51 mins 44 secs + | @ 1450 days 20 hours 51 mins 44 secs + | @ 1449 days 20 hours 51 mins 44 secs + | @ 1448 days 20 hours 51 mins 44 secs + | @ 1447 days 20 hours 51 mins 44 secs + | @ 1446 days 20 hours 51 mins 44 secs + | @ 765900 days 20 hours 51 mins 44 secs + | @ 695406 days 20 hours 51 mins 44 secs + | @ 512785 days 20 hours 51 mins 44 secs + | @ 330164 days 20 hours 51 mins 44 secs + | @ 111018 days 20 hours 51 mins 44 secs + | @ 74494 days 20 hours 51 mins 44 secs + | @ 37970 days 20 hours 51 mins 44 secs + | @ 1446 days 20 hours 51 mins 44 secs + | @ 35078 days 3 hours 8 mins 16 secs + | @ 1800 days 20 hours 51 mins 44 secs + | @ 1799 days 20 hours 51 mins 44 secs + | @ 1798 days 20 hours 51 mins 44 secs + | @ 1494 days 20 hours 51 mins 44 secs + | @ 1493 days 20 hours 51 mins 44 secs + | @ 1492 days 20 hours 51 mins 44 secs + | @ 1434 days 20 hours 51 mins 44 secs + | @ 1433 days 20 hours 51 mins 44 secs + | @ 1129 days 20 hours 51 mins 44 secs + | @ 1128 days 20 hours 51 mins 44 secs + | @ 398 days 20 hours 51 mins 44 secs + | @ 397 days 20 hours 51 mins 44 secs + | @ 32 days 20 hours 51 mins 44 secs + | @ 31 days 20 hours 51 mins 44 secs +(63 rows) + diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out index 8a4c719..0a05e37 100644 --- a/src/test/regress/expected/timestamptz.out +++ b/src/test/regress/expected/timestamptz.out @@ -2544,3 +2544,217 @@ select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; Tue Jan 17 16:00:00 2017 PST (1 row) +-- distance operators +SELECT '' AS "0", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMPTZ_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); + 63 | Distance +----+--------------------------------------- + | @ 11356 days 8 hours + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 58 secs + | @ 1453 days 6 hours 27 mins 58.6 secs + | @ 1453 days 6 hours 27 mins 58.5 secs + | @ 1453 days 6 hours 27 mins 58.4 secs + | @ 1493 days + | @ 1492 days 20 hours 55 mins 55 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1333 days 7 hours 27 mins 59 secs + | @ 231 days 17 hours 19 mins 20 secs + | @ 324 days 15 hours 45 mins 59 secs + | @ 324 days 19 hours 45 mins 58 secs + | @ 324 days 21 hours 45 mins 57 secs + | @ 324 days 20 hours 45 mins 56 secs + | @ 324 days 22 hours 45 mins 55 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 28 mins + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1453 days 14 hours 27 mins 59 secs + | @ 1453 days 14 hours 27 mins 59 secs + | @ 1453 days 14 hours 27 mins 59 secs + | @ 1453 days 9 hours 27 mins 59 secs + | @ 1303 days 10 hours 27 mins 59 secs + | @ 1333 days 6 hours 27 mins 59 secs + | @ 1453 days 6 hours 27 mins 59 secs + | @ 1452 days 6 hours 27 mins 59 secs + | @ 1451 days 6 hours 27 mins 59 secs + | @ 1450 days 6 hours 27 mins 59 secs + | @ 1449 days 6 hours 27 mins 59 secs + | @ 1448 days 6 hours 27 mins 59 secs + | @ 1447 days 6 hours 27 mins 59 secs + | @ 765901 days 6 hours 27 mins 59 secs + | @ 695407 days 6 hours 27 mins 59 secs + | @ 512786 days 6 hours 27 mins 59 secs + | @ 330165 days 6 hours 27 mins 59 secs + | @ 111019 days 6 hours 27 mins 59 secs + | @ 74495 days 6 hours 27 mins 59 secs + | @ 37971 days 6 hours 27 mins 59 secs + | @ 1447 days 6 hours 27 mins 59 secs + | @ 35077 days 17 hours 32 mins 1 sec + | @ 1801 days 6 hours 27 mins 59 secs + | @ 1800 days 6 hours 27 mins 59 secs + | @ 1799 days 6 hours 27 mins 59 secs + | @ 1495 days 6 hours 27 mins 59 secs + | @ 1494 days 6 hours 27 mins 59 secs + | @ 1493 days 6 hours 27 mins 59 secs + | @ 1435 days 6 hours 27 mins 59 secs + | @ 1434 days 6 hours 27 mins 59 secs + | @ 1130 days 6 hours 27 mins 59 secs + | @ 1129 days 6 hours 27 mins 59 secs + | @ 399 days 6 hours 27 mins 59 secs + | @ 398 days 6 hours 27 mins 59 secs + | @ 33 days 6 hours 27 mins 59 secs + | @ 32 days 6 hours 27 mins 59 secs +(64 rows) + +SELECT '' AS "0", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMPTZ_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); + 63 | Distance +----+--------------------------------------- + | @ 11356 days 9 hours 23 mins 45 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 43 secs + | @ 1453 days 7 hours 51 mins 43.6 secs + | @ 1453 days 7 hours 51 mins 43.5 secs + | @ 1453 days 7 hours 51 mins 43.4 secs + | @ 1493 days 1 hour 23 mins 45 secs + | @ 1492 days 22 hours 19 mins 40 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1333 days 8 hours 51 mins 44 secs + | @ 231 days 15 hours 55 mins 35 secs + | @ 324 days 17 hours 9 mins 44 secs + | @ 324 days 21 hours 9 mins 43 secs + | @ 324 days 23 hours 9 mins 42 secs + | @ 324 days 22 hours 9 mins 41 secs + | @ 325 days 9 mins 40 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 45 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1453 days 15 hours 51 mins 44 secs + | @ 1453 days 15 hours 51 mins 44 secs + | @ 1453 days 15 hours 51 mins 44 secs + | @ 1453 days 10 hours 51 mins 44 secs + | @ 1303 days 11 hours 51 mins 44 secs + | @ 1333 days 7 hours 51 mins 44 secs + | @ 1453 days 7 hours 51 mins 44 secs + | @ 1452 days 7 hours 51 mins 44 secs + | @ 1451 days 7 hours 51 mins 44 secs + | @ 1450 days 7 hours 51 mins 44 secs + | @ 1449 days 7 hours 51 mins 44 secs + | @ 1448 days 7 hours 51 mins 44 secs + | @ 1447 days 7 hours 51 mins 44 secs + | @ 765901 days 7 hours 51 mins 44 secs + | @ 695407 days 7 hours 51 mins 44 secs + | @ 512786 days 7 hours 51 mins 44 secs + | @ 330165 days 7 hours 51 mins 44 secs + | @ 111019 days 7 hours 51 mins 44 secs + | @ 74495 days 7 hours 51 mins 44 secs + | @ 37971 days 7 hours 51 mins 44 secs + | @ 1447 days 7 hours 51 mins 44 secs + | @ 35077 days 16 hours 8 mins 16 secs + | @ 1801 days 7 hours 51 mins 44 secs + | @ 1800 days 7 hours 51 mins 44 secs + | @ 1799 days 7 hours 51 mins 44 secs + | @ 1495 days 7 hours 51 mins 44 secs + | @ 1494 days 7 hours 51 mins 44 secs + | @ 1493 days 7 hours 51 mins 44 secs + | @ 1435 days 7 hours 51 mins 44 secs + | @ 1434 days 7 hours 51 mins 44 secs + | @ 1130 days 7 hours 51 mins 44 secs + | @ 1129 days 7 hours 51 mins 44 secs + | @ 399 days 7 hours 51 mins 44 secs + | @ 398 days 7 hours 51 mins 44 secs + | @ 33 days 7 hours 51 mins 44 secs + | @ 32 days 7 hours 51 mins 44 secs +(64 rows) + +SELECT '' AS "0", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMPTZ_TBL; +ERROR: interval out of range +SELECT '' AS "63", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); + 63 | Distance +----+---------------------------------------- + | @ 11355 days 22 hours 23 mins 45 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 43 secs + | @ 1452 days 20 hours 51 mins 43.6 secs + | @ 1452 days 20 hours 51 mins 43.5 secs + | @ 1452 days 20 hours 51 mins 43.4 secs + | @ 1492 days 14 hours 23 mins 45 secs + | @ 1492 days 11 hours 19 mins 40 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1332 days 21 hours 51 mins 44 secs + | @ 232 days 2 hours 55 mins 35 secs + | @ 324 days 6 hours 9 mins 44 secs + | @ 324 days 10 hours 9 mins 43 secs + | @ 324 days 12 hours 9 mins 42 secs + | @ 324 days 11 hours 9 mins 41 secs + | @ 324 days 13 hours 9 mins 40 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 45 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1453 days 4 hours 51 mins 44 secs + | @ 1453 days 4 hours 51 mins 44 secs + | @ 1453 days 4 hours 51 mins 44 secs + | @ 1452 days 23 hours 51 mins 44 secs + | @ 1303 days 51 mins 44 secs + | @ 1332 days 20 hours 51 mins 44 secs + | @ 1452 days 20 hours 51 mins 44 secs + | @ 1451 days 20 hours 51 mins 44 secs + | @ 1450 days 20 hours 51 mins 44 secs + | @ 1449 days 20 hours 51 mins 44 secs + | @ 1448 days 20 hours 51 mins 44 secs + | @ 1447 days 20 hours 51 mins 44 secs + | @ 1446 days 20 hours 51 mins 44 secs + | @ 765900 days 20 hours 51 mins 44 secs + | @ 695406 days 20 hours 51 mins 44 secs + | @ 512785 days 20 hours 51 mins 44 secs + | @ 330164 days 20 hours 51 mins 44 secs + | @ 111018 days 20 hours 51 mins 44 secs + | @ 74494 days 20 hours 51 mins 44 secs + | @ 37970 days 20 hours 51 mins 44 secs + | @ 1446 days 20 hours 51 mins 44 secs + | @ 35078 days 3 hours 8 mins 16 secs + | @ 1800 days 20 hours 51 mins 44 secs + | @ 1799 days 20 hours 51 mins 44 secs + | @ 1798 days 20 hours 51 mins 44 secs + | @ 1494 days 20 hours 51 mins 44 secs + | @ 1493 days 20 hours 51 mins 44 secs + | @ 1492 days 20 hours 51 mins 44 secs + | @ 1434 days 20 hours 51 mins 44 secs + | @ 1433 days 20 hours 51 mins 44 secs + | @ 1129 days 20 hours 51 mins 44 secs + | @ 1128 days 20 hours 51 mins 44 secs + | @ 398 days 20 hours 51 mins 44 secs + | @ 397 days 20 hours 51 mins 44 secs + | @ 32 days 20 hours 51 mins 44 secs + | @ 31 days 20 hours 51 mins 44 secs +(64 rows) + diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index 22f80f2..24be476 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -346,3 +346,8 @@ select make_date(2013, 13, 1); select make_date(2013, 11, -1); select make_time(10, 55, 100.1); select make_time(24, 0, 2.1); + +-- distance operators +SELECT '' AS "Fifteen", f1 <-> date '2001-02-03' AS "Distance" FROM DATE_TBL; +SELECT '' AS "Fifteen", f1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM DATE_TBL; +SELECT '' AS "Fifteen", f1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM DATE_TBL; diff --git a/src/test/regress/sql/float4.sql b/src/test/regress/sql/float4.sql index 46a9166..f97f80a 100644 --- a/src/test/regress/sql/float4.sql +++ b/src/test/regress/sql/float4.sql @@ -82,6 +82,9 @@ UPDATE FLOAT4_TBL SELECT '' AS five, * FROM FLOAT4_TBL; +SELECT '' AS five, f.f1, f.f1 <-> '1004.3' AS dist FROM FLOAT4_TBL f; +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float8 AS dist FROM FLOAT4_TBL f; + -- test edge-case coercions to integer SELECT '32767.4'::float4::int2; SELECT '32767.6'::float4::int2; diff --git a/src/test/regress/sql/float8.sql b/src/test/regress/sql/float8.sql index 6595fd2..9b50210 100644 --- a/src/test/regress/sql/float8.sql +++ b/src/test/regress/sql/float8.sql @@ -125,6 +125,9 @@ SELECT ||/ float8 '27' AS three; SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f; +-- distance +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float8 AS dist FROM FLOAT8_TBL f; +SELECT '' AS five, f.f1, f.f1 <-> '1004.3'::float4 AS dist FROM FLOAT8_TBL f; SELECT '' AS five, * FROM FLOAT8_TBL; diff --git a/src/test/regress/sql/int2.sql b/src/test/regress/sql/int2.sql index 7dbafb6..16dd5d8 100644 --- a/src/test/regress/sql/int2.sql +++ b/src/test/regress/sql/int2.sql @@ -84,6 +84,16 @@ SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i; SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i; +-- distance +SELECT '' AS five, i.f1, i.f1 <-> int2 '2' AS x FROM INT2_TBL i; + +SELECT '' AS four, i.f1, i.f1 <-> int2 '2' AS x FROM INT2_TBL i +WHERE f1 > -32767; + +SELECT '' AS five, i.f1, i.f1 <-> int4 '2' AS x FROM INT2_TBL i; + +SELECT '' AS five, i.f1, i.f1 <-> int8 '2' AS x FROM INT2_TBL i; + -- corner cases SELECT (-1::int2<<15)::text; SELECT ((-1::int2<<15)+1::int2)::text; diff --git a/src/test/regress/sql/int4.sql b/src/test/regress/sql/int4.sql index f014cb2..cff32946 100644 --- a/src/test/regress/sql/int4.sql +++ b/src/test/regress/sql/int4.sql @@ -93,6 +93,16 @@ SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i; SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i; +SELECT '' AS five, i.f1, i.f1 <-> int2 '2' AS x FROM INT4_TBL i; + +SELECT '' AS four, i.f1, i.f1 <-> int2 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + +SELECT '' AS four, i.f1, i.f1 <-> int4 '2' AS x FROM INT4_TBL i +WHERE f1 > -2147483647; + +SELECT '' AS five, i.f1, i.f1 <-> int8 '2' AS x FROM INT4_TBL i; + -- -- more complex expressions -- diff --git a/src/test/regress/sql/int8.sql b/src/test/regress/sql/int8.sql index e890452..d7f5bde 100644 --- a/src/test/regress/sql/int8.sql +++ b/src/test/regress/sql/int8.sql @@ -89,6 +89,11 @@ SELECT q1 + 42::int2 AS "8plus2", q1 - 42::int2 AS "8minus2", q1 * 42::int2 AS " -- int2 op int8 SELECT 246::int2 + q1 AS "2plus8", 246::int2 - q1 AS "2minus8", 246::int2 * q1 AS "2mul8", 246::int2 / q1 AS "2div8" FROM INT8_TBL; +-- distance +SELECT '' AS five, q2, q2 <-> int2 '123' AS dist FROM INT8_TBL i; +SELECT '' AS five, q2, q2 <-> int4 '123' AS dist FROM INT8_TBL i; +SELECT '' AS five, q2, q2 <-> int8 '123' AS dist FROM INT8_TBL i; + SELECT q2, abs(q2) FROM INT8_TBL; SELECT min(q1), min(q2) FROM INT8_TBL; SELECT max(q1), max(q2) FROM INT8_TBL; diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql index bc5537d..d51c866 100644 --- a/src/test/regress/sql/interval.sql +++ b/src/test/regress/sql/interval.sql @@ -59,6 +59,8 @@ SELECT '' AS fortyfive, r1.*, r2.* WHERE r1.f1 > r2.f1 ORDER BY r1.f1, r2.f1; +SELECT '' AS ten, f1 <-> interval '@ 2 day 3 hours' FROM INTERVAL_TBL; + -- Test intervals that are large enough to overflow 64 bits in comparisons CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval); INSERT INTO INTERVAL_TBL_OF (f1) VALUES diff --git a/src/test/regress/sql/money.sql b/src/test/regress/sql/money.sql index 37b9ecc..8428d59 100644 --- a/src/test/regress/sql/money.sql +++ b/src/test/regress/sql/money.sql @@ -25,6 +25,7 @@ SELECT m / 2::float8 FROM money_data; SELECT m * 2::float4 FROM money_data; SELECT 2::float4 * m FROM money_data; SELECT m / 2::float4 FROM money_data; +SELECT m <-> '$123.45' FROM money_data; -- All true SELECT m = '$123.00' FROM money_data; diff --git a/src/test/regress/sql/oid.sql b/src/test/regress/sql/oid.sql index 4a09689..9f54f92 100644 --- a/src/test/regress/sql/oid.sql +++ b/src/test/regress/sql/oid.sql @@ -40,4 +40,6 @@ SELECT '' AS four, o.* FROM OID_TBL o WHERE o.f1 >= '1234'; SELECT '' AS three, o.* FROM OID_TBL o WHERE o.f1 > '1234'; +SELECT '' AS eight, f1, f1 <-> oid '123' FROM OID_TBL; + DROP TABLE OID_TBL; diff --git a/src/test/regress/sql/time.sql b/src/test/regress/sql/time.sql index 99a1562..31f0330 100644 --- a/src/test/regress/sql/time.sql +++ b/src/test/regress/sql/time.sql @@ -40,3 +40,6 @@ SELECT f1 AS "Eight" FROM TIME_TBL WHERE f1 >= '00:00'; -- where we do mixed-type arithmetic. - thomas 2000-12-02 SELECT f1 + time '00:01' AS "Illegal" FROM TIME_TBL; + +-- distance +SELECT f1 AS "Ten", f1 <-> time '01:23:45' AS "Distance" FROM TIME_TBL; diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql index b7957cb..5d023dd 100644 --- a/src/test/regress/sql/timestamp.sql +++ b/src/test/regress/sql/timestamp.sql @@ -230,3 +230,11 @@ SELECT '' AS to_char_11, to_char(d1, 'FMIYYY FMIYY FMIY FMI FMIW FMIDDD FMID') -- timestamp numeric fields constructor SELECT make_timestamp(2014,12,28,6,30,45.887); + +-- distance operators +SELECT '' AS "0", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMP_TBL; +SELECT '' AS "63", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); +SELECT '' AS "0", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMP_TBL; +SELECT '' AS "63", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); +SELECT '' AS "0", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMP_TBL; +SELECT '' AS "63", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMP_TBL WHERE isfinite(d1); diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql index c3bd46c..7f0525d 100644 --- a/src/test/regress/sql/timestamptz.sql +++ b/src/test/regress/sql/timestamptz.sql @@ -464,3 +464,11 @@ insert into tmptz values ('2017-01-18 00:00+00'); explain (costs off) select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00'; + +-- distance operators +SELECT '' AS "0", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMPTZ_TBL; +SELECT '' AS "63", d1 <-> date '2001-02-03' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); +SELECT '' AS "0", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMPTZ_TBL; +SELECT '' AS "63", d1 <-> timestamp '2001-02-03 01:23:45' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); +SELECT '' AS "0", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMPTZ_TBL; +SELECT '' AS "63", d1 <-> timestamptz '2001-02-03 01:23:45+03' AS "Distance" FROM TIMESTAMPTZ_TBL WHERE isfinite(d1); -- 2.7.4