Postgres 9.6

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_js (
  id int
, j json                                 -- !
, modified_at timestamptz DEFAULT now()  -- !
);


CREATE TABLE tbl_jsb (
  id int
, j jsonb                                -- !
, modified_at timestamptz DEFAULT now()  -- !
);
-- doesn't work with json
SELECT (1, '{}', now())::tbl_js IS DISTINCT FROM (1, '{}', now())::tbl_js;
ERROR:  operator does not exist: json = json
LINE 2: SELECT (1, '{}', now())::tbl_js IS DISTINCT FROM (1, '{}', n...
                                        ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


-- but works with jsonb!
SELECT (1, '{}', now())::tbl_jsb IS DISTINCT FROM (1, '{}', now())::tbl_jsb;


?column?


f


CREATE OR REPLACE FUNCTION on_update_modified()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW IS DISTINCT FROM OLD THEN
      NEW.modified_at = now();
      RETURN NEW;
   ELSE
      RETURN NULL;  -- skip empty update
   END IF;
END
$func$;

CREATE TRIGGER mytable_update  -- doesn't work!
BEFORE UPDATE ON tbl_js
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

CREATE TRIGGER mytable_update  --  works
BEFORE UPDATE ON tbl_jsb
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();
-- INSERT = orthogonal to problem
INSERT INTO tbl_js VALUES (1, '{}', NULL);
1 rows affected

-- notably does not fail - because row comparison finishes at first column (!)
UPDATE tbl_js SET id = 2
RETURNING *;


id
j
modified_at


2
{}
2022-04-12 23:53:47.228915+01


-- fails as expected!
UPDATE tbl_js SET j = '[]'
RETURNING *;
ERROR:  could not identify an equality operator for type json
CONTEXT:  PL/pgSQL function on_update_modified() line 3 at IF


-- INSERT = orthogonal to problem
INSERT INTO tbl_jsb VALUES (1, '{}', now());
1 rows affected

-- works as expected!
UPDATE tbl_jsb SET j = '[]'
RETURNING *;


id
j
modified_at


1
[]
2022-04-12 23:53:47.233499+01