pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17473: Custom range type with timestamp doesn't respect precision
3+ messages / 3 participants
[nested] [flat]
* BUG #17473: Custom range type with timestamp doesn't respect precision
@ 2022-04-29 19:19 PG Bug reporting form <noreply@postgresql.org>
2022-04-29 21:20 ` Re: BUG #17473: Custom range type with timestamp doesn't respect precision David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2022-04-29 19:19 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cwikla@puzzle.io
The following bug has been logged on the website:
Bug reference: 17473
Logged by: John Cwikla
Email address: cwikla@puzzle.io
PostgreSQL version: 14.2
Operating system: Debian 14.2-1.pgdg110+1
Description:
Are range types supposed to respect precision? There is no mention of
precision in the documents that I can find, and it doesn't seem to be the
case for timestamps.
psql (14.2 (Debian 14.2-1.pgdg110+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
256, compression: off)
Type "help" for help.
postgres=> CREATE TYPE tstzrange3 AS RANGE ( subtype = timestamp(3) with
time zone);
CREATE TYPE
postgres=> select tstzrange3(NOW(), NULL);
tstzrange3
------------------------------------
["2022-04-29 19:11:31.920322+00",)
(1 row)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17473: Custom range type with timestamp doesn't respect precision
2022-04-29 19:19 BUG #17473: Custom range type with timestamp doesn't respect precision PG Bug reporting form <noreply@postgresql.org>
@ 2022-04-29 21:20 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-29 22:29 ` Re: BUG #17473: Custom range type with timestamp doesn't respect precision John Cwikla <cwikla@puzzle.io>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2022-04-29 21:20 UTC (permalink / raw)
To: cwikla@puzzle.io, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Apr 29, 2022 at 1:53 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17473
> Logged by: John Cwikla
> Email address: cwikla@puzzle.io
> PostgreSQL version: 14.2
> Operating system: Debian 14.2-1.pgdg110+1
> Description:
>
> Are range types supposed to respect precision? There is no mention of
> precision in the documents that I can find, and it doesn't seem to be the
> case for timestamps.
>
> psql (14.2 (Debian 14.2-1.pgdg110+1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
> 256, compression: off)
> Type "help" for help.
>
> postgres=> CREATE TYPE tstzrange3 AS RANGE ( subtype = timestamp(3) with
> time zone);
> CREATE TYPE
> postgres=> select tstzrange3(NOW(), NULL);
> tstzrange3
> ------------------------------------
> ["2022-04-29 19:11:31.920322+00",)
> (1 row)
>
>
In short, that isn't how the type modifier works. Once you have a value of
a given base type you end up passing it around the system as-is.
# create function echotime(intime timestamp(3) with time zone) returns
timestamp(3) with time zone as $$ select intime; $$ language sql;
CREATE FUNCTION
# select echotime(now());
echotime
-------------------------------
2022-04-29 21:04:14.047011+00
(1 row)
Though you can explicitly cast an existing value, creating a new one with
the newly specified type modifier.
# select echotime(now()::timestamptz(3));
echotime
----------------------------
2022-04-29 21:04:48.887+00
(1 row)
Basically, in most cases the type modifier is simply either documentation
or a runtime constraint enforced when first creating a data value.
However, one can use "create domain" to actually create a brand new type
without a direct modifier but whose underlying base type is constrained.
# create domain tstz3 as timestamptz(3);
CREATE DOMAIN
^
# create function echotime3(intime tstz3) returns tstz3 as $$ select
intime; $$ language sql;
CREATE FUNCTION
# select echotime3(now());
echotime3
----------------------------
2022-04-29 21:14:56.703+00
(1 row)
Not tested, but a range type of that domain should provide the behavior you
are looking for.
Domains do have their own idiosyncrasies, particularly when it comes to
working out implicit casts and general type resolution, since a domain is
also a valid instance of the base type and that fact can be relied upon to
make things work, possibly in reliable but unexpected ways (I don't have a
good example at the moment though...)
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17473: Custom range type with timestamp doesn't respect precision
2022-04-29 19:19 BUG #17473: Custom range type with timestamp doesn't respect precision PG Bug reporting form <noreply@postgresql.org>
2022-04-29 21:20 ` Re: BUG #17473: Custom range type with timestamp doesn't respect precision David G. Johnston <david.g.johnston@gmail.com>
@ 2022-04-29 22:29 ` John Cwikla <cwikla@puzzle.io>
0 siblings, 0 replies; 3+ messages in thread
From: John Cwikla @ 2022-04-29 22:29 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Groovy, thanks for the thorough explanation.
On Fri, Apr 29, 2022 at 2:20 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Fri, Apr 29, 2022 at 1:53 PM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 17473
>> Logged by: John Cwikla
>> Email address: cwikla@puzzle.io
>> PostgreSQL version: 14.2
>> Operating system: Debian 14.2-1.pgdg110+1
>> Description:
>>
>> Are range types supposed to respect precision? There is no mention of
>> precision in the documents that I can find, and it doesn't seem to be the
>> case for timestamps.
>>
>> psql (14.2 (Debian 14.2-1.pgdg110+1))
>> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits:
>> 256, compression: off)
>> Type "help" for help.
>>
>> postgres=> CREATE TYPE tstzrange3 AS RANGE ( subtype = timestamp(3) with
>> time zone);
>> CREATE TYPE
>> postgres=> select tstzrange3(NOW(), NULL);
>> tstzrange3
>> ------------------------------------
>> ["2022-04-29 19:11:31.920322+00",)
>> (1 row)
>>
>>
> In short, that isn't how the type modifier works. Once you have a value
> of a given base type you end up passing it around the system as-is.
>
> # create function echotime(intime timestamp(3) with time zone) returns
> timestamp(3) with time zone as $$ select intime; $$ language sql;
> CREATE FUNCTION
> # select echotime(now());
> echotime
> -------------------------------
> 2022-04-29 21:04:14.047011+00
> (1 row)
>
> Though you can explicitly cast an existing value, creating a new one with
> the newly specified type modifier.
>
> # select echotime(now()::timestamptz(3));
> echotime
> ----------------------------
> 2022-04-29 21:04:48.887+00
> (1 row)
>
> Basically, in most cases the type modifier is simply either documentation
> or a runtime constraint enforced when first creating a data value.
> However, one can use "create domain" to actually create a brand new type
> without a direct modifier but whose underlying base type is constrained.
>
> # create domain tstz3 as timestamptz(3);
> CREATE DOMAIN
> ^
> # create function echotime3(intime tstz3) returns tstz3 as $$ select
> intime; $$ language sql;
> CREATE FUNCTION
>
> # select echotime3(now());
> echotime3
> ----------------------------
> 2022-04-29 21:14:56.703+00
> (1 row)
>
> Not tested, but a range type of that domain should provide the behavior
> you are looking for.
>
> Domains do have their own idiosyncrasies, particularly when it comes to
> working out implicit casts and general type resolution, since a domain is
> also a valid instance of the base type and that fact can be relied upon to
> make things work, possibly in reliable but unexpected ways (I don't have a
> good example at the moment though...)
>
> David J.
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-04-29 22:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-29 19:19 BUG #17473: Custom range type with timestamp doesn't respect precision PG Bug reporting form <noreply@postgresql.org>
2022-04-29 21:20 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-29 22:29 ` John Cwikla <cwikla@puzzle.io>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox