Psycopg执行含WITH子句的PostgreSQL查询返回空结果排查

psycopg2完全支持PostgreSQL的WITH子句（包括嵌套CTE写法），你的空结果问题和WITH语法无关，建议从以下几个方向排查：

1. 验证SQL逻辑本身 #

把代码中的查询语句复制到psql、pgAdmin等数据库客户端直接执行：

• 如果客户端执行也返回空，说明你的SQL逻辑存在问题（比如筛选条件过严、JOIN无匹配等）
• 如果客户端执行有数据，再排查psycopg执行环节的问题

2. 检查时区一致性 #

你的SQL中使用了带时区的时间条件：

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')

psycopg连接时的时区可能与数据库服务器时区不一致，导致筛选的时间范围偏差。解决方法：

• 在连接参数中指定时区，比如：

  params = {
      # 其他连接参数
      'timezone': 'Asia/Singapore'  # 对应+8时区
  }
  conn = psycopg2.connect(**params)
  

• 或者将SQL中的时间条件改为基于UTC的写法，避免时区差异影响

3. 确认Schema匹配 #

你的SQL中部分表指定了public.前缀（如public.table_a），但table_b、table_c未指定。如果psycopg连接的数据库用户默认schema不是public，会导致查询的表不是你预期的表。建议：

• 所有表都加上public.前缀，比如public.table_b、public.table_c
• 或者在建立连接后执行：

  cur.execute("SET search_path TO public;")
  

4. 检查JOIN条件有效性 #

最终查询是a JOIN c ON a.code = c.unit_code AND a.address = c.address，如果CTE a和c的code/address没有匹配的记录，会返回空结果。可以单独查询两个CTE的结果：

• 先查a的结果：

  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的结果（包括内部的b），确认两者是否有交集

5. 修正SQL中的转义字符 #

注意到你提供的代码中出现了<>，这是HTML转义后的不等于符号，实际SQL中应该使用<>或!=。如果代码中确实是<>，会导致条件判断错误，需要修正为<>。

你的原始代码（整理后） #

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)

内容的提问来源于stack exchange，提问作者tiredqa_18