common-table-expressionpostgresql-13

Why is postgres update not limited to the rows in the cte


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)

Solution

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