Postgresql – how to return all the columns not just the updated columns when update in postgresql

postgresql

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

Best Answer

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

Related Solutions

PostgreSQL Table Update – How to Update a Table After Insertion to Another Table

The cleanest solution is to remove the redundant number_of_cars column completely. Your solution (as well as many related ideas floating around) are not safe against concurrent write access.

Instead, create a VIEW (or a MATERIALIZED VIEW to optimize read performance) like:

CREATE VIEW person_cars AS
SELECT *
FROM  (
   SELECT owner AS personid, count(*) AS number_of_cars
   FROM   car 
   GROUP  BY 1
   ) c
JOIN person p USING (personid);

Or you could have a custom materialized view where you only update persons that had actual changes. Related example:

Refresh materalized view incrementally in PostgreSQL

If you insist on your original idea (and concurrent write access is not an issue), you could use a trigger solution. Basic example:

How do I make a trigger to update a column in another table?

You need to cover all possible changes: INSERT, UPDATE, DELETE on either table. @bgiles added more considerations.

Postgresql – Querying JSONB in PostgreSQL

This does not work because it's trying to cast a jsonb value to integer.

select data->'name' as name from persons where cast(data->'age' as int) > 25

This would actually work:

SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25;

Or shorter:

SELECT data->'name' AS name FROM persons WHERE (data->>'age')::int > 25;

And this:

SELECT data->'name' AS name FROM persons WHERE data->>'name' > 'Jenny';

Seems like confusion with the two operators -> and ->> and operator precedence. The cast :: binds stronger than the json(b) operators.

Figure out type dynamically

This is the more interesting part of your question:

the type of age in the JSON document is number anyway, so why can't PostgreSQL figure out that by itself?

SQL is a strictly typed language, it does not allow the same expression to evaluate to integer in one row and to text in the next. But since you are only interested in the boolean result of the test, you can get around this restriction with a CASE expression that forks depending on the result of jsonb_typeof():

SELECT data->'name'
FROM   persons
WHERE  CASE jsonb_typeof(data->'age')
        WHEN 'number'  THEN (data->>'age')::numeric > '25' -- treated as numeric
        WHEN 'string'  THEN data->>'age' > 'age_level_3'   -- treated as text
        WHEN 'boolean' THEN (data->>'age')::bool           -- use boolean directly (example)
        ELSE FALSE                                         -- remaining: array, object, null
       END;

An untyped string literal to the right of the > operator is coerced to the respective type of the value to the left automatically. If you put a typed value there, the type has to match or you have to cast it explicitly - unless there is adequate implicit cast registered in the system.

If you know that all numeric values are actually integer, you can also:

... (data->>'age')::int > 25 ...

Best Answer

Related Solutions

PostgreSQL Table Update – How to Update a Table After Insertion to Another Table

Postgresql – Querying JSONB in PostgreSQL

Figure out type dynamically

Related Question