sqlalchemy incorrectly resolving type within postgresql func.tstzrange call

[Darin Gordon]

Hey Mike and company,

If you were to run the following code against a postgresql 9.5+ database, you'll see an exception because the "contains" function sqlalchemy associates with the func.tstzrange expression below is intended for an Interval type when instead it ought to use the contains for a Timestamp.  After the code example is the exception that is raised, followed by a query you can run to confirm that a Timestamp type is created.

Thanks for considering!

Darin

-------------------------------------------------------------------------------------------------------------------------

from sqlalchemy.dialects.postgresql import INTERVAL

from sqlalchemy import (create_engine, select, func, literal, cast)

db_url = "postgres://enter your db info here"

engine = create_engine(db_url, echo=True)

with engine.connect() as conn:

    timeslot = func.tstzrange((func.current_date() + cast('8 hours', INTERVAL())),

                                         (func.current_date() + cast('12 hours', INTERVAL())),

                                         '[]')

    is_ongoing = conn.scalar(select([literal(True)]).

                                         where(timeslot.contains(func.now())))

    print('\n\n', is_ongoing.compile(engine))

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

sqlalchemy.exc.DataError: (psycopg2.DataError) invalid input syntax for type interval: "%"

LINE 2: ...T_DATE + CAST('12 hours' AS INTERVAL), '[]') LIKE '%' + now(...

                                                             ^

 [SQL: "SELECT %(param_1)s AS anon_1 \nWHERE (tstzrange(CURRENT_DATE + CAST(%(param_2)s AS INTERVAL), CURRENT_DATE + CAST(%(param_3)s AS INTERVAL), %(tstzrange_1)s) LIKE '%%' + now() || '%%')"] [parameters: {'param_1': True, 'param_2': '8 hours', 'param_3': '12 hours', 'tstzrange_1': '[]'}]

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

To confirm Timestamp type, run:  SELECT pg_typeof(CURRENT_DATE + '8 hours'::interval);

[Mike Bayer]

Hey Darin -

Are these two email threads about tztange the same issue or do I have
to consider each separately? haven't looked yet.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Darin Gordon]

Same.  I simplified the issue down to the bare essentials.

> email to sqlalchemy+unsubscribe@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/Hb-_MjoUY68/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Mike Bayer]

On Sun, Nov 5, 2017 at 5:58 AM, Darin Gordon <dar...@gmail.com> wrote:

> Hey Mike and company,
>
> If you were to run the following code against a postgresql 9.5+ database,
> you'll see an exception because the "contains" function sqlalchemy
> associates with the func.tstzrange expression below is intended for an
> Interval type when instead it ought to use the contains for a Timestamp.
> After the code example is the exception that is raised, followed by a query
> you can run to confirm that a Timestamp type is created.

The func.tstzrange() function doesn't have a preset return type in
SQLAlchemy core, you have to specify it:

from sqlalchemy.dialects.postgresql import INTERVAL, TSTZRANGE

from sqlalchemy import (create_engine, select, func, literal, cast)

db_url = "postgres://scott:tiger@localhost/test"

engine = create_engine(db_url, echo=True)

with engine.connect() as conn:

timeslot = func.tstzrange(
(
func.current_date() + cast('8 hours', INTERVAL())
),
(
func.current_date() + cast('12 hours', INTERVAL())
),

'[]',
type_=TSTZRANGE
)

is_ongoing = conn.scalar(
select([literal(True)]).where(timeslot.contains(func.now())))


then the output is:

SELECT %(param_1)s AS anon_1
WHERE tstzrange(CURRENT_DATE + CAST(%(param_2)s AS INTERVAL),
CURRENT_DATE + CAST(%(param_3)s AS INTERVAL), %(tstzrange_1)s) @>
now()

which I think is what you're looking for.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to sqlalchemy+...@googlegroups.com.

[Darin Gordon]

A-ha! :)  a return type!  Thanks, Mike!  Once a return type was added, the correct sql generated.