Postgres - upsert on passed parameter-CodePudding

In my project I am using Postgres 12 and I want to use one sql query to INSERT OR UPDATE..

My syntax is not correct.

UPDATE: Insert works but updating does not.

ERROR: Invalid parameter number: :name"

 'INSERT INTO user (
         name, url
  ) VALUES (:name, :url)
    ON CONFLICT (id)
    WHERE id = :userId
    DO UPDATE SET
             name = :name,
             url = :url'

I am using this EXAMPLE to do UPSERT and I want to UPDATE if userId is passed and if not to INSERT new row.

Thanks

CodePudding user response：

BEGIN;
CREATE TABLE users (
    user_id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name text,
    url text
);
INSERT INTO users (name, url)
    VALUES ('Hello', 'world');
COMMIT;

using psql: https://www.postgresql.org/docs/current/app-psql.html
set variable in psql: How do you use script variables in psql?
You can also set variable in an transaction.

BEGIN;
\set name 'hi'
\set url 'yech'
INSERT INTO users (user_id, name, url)
    VALUES (1, :'name', :'url')
ON CONFLICT (user_id)
    DO UPDATE SET
        name = EXCLUDED.name, url = EXCLUDED.url
    RETURNING
        *;
TABLE users;
COMMIT;