<class 'psycopg2.errors.UndefinedColumn'>: column does not exist when JOIN

Question

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

I did all I can and still this error:

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...
                                                             ^

Python code:

# this all below return the same error
# 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 and user tables:

What is mistake?

Answer 1

key and user are reserved word. Try:

FROM "key" k JOIN "user" u