PostgreSQL错误：运算符不存在：name=integer

Question

执行简单查询时出现操作符不匹配错误。这是什么原因造成的？

dev_db=# `select * from registrants where user=1;`
ERROR:  operator does not exist: name = integer
LINE 1: select * from registrants where user=1;
                                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

表定义：

dev_db=# \d+ registrants
                              Table "public.registrants"
    Column    |           Type           |     Modifiers      | Storage  | Description
--------------+--------------------------+--------------------+----------+-------------
 user         | integer                  | not null           | plain    |
 degree       | text                     |                    | extended |
 title        | text                     |                    | extended |
 organization | text                     |                    | extended |
 address      | text                     |                    | extended |
 city         | text                     |                    | extended |

Indexes:
    "registrants_pkey" PRIMARY KEY, btree ("user")
Foreign-key constraints:
    "registrants_country_fkey" FOREIGN KEY (country) REFERENCES countries(id)
    "registrants_user_fkey" FOREIGN KEY ("user") REFERENCES users(id)
Referenced by:
    TABLE "class_evaluations" CONSTRAINT "class_evaluations_registrant_fkey" FOREIGN KEY (registrant) REFERENCES registrants("user")

Triggers:
    archive_registrants BEFORE DELETE OR UPDATE ON registrants FOR EACH ROW EXECUTE PROCEDURE archive_reg_table()
Has OIDs: no

Answer 1

根据手册，USER是一个保留关键字。您必须引用它以避免语法错误。

SELECT * FROM registrants WHERE "user" = 1

PostgreSQL保留关键字列表

如果有时间更改数据库，请将列名更改为非保留关键字。这将帮助你避免未来的头痛。