Postgres 13

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 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE t 
(
  a TEXT UNIQUE NOT NULL,
  b INT GENERATED ALWAYS AS ((SUBSTRING(a,      '^\|(\d+)\|'))::INT) STORED,
  c INT GENERATED ALWAYS AS ((SUBSTRING(a, '^\|\d+\|(\d+)\|'))::INT) STORED  
);
INSERT INTO t VALUES ('|3|3| some stuff here');
1 rows affected

INSERT INTO t VALUES ('|SE + 18.5D  some other stuff');
1 rows affected

INSERT INTO t VALUES ('|54|45| yet more stuff');
1 rows affected

INSERT INTO t VALUES ('|32|23| yet more |78|78| stuff');
1 rows affected

INSERT INTO t VALUES ('|This is more text |11|111|22222||| and stuff |||||||');
1 rows affected

INSERT INTO t VALUES ('This is more text |11|111|22222||| and stuff |||||||');
1 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

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


SELECT
  SUBSTRING(a, '^\|(\d+)\|'), SUBSTRING (a, '^\|\d+\|(\d+)\|')
FROM t;


substring
substring


3
3

null
null

54
45

32
23

null
null

null
null


SELECT 
  STRING_TO_ARRAY(a, '|'),
  ARRAY_TO_STRING((STRING_TO_ARRAY(a, '|'))[2:3], '|'),
  a
FROM t;


string_to_array
array_to_string
a


{"",3,3," some stuff here"}
3|3
|3|3| some stuff here

{"","SE + 18.5D  some other stuff"}
SE + 18.5D  some other stuff
|SE + 18.5D  some other stuff

{"",54,45," yet more stuff"}
54|45
|54|45| yet more stuff

{"",32,23," yet more ",78,78," stuff"}
32|23
|32|23| yet more |78|78| stuff

{"","This is more text ",11,111,22222,"",""," and stuff ","","","","","","",""}
This is more text |11
|This is more text |11|111|22222||| and stuff |||||||

{"This is more text ",11,111,22222,"",""," and stuff ","","","","","","",""}
11|111
This is more text |11|111|22222||| and stuff |||||||


SELECT
  a,
  CASE
    WHEN
      (STRING_TO_ARRAY(a, '|'))[2] ~ '^[0-9]' THEN ((STRING_TO_ARRAY(a, '|'))[2])::INT 
    ELSE NULL
  END AS f1,
  
  CASE
    WHEN
      (STRING_TO_ARRAY(a, '|'))[3] ~ '^[0-9]' THEN ((STRING_TO_ARRAY(a, '|'))[3])::INT 
    ELSE NULL
  END AS f2
  
FROM t;


a
f1
f2


|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
11

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


SELECT
  a,
  CASE
    WHEN 
      LEFT(a, 1) = '|' THEN 
        CASE
          WHEN (STRING_TO_ARRAY(a, '|'))[2] ~ '^[0-9]' AND
               (STRING_TO_ARRAY(a, '|'))[2] ~ '^[0-9]'
            THEN ((STRING_TO_ARRAY(a, '|'))[2])::INT
          ELSE NULL
        END
    ELSE NULL
  END AS n1,
  
  CASE
    WHEN 
      LEFT(a, 1) = '|' THEN 
        CASE
          WHEN (STRING_TO_ARRAY(a, '|'))[2] ~ '^[0-9]' AND
               (STRING_TO_ARRAY(a, '|'))[2] ~ '^[0-9]'
            THEN ((STRING_TO_ARRAY(a, '|'))[3])::INT
          ELSE NULL
        END
    ELSE NULL
  END AS n2

FROM t;
    


a
n1
n2


|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

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

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
This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|	null	null

string_to_array	array_to_string	a
{"",3,3," some stuff here"}	3\|3	\|3\|3\| some stuff here
{"","SE + 18.5D some other stuff"}	SE + 18.5D some other stuff	\|SE + 18.5D some other stuff
{"",54,45," yet more stuff"}	54\|45	\|54\|45\| yet more stuff
{"",32,23," yet more ",78,78," stuff"}	32\|23	\|32\|23\| yet more \|78\|78\| stuff
{"","This is more text ",11,111,22222,"",""," and stuff ","","","","","","",""}	This is more text \|11	\|This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|
{"This is more text ",11,111,22222,"",""," and stuff ","","","","","","",""}	11\|111	This is more text \|11\|111\|22222\|\|\| and stuff \|\|\|\|\|\|\|