psycopg2报UndefinedColumn错误：JOIN时提示u.id列不存在

环境信息 #

• python: 3.9.17
• psycopg2: 2.9.7
• OS: Ubuntu 20.0
• postgre: 12

遇到的错误 #

执行SQL时返回如下错误：

2023-09-11 22:45:35,033 ERROR <class 'psycopg2.errors.UndefinedColumn'>: column u.id does not exist
LINE 1: ...id, u.balance FROM key k JOIN user u ON k.user_id=u.id WHERE...
                                                             ^

相关代码 #

尝试过多种JOIN写法，均返回相同错误：

# 以下所有写法均返回相同错误
# sql = """SELECT k.user_id, k.custom_rates, u.id, u.balance FROM key k JOIN user u ON k.user_id=u.id WHERE k.key='test'"""
# sql = "SELECT k.user_id, k.custom_rates, u.id, u.balance FROM user u JOIN key k ON k.user_id=u.id WHERE k.key='test'"
# sql = "SELECT k.user_id, k.custom_rates, u.id, u.balance FROM key k INNER JOIN user u ON k.user_id=u.id WHERE k.key='test'"
sql = "SELECT k.user_id, k.custom_rates, u.id, u.balance FROM key k JOIN user u ON k.user_id=u.id WHERE k.key='test'"
cursor.execute(sql)
res = cursor.fetchone()

表结构说明 #

key表和user表结构如下：

• key表：包含user_id、custom_rates、key字段
• user表：包含user_id、balance字段（无id字段）

错误原因 #

核心问题是user表不存在id字段，但SQL语句中错误地使用了u.id进行关联与查询。从表结构来看，user表的用户标识字段是user_id，而非id，需要将SQL中的u.id替换为u.user_id。

修正后的SQL示例：

SELECT k.user_id, k.custom_rates, u.user_id, u.balance 
FROM key k 
JOIN user u ON k.user_id = u.user_id 
WHERE k.key='test'

另外注意：user和key是PostgreSQL的保留关键字，作为表名使用时建议用双引号包裹，避免潜在语法问题：

SELECT k.user_id, k.custom_rates, u.user_id, u.balance 
FROM "key" k 
JOIN "user" u ON k.user_id = u.user_id 
WHERE k.key='test'

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