Postgres Update with json to record?

Question

i try to find a simple solution to update data with the json_to_record statement. I want to update the data every night with a json_array i get by web_craping. Any idea how i can solve the problem when theres a new article?
Thanks a lot :)

INSERT INTO prices
Select * from
  json_to_record(
    '{"distributor“: "1", "articelnr": 4711,  "price": „700“, "delivery": "too late", "created_on": "2022-09-25 03:14:07"}'
  ) AS x(distributor INT, articelnr VARCHAR, price VARCHAR, delivery VARCHAR, created_on TIMESTAMP)

Is it possible to use an update statement in a similar way like:

UPDATE prices
SET
  json_to_record(
    '{"distributor“: "1", "articelnr": 4711,  "price": „700“, "delivery": "too late", "created_on": "2022-09-25 03:14:07"}'
  ) AS x(distributor INT, articelnr VARCHAR, price VARCHAR, delivery VARCHAR, created_on TIMESTAMP)

I would suggest that you do not update at all but keep inserting (i.e. insert-only workflow). The table design must be changed with an autogenerated (surrogate) primary key and an index on `created_on`. Then you query the latest record (`order by created_on desc limit 1`) or use [distinct on](https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by). Keeping the history is free bonus of this approach. — Stefanov.sm, Oct 03 '22 at 19:53
"*Any idea how i can solve the problem when theres a new article?*" - not sure what problem you are referring to. But how do you identify a "**new** article"? By what criteria do you distinguish that from an existing article (which I suppose you want to update)? — Bergi, Oct 03 '22 at 20:59
Thanks a lot. I will check this the next days and will let you know :) — Alexander Hübner, Oct 04 '22 at 17:09

klin · Accepted Answer · 2022-10-03T20:24:33.140

According to UPDATE syntax it is possible to update a table using a sub-select, e.g.:

update prices set
(distributor, articelnr, price, delivery, created_on) = 
(
    select * 
    from json_to_record(
        '{"distributor": "1", "articelnr": 4711,  "price": "700", "delivery": "too late", "created_on": "2022-09-25 03:14:07"}'
    ) as x(distributor int, articelnr varchar, price varchar, delivery varchar, created_on timestamp)
)
where distributor = 1 and articelnr = 4711

Unfortunately, the sub-select results are not visible in the WHERE clause. Hence using the query in the FROM section seems more suitable, e.g.:

update prices p set
    (distributor, articelnr, price, delivery, created_on) = 
    (x.distributor, x.articelnr, x.price, x.delivery, x.created_on)
from (
    select * 
    from json_to_record(
        '{"distributor": "1", "articelnr": 4711,  "price": "700", "delivery": "too late", "created_on": "2022-09-25 03:14:07"}'
    ) as x(distributor int, articelnr varchar, price varchar, delivery varchar, created_on timestamp)
) as x
where p.distributor = x.distributor and p.articelnr = x.articelnr

Note, that you need unique columns (typically a primary key) to identify a row to update. Examples assume that they are (distributor, articelnr).

Thanks a lot. I will check this the next days and will let you know :) Right - the combination of distributor and articelnr is unique. I'm relative new to database and backend development. Sorry if my questions are kind of silly :) — Alexander Hübner, Oct 04 '22 at 17:10

Postgres Update with json to record?

1 Answers1