Postgres语法错误位于“ ON”或“ ON”附近。 [英] postgres syntax error at or near "ON"
问题描述
我创建了此表:
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
);
我尝试运行以下postgres脚本:
I try to run this postgres script:
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)
推荐答案
支持的版本
每个@klin在上面的评论中,<$ c $仅从 PostgreSQL 9.5 起支持c> ON CONFLICT 。
Per @klin's comment above, ON CONFLICT
is only supported from PostgreSQL 9.5 onwards.
如果您使用的是较早的版本,在此答案中有一些很好的信息: https://stackoverflow.com/a/1 7267423/361842
If you're on an earlier version, there's some great info in this answer: https://stackoverflow.com/a/17267423/361842
唯一约束
添加唯一索引在 activity_name
上。目前,该列上没有任何约束,因此该列上没有冲突。
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.
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 conflict
action?
请参见 https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
另一种语法是修改您的create语句以在其中包含唯一条件;例如
An alternative syntax is to modify your create statement to include the unique condition there; e.g.
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
);
这篇关于Postgres语法错误位于“ ON”或“ ON”附近。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!