0

This is PostgreSQL 13.10...

This question is about using UPDATE with a CTE on a VIEW (though I tried eliminating the VIEW and still have the same issue).

I am using a REST API frontend that generates SQL queries for CSV updates using a template like:

WITH cte AS (SELECT '[...CSV data encoded as JSON...]'::json AS data)
UPDATE t SET c1 = _.c1, c2 = _.c2, ...
FROM (SELECT * FROM JSON_POPULATE_RECORDSET(NULL::t, (SELECT data FROM cte))) _;

It seems to be updating all the rows in the table and not just the ones referenced in the CTE:

test=# create table t (tid int, tval text);
CREATE TABLE
test=# insert into t (tid, tval) select generate_series(1,100000), md5(random()::text);
INSERT 0 100000
test=# create view v as select tid as id, tval as val from t;
CREATE VIEW
test=# select count(*) from v;
 count
--------
 100000
(1 row)

test=# WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
test-# SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte)) _;
  id   |  val
-------+-------
 99991 | test3
 99992 | test4
(2 rows)

test=# begin;
BEGIN
test=*# WITH cte as (SELECT '[{"id":"99991","val":"test3"},{"id":"99992","val":"test4"}]'::json AS data)
test-*# UPDATE v SET val = _.val, id = _.id
test-*# FROM (SELECT * FROM json_populate_recordset (NULL::v, (SELECT data FROM cte))) _;
UPDATE 100000
test=*#
test=*# select count(*) from v where val like 'test%';
 count
--------
 100000
(1 row)
user9645
  • 6,286
  • 6
  • 29
  • 43

1 Answers1

1

There's no WHERE clause in the UPDATE; therefore, all of the rows in v are updated.

JohnH
  • 2,001
  • 1
  • 2
  • 13
  • Thanks - I discovered that the REST API frontend we're using (PostgREST) does not actually support bulk updates at all, thus the broken query. It does have bulk UPSERT, but we can't use that as we don't want any INSERTs happening. – user9645 Apr 13 '23 at 11:52