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
  • returning * will most certainly return all columns of the row that was updated: dbfiddle.uk/…
    – user1822
    Commented Mar 6, 2021 at 6:52

1 Answer 1

1

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
  • Of course there is a returning *dbfiddle.uk/…
    – user1822
    Commented Mar 6, 2021 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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