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

注：此处涉及到数据类型转换问题，您可能需要添加显式类型转换。

- DevR

Answer 1

根据手册，USER是一个保留关键字。为避免语法错误，必须将其引用起来。

根据手册，USER是一个保留关键字。为避免语法错误，必须将其引用起来。

SELECT * FROM registrants WHERE "user" = 1

PostgreSQL 保留关键字列表

如果您有时间修改数据库，请将列名更改为不是保留关键字的列名。这将有助于避免未来的麻烦。

- John Woo

---

不要使用保留字作为标识符，这是最好的做法。 - Erwin Brandstetter

感谢您的快速回复。这是来自一个庞大的现有应用程序，不太容易更改。 - DevR