I have 2 tables in 2 different databases, both Postgres. I'm getting some records from one of the tables in Python and I'd like to join this with the other table on Postgres (on a diffferent DB). I have written the following query to do this:
sql = """
SELECT
*,
extra::json ->> 'field_name' AS field_name
FROM
tablename
WHERE
sent_at >= %(notification_date)s
and sent_at <= %(notification_date)s + interval '1 hour'
and to_user_id = %(user_id)s
"""
df_results = db_query('DB_Name', sql,
params={'notification_date': tuple(df['sent_at']),
'user_id': tuple(df['user_id'])})
But this gives me the following error:
UndefinedFunction: operator does not exist: timestamp without time zone >= record
LINE 8: sent_at >= ('2022-03-02T06:49:06.416170'::timestamp, '20...
It's looking like notification_date appears as a list within brackets. How do I make this a row-wise operation just like joining tables in SQL?
I'd like to pass only one value of notification_date and only one value of user_id at a time for a row-wise comparison.
I don't want to put this in a loop where I pass one value at a time. The table I am dealing with is massive and it'd take way too much time for the code to finish. Even then, I suspect it might just time out and not execute at all.
Any help would be greatly appreciated.