Postgres 14

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?.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT)
  LANGUAGE plpgsql IMMUTABLE AS
$func$
BEGIN
   EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
   INTO  _out;
EXCEPTION WHEN others THEN
   -- do nothing: _out already carries default
END
$func$;
SELECT try_cast('3', NULL::INT) UNION ALL 
SELECT try_cast('3243234234323423432443', NULL::INT);  


try_cast


3

null


CREATE TABLE t
(
  a TEXT UNIQUE NOT NULL,
  
  b INT GENERATED ALWAYS AS (try_cast(SUBSTRING(a,      '^\|([-+]?\d+)\|'), NULL::INT)) STORED,

  c INT GENERATED ALWAYS AS (try_cast(SUBSTRING(a, '^\|[-+]?\d+\|([-+]?\d+)\|'), NULL::INT)) STORED

);
INSERT INTO t VALUES 
('|3|3| some stuff here'),
('|SE + 18.5D  some other stuff'),
('|54|45| yet more stuff'),
('|32|23| yet more |78|78| stuff'),
('|This is more text |11|111|22222||| and stuff |||||||'),
('|1  1|1  1  1|22222|'),
('|7125341246362562654562465|ahgsdfhgasfghasf'),
('|-567|-765|A test of negatives... '); 
8 rows affected

SELECT * FROM t;


a
b
c


|3|3| some stuff here
3
3

|SE + 18.5D  some other stuff
null
null

|54|45| yet more stuff
54
45

|32|23| yet more |78|78| stuff
32
23

|This is more text |11|111|22222||| and stuff |||||||
null
null

|1  1|1  1  1|22222|
null
null

|7125341246362562654562465|ahgsdfhgasfghasf
null
null

|-567|-765|A test of negatives...
-567
-765

a	b	c
\|3\|3\| some stuff here	3	3
\|SE + 18.5D some other stuff	null	null
\|54\|45\| yet more stuff	54	45
\|32\|23\| yet more \|78\|78\| stuff	32	23
\|This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|	null	null
\|1 1\|1 1 1\|22222\|	null	null
\|7125341246362562654562465\|ahgsdfhgasfghasf	null	null
\|-567\|-765\|A test of negatives...	-567	-765