I have this query:
update client set start_date = current_date, email = '123@fakeemail.com' where client_id = 1 returning client_id, username, 1 as isSuccess
When the update is successful, it returns the following:
| client_id | username | isSuccess |
|---|---|---|
| 1 | test_name | 1 |
When the update doesn’t execute, it returns client_id, username, and isSuccess, but their values are blank.
What I’m having trouble with is customizing what returns when NO update is performed. If no update is performed, I need the following to return:
| client_id | username | isSuccess |
|---|---|---|
| NULL | NULL | 0 |
Are there any tricks for writing a RETURNING clause with an ELSE clause to get the above result set when no update is executed? OR are there any other methods to get the result set I need? The following code does not work –
update client
set start_date = current_date,
email = '123@fakeemail.com'
where client_id = 1
returning client_id, username, 1 as isSuccess
else client_id is null, username is null, 0 as isSuccess
Advertisement
Answer
There is no ELSE statement in the RETURNING clause, but you can do something similar. The basic idea is to take the result from your update, combine it with another row using a UNION and just return the first result.
Postgres doesn’t allow us to use an update statement with a UNION so we have to put the update statement in a CTE:
WITH client_update AS (
update client
set start_date = current_date,
email = '123@fakeemail.com'
WHERE client_id = 2
returning client_id, username, 1 as isSuccess, 1 as result_order
)
SELECT client_id, username, isSuccess FROM
(
SELECT client_id, username, isSuccess, result_order
FROM client_update
UNION ALL
SELECT null, null, 0, 2
) sub
ORDER BY result_order
LIMIT 1;
I added an addition column, result_order so we can manually specify which result to prefer. In this case, if the returning clause returns a result, we want to return that one, so it gets the 1.