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 TABLE tab
(
  ln TEXT
);
with cte as (
select 'LINESTRING ( 1.0 2.0, 3 4)'                               as txt  UNion all
select 'LINESTRING M ( 1 2 3, 4 5 6.0)'                           as txt  union all
select 'LINESTRING ( 1 2, 3 4, 5 6)'                              as txt  union all
select 'LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)'               as txt  union all
select 'LINESTRING M ( 1 2 3.0, 4 5 6)'                           as txt  union all
select 'MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))' as txt 
)
INSERT INTO tab
select
    txt
from
    cte;
6 rows affected

SELECT * FROM tab;


ln


LINESTRING ( 1.0 2.0, 3 4)

LINESTRING M ( 1 2 3, 4 5 6.0)

LINESTRING ( 1 2, 3 4, 5 6)

LINESTRING M ( 1 2 3, 4 5 6.0, 7 8.00 9.0)

LINESTRING M ( 1 2 3.0, 4 5 6)

MULTILINESTRING M (( 1 2 3, 4 5 6),( 7 8 9, 10 11 12.0))


WITH cte AS
(
  SELECT 
    TRANSLATE(ln, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ()', '') AS tr
  FROM
    tab
), x AS
(
  SELECT
    TRIM(REGEXP_SPLIT_TO_TABLE(tr, ',')) AS sp 
  FROM
    cte
)
SELECT * FROM x;


sp


1.0 2.0

3 4

1 2 3

4 5 6.0

1 2

3 4

5 6

1 2 3

4 5 6.0

7 8.00 9.0

1 2 3.0

4 5 6

1 2 3

4 5 6

7 8 9

10 11 12.0


WITH cte AS
(
  SELECT 
    TRANSLATE(ln, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ()', '') AS tr
  FROM
    tab
), x AS
(
  SELECT
    TRIM(REGEXP_SPLIT_TO_TABLE(tr, ',')) AS sp 
  FROM
    cte
)
SELECT 
  sp,
  a.elem,
  a.nr,
  UNNEST(STRING_TO_ARRAY(x.sp, ' ')) WITH ORDINALITY a(elem, nr)
FROM x;
ERROR:  syntax error at or near "WITH"
LINE 18:   UNNEST(STRING_TO_ARRAY(x.sp, ' ')) WITH ORDINALITY a(elem,...
                                              ^