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?
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);
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.column=new_value
pairs.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 |