Postgres 12

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  tbl(id int, ts timestamp, txt text);
INSERT INTO tbl  VALUES
   (1,'2020-01-01 01:00', NULL)
 , (2, NULL, NULL);

CREATE TABLE



INSERT 0 2


-- 0. fails with timestamp!
UPDATE tbl f
SET    id = t.id
     , ts = t.ts
FROM  (
   (SELECT id, ts FROM tbl LIMIT 0)  -- parenthesis needed with LIMIT
   UNION ALL
   VALUES
      (1, '2020-09-01 01:21')        -- no type casts here
   ) t  -- column names and types are already defined
WHERE  f.id = t.id;

ERROR:  UNION types timestamp without time zone and text cannot be matched


-- 1. works
UPDATE tbl f
SET    id = t.id
     , ts = t.ts
FROM  (
   (SELECT id, ts FROM tbl LIMIT 0)        -- parenthesis needed with LIMIT
   UNION ALL SELECT 1, '2020-09-02 01:21'  -- no type casts here
   UNION ALL SELECT 2, NULL
   ) t  -- column names and types are already defined
WHERE  f.id = t.id
RETURNING *;


id
ts
txt
id
ts


1
2020-09-02 01:21:00
null
1
2020-09-02 01:21:00

2
null
null
2
null



UPDATE 2


-- also fails! added parentheses force early type resolution
(SELECT id, ts FROM tbl LIMIT 0)  -- parenthesis needed with LIMIT
UNION ALL
(
SELECT 1, '2020-09-01 01:21'
UNION ALL
SELECT 2, NULL
);

ERROR:  UNION types timestamp without time zone and text cannot be matched
LINE 5: SELECT 1, '2020-09-01 01:21'
                  ^


-- 2.
UPDATE tbl f
SET    id = t.id
     , ts = t.ts
FROM  (
   VALUES 
     ((SELECT id FROM tbl LIMIT 0)
    , (SELECT ts FROM tbl LIMIT 0))  -- get type for each col individually
    , (1, '2020-09-03 01:21')
    , (2, NULL)
   ) t (id,ts)  -- columns names not defined yet, only types.
WHERE  f.id = t.id
RETURNING f.*;


id
ts
txt


1
2020-09-03 01:21:00
null

2
null
null



UPDATE 2


-- 3a.
UPDATE tbl f
SET id = (t.r).id         -- parenthesis needed to make syntax unambiguous
  , ts = (t.r).ts
FROM (
   VALUES
      ('(1,"2020-09-04 01:11",)'::tbl)  -- columns in default order of table
     ,('(2,,)')                         -- nothing after last comma for NULL
   ) t (r)                              -- column name for row type
WHERE  f.id = (t.r).id
RETURNING f.*;


id
ts
txt


1
2020-09-04 01:11:00
null

2
null
null



UPDATE 2


-- 3b.
UPDATE tbl f
SET id = (t.r).id         -- parenthesis needed to make syntax unambiguous
  , ts = (t.r).ts
FROM (
  VALUES
      ((NULL::tbl))                -- row of NULL values
    , ('(1,"2020-09-04 02:22",)')  -- uniform ROW value syntax for all
    , ('(2,,)')
   ) t(r)
WHERE  f.id = (t.r).id
RETURNING f.*;


id
ts
txt


1
2020-09-04 02:22:00
null

2
null
null



UPDATE 2


-- 4a.
UPDATE tbl f
SET    id = t.id
     , ts = t.ts
FROM (
   VALUES
      (('(1,"2020-09-05 01:11",)'::tbl).*)  -- decomposed row of values
    , (2, NULL, NULL)
   ) t(id, ts)  -- arbitrary column names (I made them match)
WHERE  f.id = t.id
RETURNING f.*;


id
ts
txt


1
2020-09-05 01:11:00
null

2
null
null



UPDATE 2


-- 4b.
UPDATE tbl f
SET    id = t.id
     , ts = t.ts
FROM (
   VALUES
      ((NULL::tbl).*)  -- row of NULL values
    , (1, '2020-09-05 02:22', NULL)    -- uniform syntax for all
    , (2, NULL, NULL)
    ) t(id, ts)  -- arbitrary column names (I made them match)
WHERE  f.id = t.id
RETURNING f.*;


id
ts
txt


1
2020-09-05 02:22:00
null

2
null
null



UPDATE 2


-- 5.
UPDATE tbl f
SET   (  id,   ts)
    = (t.id, t.ts)  -- short notation, see below
FROM (
   VALUES
      ((NULL::tbl).id, (NULL::tbl).ts)  -- subset of columns
    , (1, '2020-09-06 01:21')
    , (2, NULL)
   ) t(id, ts)  -- arbitrary column names (I made them match)
WHERE  f.id = t.id
RETURNING f.*;


id
ts
txt


1
2020-09-06 01:21:00
null

2
null
null



UPDATE 2
id	ts	txt	id	ts
1	2020-09-02 01:21:00	null	1	2020-09-02 01:21:00
2	null	null	2	null