psycopg returns empty query result when there is data

Question

I'm trying to get records from the postgressql query below. It does have data but when I tried to retrieve it with psycopg, weirdly it returns empty. Does it work with WITH query or we can only use SELECT instead? Any input would be appreciated, thank you.

query = \
        f""" WITH a AS (
            SELECT "public"."table_a"."code", 
            "public"."table_a"."address", sum("public"."table_a"."amount") AS 
            "sum" FROM "public"."table_a" WHERE "public"."table_a"."address" <> 'KL' GROUP BY 
            "public"."table_a"."code", "public"."table_a"."address"), 
            c as (
                WITH b AS ( 
                SELECT address, code, count(*) as "total" FROM table_b where created_date between 
                ((current_date + TIME '14:00:00.000+08:00') - interval '7 days') and ((
                current_date + TIME '23:59:00.000+08:00') - interval '7 days') group by 
                address, code order by address, code) 
                select b.address, table_c.unit_code, table_c.name, sum(b.total * 
                table_c.amount) as "total" from table_c join b on table_c.code = b.code 
                group by table_c.unit_code, table_c.name, b.address 
                order by table_c.unit_code, b.address
                )
            SELECT a.address, a.code, (a.sum - c.total)::int as output
            FROM a join c on a.code = c.unit_code
            AND a.address = c.address
            ORDER BY a.address, a.code 
"""

conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(query)
rows = cur.fetchall()
for row in rows:
            print(row)

WITH ... WITH ... is not valid SQL and should result in an error. Use WITH a AS (...) , b AS (...) SELECT * FROM a, b — Frank Heikens, Sep 21 '22 at 09:19
@FrankHeikens i did get records from the query actually. Just not with psycopg. Do you mean that it won't work with psycopg? — tiredqa_18, Sep 21 '22 at 09:20
psycopg2 does not validate the syntax of queries passed as text. — snakecharmerb, Sep 21 '22 at 09:21
@FrankHeikens saw your edited comment. Sure, I'll try out that. Thanks — tiredqa_18, Sep 21 '22 at 09:39
I mean, `cursor.execute(some_string)` just sends the string to the PostgreSQL server, it does not check that the string is valid SQL.If you do `cursor.execute('pass the sauce')` psycopg2 will send "pass the sauce" to the server. The server, of course, will respond with a syntax error. — snakecharmerb, Sep 21 '22 at 12:06
thanks for the explanation @snakecharmerb. i found the issue. turns out, it's not converting the date and time correctly even if i've specified timezone. thanks all for your time — tiredqa_18, Sep 21 '22 at 12:15
**Do not** get into the habit of using `f` strings. You do not use parameters here, but should you need to then read here [Parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries). — Adrian Klaver, Sep 21 '22 at 22:07

score 1 · Accepted Answer · answered Sep 21 '22 at 15:10

It took a whole day for me to realize that the timestamp in the log is different than expected. The query below works (with WITH AS and all), just need to remove the timezone for me since I don't need it. Lesson learnt. Make sure to check the server timezone and if conversion is needed. Hope this helps someone in the future.

WITH a AS (
            SELECT "public"."table_a"."code", 
            "public"."table_a"."address", sum("public"."table_a"."amount") AS 
            "sum" FROM "public"."table_a" WHERE "public"."table_a"."address" <> 'KL' GROUP BY 
            "public"."table_a"."code", "public"."table_a"."address"), 
            c as (
                WITH b AS ( 
                SELECT address, code, count(*) as "total" FROM table_b where created_date between 
                ((current_date + TIME '14:00:00.000') - interval '7 days') and ((
                current_date + TIME '23:59:00.000') - interval '7 days') group by 
                address, code order by address, code) 
                select b.address, table_c.unit_code, table_c.name, sum(b.total * 
                table_c.amount) as "total" from table_c join b on table_c.code = b.code 
                group by table_c.unit_code, table_c.name, b.address 
                order by table_c.unit_code, b.address
                )
            SELECT a.address, a.code, (a.sum - c.total)::int as output
            FROM a join c on a.code = c.unit_code
            AND a.address = c.address
            ORDER BY a.address, a.code

A thread about using only *one* WITH clause: https://stackoverflow.com/q/21386772/9987623 — AlexK, Sep 25 '22 at 07:46
Yes, same problem here. I created a query in a local machine with a timestamptz that was compared with a created_at column. When I execute that query in a DBMS client it works with that timestamp, but failed when executed with pscopg2. The problem was solved when I transform that comparation to ````sql WHERE created_at > clock_timestamp() - interval '10 minutes' ```` More details: https://www.postgresql.org/docs/current/functions-datetime.html — nachouve, Aug 18 '23 at 10:27

score 0 · Answer 2 · answered Aug 18 '23 at 10:31

I encountered a puzzling issue recently and wanted to share my experience for the benefit of the community. I crafted a query on my local machine that involved a timestamptz comparison against a created_at column in a PostgreSQL table. The query worked flawlessly when executed in my DBMS client, but it threw an error when executed using psycopg2 within my Python code.

To my surprise, the solution turned out to be quite straightforward. The timestamp comparison that initially looked fine was causing the discrepancy. When executed with psycopg2, it somehow failed. However, I was able to resolve the issue by transforming the comparison using PostgreSQL's clock_timestamp() function along with an interval.

Here's the snippet that initially caused the issue:


WHERE created_at > '2023-08-17 12:00:00' -- Example timestamp

And here's the improved version that worked consistently with both the DBMS client and psycopg2:


WHERE created_at > clock_timestamp() - interval '10 minutes'

The key lesson here is that even seemingly correct queries can sometimes behave differently in various contexts (such time in the server). By using PostgreSQL's built-in functions and expressions, we can ensure consistent behavior across different tools and interfaces.

For more information on PostgreSQL's datetime functions, you can refer to the official documentation: PostgreSQL Datetime Functions

psycopg returns empty query result when there is data

2 Answers2