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?

What? If you update, you want to store the mediaUrl in userId and vice versa? — Laurenz Albe
– Laurenz Albe, Commented Dec 7, 2023 at 13:24
No.. I'm trying to insert a record and provide the values for user_id and media_url. If a record already happens to exists with the provided user_id (as I've specified in the on conflict clause), then I'd like to update that record by user_id and set a new value for updated_at and media_url — Mike K
– Mike K, Commented Dec 7, 2023 at 13:29
But that's what your statement is already doing (except that you should remove the WHERE clause). — Laurenz Albe
– Laurenz Albe, Commented Dec 7, 2023 at 13:32
I've removed the where clause, but the problem is still in the fact that $1 is going to get mapped to values[0] (which is request.userId). I need it to map to request.mediaUrl. And now that I've removed the where user_id = $2 part, node-pg will start complaining about the update portion of the query and the fact that there are more values provided than columns being updated — Mike K
– Mike K, Commented Dec 7, 2023 at 13:37
It seems like you want DO UPDATE SET (updated_at, media_url) = (CURRENT_TIMESTAMP, $2)? Nothing says the the parameters have to occur in the query only once or in order. — Bergi
– Bergi, Commented Dec 7, 2023 at 14:35

Zegarek · Accepted Answer · 2023-12-07 14:27:52Z

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);

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.
You're free to reorder the update column list.
When value order is problematic, you can swap out the parenthesized syntax in place of comma separated column=new_value pairs.

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

Collectives™ on Stack Overflow

Re-using parameters passed to node-postgres upsert query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related