Trying to constrain PUT request to update if unique for key - PostgreSQL

Question

I have the following code for pg-promise that works:

function putUser(req, res, next) {
    var values = req.body
    var query = 'insert into users(fullname, fname, lname, email, region, area, labname, labcode) values(${fullname}, ${fname}, ${lname}, ${email}, ${region}, ${area}, ${labname}, ${labcode})'
    db.none(query, values)
...

When I try to add a second user w/ same fullname I get --> "duplicate key value violates unique constraint "users_fullname_key"

Which is sorta what I want, but I actually don't want it to error, just update the user and report back OK.

I read this SO post --> how do I insert many, if it already exists, dont do anything with PG-promise

and it states I should be able to add ON DUPLICATE KEY UPDATE but when I add that, I get "error near DUPLICATE" message.

I also read about pgp.helpers. but I'd prefer to just run straight SQL, not use helpers as I'm trying to avoid "middlemen" as much as possible, hence why I moved away from using an ORM like TypeORM.

Here is my setup SQL Statement FYI:

CREATE TABLE users (
  ID SERIAL PRIMARY KEY,
  fullname VARCHAR UNIQUE NOT NULL,
...

can you please post the complete code with the on duplicate code in it — nbk, Jul 26 '23 at 20:52
also in postgres it i `ON CONFLICT`see https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql — nbk, Jul 26 '23 at 20:54
@NKB `var query = 'insert into users(fullname, fname, lname, email, region, area, labname, labcode) values(${fullname}, ${fname}, ${lname}, ${email}, ${region}, ${area}, ${labname}, ${labcode}) ON DUPLICATE KEY UPDATE` — FreeSoftwareServers, Jul 26 '23 at 20:57
@nbk is it that I literally copied only that `KEY UPDATE` part? That link shows I need to give further statements afterwards I think. Any way I can say `ON DUPLICATE UPDATE EVERY FIELD` or do I need to repeat each field? I basically just want to overwrite row when it exists. — FreeSoftwareServers, Jul 26 '23 at 21:02
https://www.postgresql.org/docs/current/sql-insert.html yes you neeed to specify all columns — nbk, Jul 26 '23 at 21:11
Sounds like an issue with posgresql queries, and not with `pg-promise`. — vitaly-t, Jul 27 '23 at 03:32
@vitaly-t thats correct. I just have to write out the answer, but I figured it out. Unless somebody else writes it first — FreeSoftwareServers, Jul 27 '23 at 16:57
Anybody care to explain the close vote? Is this too simple? Just because you think its simple, doesn't mean that it won't help future people. — FreeSoftwareServers, Jul 27 '23 at 17:17

score 0 · Accepted Answer · answered Jul 28 '23 at 20:05

I had two options I debated and both worked:

var query = 'insert into users(fullname, fname, lname, email, region, area, labname, labcode) ' +
            'values(${fullname}, ${fname}, ${lname}, ${email}, ${region}, ${area}, ${labname}, ${labcode}) ' +
            'ON CONFLICT (fullname) DO UPDATE SET ' +
            'labcode = excluded.labcode'

var query = 'insert into users(fullname, fname, lname, email, region, area, labname, labcode) values(${fullname}, ${fname}, ${lname}, ${email}, ${region}, ${area}, ${labname}, ${labcode}) ON CONFLICT DO NOTHING'

Trying to constrain PUT request to update if unique for key - PostgreSQL

1 Answers1

Linked