How to increment value in postgres update statemen

问题:

When updating a relational table:

CREATE TABLE foo ( id serial primary key, credit numeric);
UPDATE foo SET bar = bar + $1 WHERE id = $2;

However the equivalent in JSON doesn't work:

CREATE TABLE foo ( id serial primary key, data json);
UPDATE foo SET data->'bar' = data->'bar' + $1 WHERE id = $2;

The error I get is error: syntax error at or near "->" - which is rather ambiguous.

How do I do this?

I am using postgres 9.3.4

---

In light of @GordonLinoff's comment below, I have created a feature request: https://postgresql.uservoice.com/forums/21853-general/suggestions/6466818-create-update-delete-on-json-keys

You can vote on it if you would like this feature too.

回答1:

Based on @joonas.fi's and pozs's answers, I came up with a slightly more 'beautiful' solution

UPDATE foo 
SET data = jsonb_set(data, '{bar}', (COALESCE(data->>'bar','0')::int + 1)::text::jsonb)
WHERE id = 1;

回答2:

You can do this with jsonb, at least with Postgres 9.5.2.

Given the following table:

CREATE TABLE users (id INT, counters JSONB NOT NULL DEFAULT '{}');

With sample data:

INSERT INTO users (id, counters) VALUES (1, '{"bar": 0}');

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 0}

You can increment "bar" key in JSON atomically:

UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 1}

It's not beautiful but it works.

Here it is broken down in steps:

You can set a key in jsonb to an explicit value by OR'ing the jsonb objects:

UPDATE users SET counters = counters || '{"bar": 314}'::jsonb WHERE id = 1;

SELECT * FROM users;

 id |     counters
----+--------------
  1 | {"bar": 314}

Now all that's left to do is build the string dynamically with the help of CONCAT(), at the same time demonstrating incrementing (by 27) an undefined key (defaulting initial value with help of COALESCE() ):

UPDATE users SET counters = counters || CONCAT('{"foo":', COALESCE(counters->>'foo','0')::int + 27, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |          counters
----+-------------------------
  1 | {"bar": 314, "foo": 27}

Bob's your uncle. :)