Issue with range types and casts?

[Josh Berkus]

select version();
version

--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit


create table tenmin as select * from sampledata where collect_ts <@
'[2013-01-01 00:00:00,2013-01-01 00:10:00)';
ERROR: could not find range type for data type timestamp with time zone
Time: 0.189 ms

This seems like it ought to be fixable. Postgres has figured out that
it needs to find the range type for timestamptz. Why can't it?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[Jeff Davis]

On Fri, 2013-03-08 at 15:27 -0800, Josh Berkus wrote:
> create table tenmin as select * from sampledata where collect_ts <@
> '[2013-01-01 00:00:00,2013-01-01 00:10:00)';
> ERROR: could not find range type for data type timestamp with time zone
> Time: 0.189 ms
>
> This seems like it ought to be fixable. Postgres has figured out that
> it needs to find the range type for timestamptz. Why can't it?

The reason it's doing that is because there could be multiple range
types based on one element type. The workaround (which you are probably
already using) is to cast the second argument to a tstzrange.

We could try to be smarter about it, but the problem with relying on
that intelligence is that, when it doesn't work, your queries break. For
instance, someone else could add a new datatype "tstzrange2"[1], and
that would introduce ambiguity, and you'd get an error then (probably
breaking many existing queries baked into your application). Different
people have different opinions on how smart the type system should try
to be, and reasonable people may differ; I'm just saying what it does
currently.

That being said, perhaps the error message should be improved? I'm not
sure what it should say exactly, though.

Regards,
Jeff Davis

[1] To make it more plausible, tstzrange2 might have a canonicalization
function that turns it into a discrete range (kind of like date but at a
resolution smaller than a day).