为Heroku应用添加PostgreSQL时INSERT查询语法报错如何解决

问题原因 #

• 保留关键字冲突：User是PostgreSQL的内置保留关键字，直接作为列名使用会触发语法错误，是第一次报错的原因。
• 标识符引用规则不符：PostgreSQL中对保留字、大小写敏感的标识符需要使用双引号"包裹，你使用MySQL常用的反引号包裹列名，会被PostgreSQL识别为非法标识符，是第二次报错的原因。
• 列名不匹配：建表时定义的传动比列名为Final_Gearing，但INSERT语句中写的是Final_Ratio，二者不一致，即使解决引号问题也会触发报错。

解决方案 #

推荐优先选择方案一，避免后续重复踩坑：

方案一：修改列名避开保留字+统一命名规则 #

PostgreSQL默认会将未加双引号的标识符转为小写，采用小写加下划线的命名规则可完全避免大小写、保留字冲突问题：

1. 先删除已创建的错误表，执行SQL：

DROP TABLE IF EXISTS estimation_data;

2. 修改建表语句：

def create_table():
   sql = '''CREATE TABLE estimation_data 
   (user_ip text PRIMARY KEY,
    motor_kv integer,
    batt_volt decimal,
    pinion integer,
    spur integer,
    final_gearing decimal,
    wheel_rad decimal);'''
   pgcursor.execute(sql)
   pgdb.commit()

3. 修改插入SQL语句：

pgsql = '''INSERT INTO estimation_data 
   (user_ip, motor_kv, batt_volt, pinion, spur, final_gearing, wheel_rad) 
   VALUES (%s, %s, %s, %s, %s, %s, %s);'''

方案二：保留原有列名，按PostgreSQL规则引用标识符 #

如果必须保留User等大小写混合的列名，需统一使用双引号包裹所有对应标识符：

1. 先删除已创建的错误表，执行SQL：

DROP TABLE IF EXISTS estimation_data;

2. 修改建表语句，给所有大小写混合的列名加双引号：

def create_table():
   sql = '''CREATE TABLE estimation_data 
   ("User" text PRIMARY KEY,
    "Motor_kV" integer,
    "Batt_Volt" decimal,
    "Pinion" integer,
    "Spur" integer,
    "Final_Gearing" decimal,
    "Wheel_Rad" decimal);'''
   pgcursor.execute(sql)
   pgdb.commit()

3. 修改插入SQL语句，用双引号包裹列名，同时修正传动比列名：

pgsql = '''INSERT INTO estimation_data 
   ("User", "Motor_kV", "Batt_Volt", "Pinion", "Spur", "Final_Gearing", "Wheel_Rad") 
   VALUES (%s, %s, %s, %s, %s, %s, %s);'''

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