postgresql “ON”处或附近的 postgres 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40327449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres syntax error at or near "ON"
提问by Elad Benda2
I created this table:
我创建了这个表：
CREATE TABLE IF NOT EXISTS config_activity_log
(
id serial primary key,
activity_name varchar(100) NOT NULL,
last_config_version varchar(50) NOT NULL,
activity_status varchar(100) NOT NULL DEFAULT 'Awaiting for cofman',
cofman_last_update bigint NOT NULL DEFAULT -1,
is_error boolean DEFAULT FALSE,
activity_timestamp timestamp DEFAULT current_timestamp
);
I try to run this postgres script:
我尝试运行这个 postgres 脚本：
INSERT INTO config_activity_log
(activity_name, last_config_version, activity_status)
VALUES
('test awating deployment','5837-2016-08-24_09-12-22', 'Awaiting for deployment')
ON CONFLICT (activity_name)
DO UPDATE SET
activity_status = EXCLUDED.activity_status
why do i get this syntax error?
为什么我会收到这个语法错误？
psql:upsert_test_log.sql:7: ERROR: syntax error at or near "ON"
LINE 5: ON CONFLICT (activity_name)
回答by JohnLBevan
Supported Version
支持版本
Per @klin's comment above, ON CONFLICTis only supported from PostgreSQL 9.5onwards.
根据上面@klin 的评论，ON CONFLICT仅从PostgreSQL 9.5开始支持。
If you're on an earlier version, there's some great info in this answer: https://stackoverflow.com/a/17267423/361842
如果您使用的是早期版本，则此答案中有一些重要信息：https: //stackoverflow.com/a/17267423/361842
Unique Constraint
唯一约束
Add a unique index on activity_name. At present there's no constraints on that column, so there's no possibility for a conflict on that column.
在 上添加唯一索引activity_name。目前对该列没有限制，因此该列不可能发生冲突。
CREATE UNIQUE INDEX UK_config_activity_log__activity_name
ON config_activity_log (activity_name);
If, however, you don't want that column to be unique, what conflict are you envisaging / what's the issue you're hoping to resolve with the on conflictaction?
但是，如果您不希望该列是唯一的，那么您设想的冲突是什么/您希望通过该on conflict操作解决什么问题？
See conflict_target in https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
请参阅https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT 中的冲突目标
An alternative syntax is to modify your create statement to include the unique condition there; e.g.
另一种语法是修改您的 create 语句以在其中包含唯一条件；例如
CREATE TABLE IF NOT EXISTS config_activity_log
(
id serial primary key,
activity_name varchar(100) NOT NULL UNIQUE,
last_config_version varchar(50) NOT NULL,
activity_status varchar(100) NOT NULL DEFAULT 'Awaiting for cofman',
cofman_last_update bigint NOT NULL DEFAULT -1,
is_error boolean DEFAULT FALSE,
activity_timestamp timestamp DEFAULT current_timestamp
);
回答by David ???? Markovitz
According to the error code, your version does not support ON CONFLICT.
On PostreSQL 9.6 the error message is -
根据错误代码，您的版本不支持ON CONFLICT。
在 PostreSQL 9.6 上，错误消息是 -
[Code: 0, SQL State: 42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
