Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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
returning *will most certainly return all columns of the row that was updated: dbfiddle.uk/…