0

let's say i have this update statement

UPDATE person set age = 20,location = 'us' WHERE id = 1234

based from documentation of postgresql on UPDATE , it can only return updated fields. but I want to get all fields regardless if it's updated or not. e.g i have name, first name, gender and where not updated in my UPDATE query.

RETURNING *

doesn't work

1
0

Your understanding of the documentation saying "you can only return columns you use" is incorrect

You can list all the columns you want to return. You can even list results of functions or use values that are nothing to do with the columns at all, like you would in a SELECT:

UPDATE person set age = age+1,location = 'us' WHERE id = 1234
RETURNING id, age, location, name, "first name", gender, "where", CONCAT("first name", ' ', name) as fullname, 1+2 as three
--                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                "i have name, first name, gender and where not updated"

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=5ad1bc7b45c4e73da351adedf987b0c3

And as HWNN points out, you can have RETURNING * .. check your original query for syntax errors

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.