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 OR REPLACE FUNCTION cast_to(_js jsonb, INOUT _target_type anyelement = NULL::text) AS
$func$
BEGIN
   SELECT _js ->> pg_typeof(_target_type)::text
   INTO   _target_type;
END
$func$  LANGUAGE plpgsql;

CREATE FUNCTION


-- test table
CREATE TABLE tbl (id int, js jsonb);
INSERT INTO tbl VALUES
  (1, '{"int":66, "integer":"42", "text":"abc", "date":"2019-12-12"}')
, (2, '{"point":"(1,2)"}');

CREATE TABLE



INSERT 0 2


SELECT *
     , cast_to(js, NULL::int  ) AS my_int
     , cast_to(js, NULL::text ) AS my_text
     , cast_to(js, NULL::date ) AS my_date
     , cast_to(js, NULL::point) AS my_point
     , cast_to(js             ) AS my_default -- no 2nd param
FROM   tbl;


id
js
my_int
my_text
my_date
my_point
my_default


1
{"int": 66, "date": "2019-12-12", "text": "abc", "integer": "42"}
42
abc
2019-12-12
null
abc

2
{"point": "(1,2)"}
null
null
null
(1,2)
null



SELECT 2

id	js	my_int	my_text	my_date	my_point	my_default
1	{"int": 66, "date": "2019-12-12", "text": "abc", "integer": "42"}	42	abc	2019-12-12	null	abc
2	{"point": "(1,2)"}	null	null	null	(1,2)	null