Re-using parameters passed to node-postgres upsert query

Question

I'm using node-pg and I've decided to refactor some code that would first make a select query to see if a record exists and then make a second query to either insert or update a record.

Suppose the following table structure:

CREATE TABLE IF NOT EXISTS my_schema.user_profile (
    id SERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES %%.user (id) UNIQUE NOT NULL,
    media_url VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc'),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'utc')
);

I've landed on something like the below,

const request = {
    userId: 123,
    mediaUrl: 'https://...'
};
const query = `
    INSERT INTO my_schema.user_profile
        (user_id, media_url)
    VALUES
        ($1, $2)
    ON CONFLICT (user_id)
        DO UPDATE SET (
            updated_at,
            media_url
        ) = (
            CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC',
            $1
        )
`;

const values = [
    request.userId,
    request.mediaUrl
];

const result = await client.query(query, values);

However the problem here is that values is valid only for the insert part of the query. If a record exists that needs to be updated, then this array of values is not correct, it would have to be:

const values = [
    request.mediaUrl,
];

But then node-pg will start complaining about the update portion of the query having more columns being updated than paramterized provided.

How would I be able to get something like this to work?

Answer 1

const request = {
    userId: 123,
    mediaUrl: 'https://...'
};
const query = `
    INSERT INTO my_schema.user_profile
        (user_id, media_url)
    VALUES
        ($1, $2)
    ON CONFLICT (user_id)
        DO UPDATE SET
            updated_at = CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC',
            media_url  = excluded.media_url
`;

const values = [
    request.userId,
    request.mediaUrl
];

const result = await client.query(query, values);

1. The scope of the update taking place in the on conflict clause is already limited to the context of the conflicting row, it's not a full-fledged, standalone update that you'd have to specifically target to get there, so the where (now removed) was unnecessary.
2. You're free to reorder the update column list.
3. When value order is problematic, you can swap out the parenthesized syntax in place of comma separated column=new_value pairs.
4. You can re-use a value from your insert payload by addressing it as excluded.media_url: demo at db<>fiddle

   prepare insert_s(bigint,text) as
   INSERT INTO my_schema.user_profile
           (user_id, media_url)
       VALUES
           ($1, $2)
       ON CONFLICT (user_id)
           DO UPDATE SET (
               updated_at,
               media_url
           ) = (
               CURRENT_TIMESTAMP(0) AT TIME ZONE 'UTC',
               excluded.media_url
           )
   RETURNING *;
   
   execute insert_s(1,'example.com/a_completely_new_pic.jpg');
   

   id	user_id	media_url	created_at	updated_at
   1	1	example.com/a_completely_new_pic.jpg	2023-12-07 13:53:14.593296	2023-12-07 13:53:15