PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – "No function matches the given name and argument types." when trying to insert a `DateTimeTzRange` with Sqlalchemy ORM

I’ve got a database table in a Postgres 15 database, it’s got two columns (amongst others), one is of type daterange, the other is tstzrange.

We’ve got some code that inserts to this table with raw SQL (via sqlalchemy core) by converting the sqlalchemy.dialects.postgresql.Range to a psycopg2.extras.DateRange or psycopg2.extras.DateTimeTzRange. This works fine.

I’m trying to hook up the Sqlalchemy ORM but I’m getting the following error when I call commit() on my session:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction)
function daterange(timestamp with time zone, unknown, unknown) does not exist
LINE 1: ...y.co.uk', '81740d75-d75a-4187-8b13-e28ac2e4dbf8', daterange(...
                                                              ^
HINT:  No function matches the given name and argument types. 
You might need to add explicit type casts.

[SQL: INSERT INTO "user" (user_id, username, full_name, provider_subject_id, effective, asserted) 
SELECT p0::VARCHAR, p1::VARCHAR, p2::VARCHAR, p3::VARCHAR,
p4::DATERANGE, p5::TSTZRANGE FROM (VALUES (%(user_id__0)s,
%(username__0)s, %(full_name__0)s, 
%(pro ... 233 characters truncated ... en(p0, p1, p2, p3, p4, p5, sen_counter) 
ORDER BY sen_counter RETURNING "user".id, "user".id AS id__1]

[parameters: {'effective__0': DateRange(datetime.datetime(2024, 4, 30, 13, 43, 31, 985502, tzinfo=<UTC>), None, '[)'),
'provider_subject_id__0': '81740d75-d75a-4187-8b13-e28ac2e4dbf8',
'full_name__0': '[email protected]', 
'user_id__0': '81740d75-d75a-4187-8b13-e28ac2e4dbf8', 
'asserted__0': DateTimeTZRange(datetime.datetime(2024, 4, 30, 13, 43, 31, 985513, tzinfo=<UTC>), None, '[)'), 
'username__0': '[email protected]', 
'effective__1': DateRange(datetime.datetime(2024, 4, 30, 13, 43, 31, 985502, tzinfo=<UTC>), None, '[)'), 
'provider_subject_id__1': '81740d75-d75a-4187-8b13-e28ac2e4dbf8',
'full_name__1': '[email protected]', 
'user_id__1': '81740d75-d75a-4187-8b13-e28ac2e4dbf8', 
'asserted__1': DateTimeTZRange(datetime.datetime(2024, 4, 30, 13, 43, 31, 985513, tzinfo=<UTC>), None, '[)'), 
'username__1': '[email protected]'}]
(Background on this error at: https://sqlalche.me/e/20/f405)

My UserModel looks like:

class ModelBase(DeclarativeBase):
    pass

class UserModel(ModelBase):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[str] = mapped_column(String)
    username: Mapped[str] = mapped_column(String)
    full_name: Mapped[str] = mapped_column(String)
    provider_subject_id: Mapped[str] = mapped_column(String)
    effective: Mapped[DATERANGE] = Column(DATERANGE)
    asserted: Mapped[TSTZRANGE] = Column(TSTZRANGE)

The user table is defined as so:

from sqlalchemy import MetaData


metadata_obj = MetaData()

Table("user", metadata_obj, 
        Column("id", PRIMARY_KEY_TYPE, primary_key=True),
        Column("user_id", data_types.CODE_TYPE, nullable=False),
        Column("role", data_types.CODE_TYPE, nullable=True),
        Column("username", data_types.CODE_TYPE, nullable=False),
        Column("full_name", data_types.SHORT_FIXED_STRING_TYPE, nullable=False),
        Column("provider_subject_id", data_types.CODE_TYPE, nullable=True),            
        Column("effective", DATERANGE, nullable=False),
        Column("asserted", TSTZRANGE, nullable=False)
)

I get the above error when I try to commit my session, like so:

session = session_factory()

user = UserModel(
   # ...
   asserted = DateTimeTZRange(asserted_value.lower, asserted_value.upper, bounds=asserted_value.bounds)
   effective = DateRange(effective_value.lower, effective_value.upper, bounds=effective_value.bounds)
   )

session.add(user)
session.commit() #💥

So my gut says this should work, because it’s working with raw SQL, but also I’m a python/sqlalchemy/postgres n00b, so what do I know.

My gut also thinks there’s some mapping special sauce I’m missing, but again… what do I know?

Tags:

2

Answers


  1. 0

    A simple working example to verify it can work:

    import psycopg2
from psycopg2.extras import DateRange
con = psycopg2.connect("dbname=test user=postgres port=5452")
cur = con.cursor()
cur.execute("create table dr_test(id integer, dr_field daterange)")
con.commit()
cur.execute("insert into dr_test values(%(id)s, %(dr)s)", 
{"id": 1, "dr": DateRange('2024-04-01', '2024-04-30', '[)')})
con.commit()
cur.execute("select * from dr_test")
cur.fetchone()
(1, DateRange(datetime.date(2024, 4, 1), datetime.date(2024, 4, 30), '[)'))

    Using date objects:

    from datetime import date

cur.execute("insert into dr_test values(%(id)s, %(dr)s)", 
{"id": 1, "dr": DateRange(date(2024, 5, 1), date(2024, 5, 31), '[)')})
con.commit()
cur.execute("select * from dr_test")
cur.fetchall()
[(1, DateRange(datetime.date(2024, 4, 1), datetime.date(2024, 4, 30), '[)')),
 (1, DateRange(datetime.date(2024, 5, 1), datetime.date(2024, 5, 31), '[)'))]
    Login or Signup to reply.
  2. 0

    I’m not sure that you should be using the psycopg2 datatypes here. The docs recommend using the PostgreSQL dialect’s Range type.

    from sqlalchemy.dialects.postgresql import Range

with Session.begin() as session:
    user = UserModel(
        effective = Range(lower1, upper1),
        asserted = Range(lower2, upper2)
    )

    session.add(user)

    Technically (at least per the SQLAlchemy docs, the model columns should be type-hinted as Ranges of the appropriate Python type, but that doesn’t have any effect on the insert operation as far as I can tell.

    A complete example would look like this:

    import atexit
import datetime as dt

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.dialects.postgresql import DATERANGE, TSTZRANGE, Range


class ModelBase(orm.DeclarativeBase):
    pass


class UserModel(ModelBase):
    __tablename__ = "t78409103"

    id: Mapped[int] = mapped_column(primary_key=True)
    effective: Mapped[Range[dt.date]] = mapped_column(DATERANGE)
    asserted: Mapped[Range[dt.datetime]] = mapped_column(TSTZRANGE)


engine = sa.create_engine('postgresql+psycopg2:///test', echo=True)
atexit.register(engine.dispose)
ModelBase.metadata.drop_all(engine)
ModelBase.metadata.create_all(engine)
Session = orm.sessionmaker(engine)

now = dt.datetime.now(tz=dt.UTC)
upper = now.date()
lower = upper - dt.timedelta(days=7)

lower2 = now - dt.timedelta(days=7)

with Session.begin() as session:
    user = UserModel(
        effective = Range(lower, upper),
        asserted = Range(lower2, now)
    )

    session.add(user)

    Producing this output:

    <snip>
2024-04-30 22:21:39,764 INFO sqlalchemy.engine.Engine INSERT INTO t78409103 (effective, asserted) VALUES (%(effective)s::DATERANGE, %(asserted)s::TSTZRANGE) RETURNING t78409103.id
2024-04-30 22:21:39,764 INFO sqlalchemy.engine.Engine [generated in 0.00030s] {'effective': DateRange(datetime.date(2024, 4, 23), datetime.date(2024, 4, 30), '[)'), 'asserted': DateTimeTZRange(datetime.datetime(2024, 4, 23, 21, 21, 39, 762421, tzinfo=datetime.timezone.utc), datetime.datetime(2024, 4, 30, 21, 21, 39, 762421, tzinfo=datetime.timezone.utc), '[)')}
</snip>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search