Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tab(i INT);
INSERT INTO tab VALUES (1);
INSERT INTO tab VALUES(2);
1 rows affected

1 rows affected

SELECT * FROM tab


i


1

2


UPDATE tab
SET i =10 * i
RETURNING COUNT(*);
-- aggregate functions are not allowed in RETURNING
ERROR:  aggregate functions are not allowed in RETURNING
LINE 3: RETURNING COUNT(*);
                  ^


SELECT COUNT(*), STRING_AGG(i, ',') OVER(ORDER BY i)
FROM (UPDATE tab
      SET i =10 * i
      RETURNING *) s
-- syntax error at or near "SET"
ERROR:  syntax error at or near "SET"
LINE 3:       SET i =10 * i
              ^


WITH cte AS (
UPDATE tab
      SET i =10 * i
      RETURNING *
)
SELECT COUNT(*), STRING_AGG(i::text, ',' ORDER BY i)
FROM cte


count
string_agg


2
10,20