Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.7k views
in Technique[技术] by (71.8m points)

sql - postgres syntax error at or near "ON"

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:

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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Supported Version

Per @klin's comment above, ON CONFLICT is only supported from PostgreSQL 9.5 onwards.

If you're on an earlier version, there's some great info in this answer: 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.

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?

See conflict_target in 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 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
);

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...