PostgreSQL 插入数据报错：column "xxx" does not exist 解决方法

背景

shell 脚本跑 Python 写数据脚本任务的时候报错：

  File "./xxx.py", line 59, in xxx
    cur.execute(insert_sql(col_string, str(data_list)))
psycopg2.ProgrammingError: column "it’s adj for sb和it's adj of sb的区别" does not exist
LINE 1: ...1', 'student', 16, '八年级下册', 20211028, 50347, "it’s adj ...

原因

做 print 输出之后发现是这个数写入报错：

it’s adj for sb和it's adj of sb的区别

可以看出其实是 ’ 和 ' 混用了（作为写作遵循互联网写作规范的强迫症患者我对于这种混用真的不能忍）。

然后在 stackoverflow 上查到了这个相关问题：Insert text with single quotes in PostgreSQL。但是它的解决方案在我看来还是不太符合我的要求，毕竟写入 pg 的数据不能因为 ' 报错就强制将单个 ' 转为 '' 。所以我就将 ' 替换为 ’，发现执行还是报错，意识到关键问题不在这里。

然后在 这篇博客 找到了原因，据 wiki.postgresql 官网 解释：

PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').
MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.

翻译过来就是：

PostgreSQL 只能用单引号（’）表示值，双引号（"）是表示系统标识符的，比如表名或者字段名。MySQL 使用 `（重音标记或反引号）来引用系统标识符，这绝对是非标准的。

因为其实上面写入的数据完整格式是：

insert into xxx (字段1, 字段2, 字段3) values ('student', "it’s adj for sb和it's adj of sb的区别", '概念课')

发现只有字段2外面是 "，而 pg 的双引号 " 是表示系统标识符，根本写不进 pg。其实我猜测应该就是因为字符串内部有 '，导致的外部引号变为了 "。

于是我就在 Python 代码里进行了字符替换：

str.replace("\"", "\'").replace("\'s ", "’s ")

先将外部 " 替换为 '，然后再将 ' 替换为 ’。但要注意实际情况中 's 有可能会匹配到 'student'，因此我多匹配了一位空格，将 's空 替换为 ’s空。

感想

发现这个报错很明显是历史遗留问题，但是基本不会考虑到。因为写入 pg 数据库的数据应该是用户在前端输入的，符号混用这种情况真的没办法去控制。然而单引号、双引号、反引号这种在 PostgreSQL 里又和 MySQL 不太一样，很容易引起问题。