Getting error when trying to use `CASE` in PostgreSQL-CodePudding

Good afternoon, I'm trying to run a query in PgAdmin to increment a value in my table if it exists, if not it will create the row, I'm getting an error however

ERROR:  syntax error at or near "CASE"
LINE 1: CASE 
        ^
SQL state: 42601
Character: 1

CASE 
    WHEN EXISTS(SELECT * FROM counter WHERE user_id = '321') THEN
        UPDATE counter
            SET counter = counter   1
        WHERE user_id = '321'
    ELSE 
        INSERT INTO counter (user_id) VALUES ('321')  -- `counter` column is `SMALLINT DEFAULT 0`
END

I've also tried using the upsert

INSERT INTO cookies_counter (user_id) VALUES ('321')
ON CONFLICT (user_id) DO 
UPDATE SET counter = counter   1
WHERE user_id = '321'

It gives me a different error instead

ERROR:  column reference "counter" is ambiguous
LINE 2: ON CONFLICT (user_id) DO UPDATE SET counter = counter   1
                                                      ^
SQL state: 42702
Character: 100

CodePudding user response：

Something like this might work, but it depends a little on the table structure:

CREATE  TABLE counter(user_id INT UNIQUE, counter smallint default 0);
INSERT INTO counter (user_id) 
VALUES ('321')
ON CONFLICT (user_id)
DO UPDATE
SET counter = counter.counter   1::SMALLINT;