I want to increase total by one if the topic already exist else do normal insert-CodePudding

Table:

CREATE TABLE count_demo
(
    topic VARCHAR primary key,
    total integer
)

Query

INSERT INTO count_demo (topic, total) 
VALUES ('topic1', 0) 
    ON CONFLICT DO UPDATE SET total = total   1

Problem

Error: column reference total is ambiguous

CodePudding user response：

INSERT INTO count_demo (topic, total)
VALUES ('topic1', 0) 
ON CONFLICT (topic) 
DO 
   UPDATE SET total = count_demo.total   1;

CodePudding user response：

Below query worked for me:

INSERT INTO count_demo (topic, total) 
VALUES ('topic1', 0) 
    ON CONFLICT DO UPDATE SET total = count_demo.total   1