Postgres - upsert on passed parameter

Question

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

The syntax is `INSERT INTO Table(Columns, ...) VALUES ... ON CONFLICT DO UPDATE SET ...` as you can see in the linked question. No `SET` clauses in an `INSERT`. — Jeroen Mostert, Aug 10 '22 at 10:02
Get rid of the `SET` and use `VALUES` instead, as the SQL Standard mandates. `INSERT INTO t (col) VALUES ('VAL') ON CONFLICT DO UPDATE ...` — Jim Jones, Aug 10 '22 at 10:07
The syntax you are using is not the syntax the linked questions shows you and is [not documented in the manual](https://www.postgresql.org/docs/current/sql-insert.html) . Why do you think inventing your own syntax would work? — , Aug 10 '22 at 10:07
I am pretty new in writing postgres queries so I was just asking for a little help for starters.. Thanks @a_horse_with_no_name — Filip Stojavonic, Aug 10 '22 at 10:12
I have updated my post with preposed solutions still have an error. @JeroenMostert — Filip Stojavonic, Aug 10 '22 at 10:25
Your statement now no longer uses `:name` at all. Presumably you intended `VALUES (:name, :url)`. Though it's not clear where `EXCLUDED.created_at` is supposed to be coming from either. — Jeroen Mostert, Aug 10 '22 at 10:33

score 0 · Accepted Answer · answered Aug 10 '22 at 14:42

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;

Postgres - upsert on passed parameter

1 Answers1