8

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)
roeygol
  • 4,908
  • 9
  • 51
  • 88
Elad Benda2
  • 13,852
  • 29
  • 82
  • 157
  • 1
    PSQL docs say that if using brackets on "ON CONFLICT" the thing inside is an index expression. Is activity_name an index expression, or is it just a column ? https://www.postgresql.org/docs/9.5/static/sql-insert.html – Neil Stockton Oct 30 '16 at 09:01
  • just column. "id" is an index column but it's auto populated. how can i fix this? – Elad Benda2 Oct 30 '16 at 09:02
  • Do you have a unique index on `activity_name` that would cause a conflict? – JohnLBevan Oct 30 '16 at 09:05
  • no, i have added my table creation script. activity_name has no index nor it's unique – Elad Benda2 Oct 30 '16 at 09:06
  • 1
    If the `activity_name` doesn't have a unique constraint, there can't be a conflict on that field (i.e. what is there for it to conflict with if there's no constraint?). By adding a unique index on that column the conflict action makes sense, so should then work. – JohnLBevan Oct 30 '16 at 09:08
  • `on conflict` was introduced in Postgres 9.5. Your Postgres version? – klin Oct 30 '16 at 09:08
  • See `conflict_target` in https://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT – JohnLBevan Oct 30 '16 at 09:09
  • @JohnLBevan So maybe i need another solution. usually activity_name is not unique, but I want to add a "test" row with status "a". when it changes to "b" I want to alter it to "a" instead of adding a new test row – Elad Benda2 Oct 30 '16 at 09:17

2 Answers2

16

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
);
Community
  • 1
  • 1
JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • So maybe i need another solution. usually `activity_name` is not unique, but I want to add a "test" row with status "a". when it changes to "b" I want to alter it to "a" instead of adding a new test row – Elad Benda2 Oct 30 '16 at 09:16
  • If I've understood, that means `activity_name` is still unique. i.e. you have one row where `activity_name`=`'test'` and `activity_status`=`'a'`. You attempt to insert a second row where `activity_name`=`'test'` and `activity_status`=`'b'`, but in this case since a row with the same `activity_name` already exists you want it to update `activity_status` to `'b'` instead of creating a new row; i.e. at any time the database will only hold 1 row with `activity_name`=`'test'`. – JohnLBevan Oct 30 '16 at 09:22
2

According to the error code, your version does not support ON CONFLICT.
On PostreSQL 9.6 the error message is -

[Code: 0, SQL State: 42P10]  ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • 1
    So maybe i need another solution. usually activity_name is not unique, but I want to add a "test" row with status "a". when it changes to "b" I want to alter it to "a" instead of adding a new test row – Elad Benda2 Oct 30 '16 at 09:17
  • @EladBenda2, this is not clear. Could you add a scenario for example to your question? – David דודו Markovitz Oct 30 '16 at 09:24