Postgres 16

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 extension if not exists postgis;
create table my_table (id int, description text, geog geography(Point,4326));

CREATE EXTENSION

CREATE TABLE

copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

COPY 1

ERROR:  parse error - invalid geometry
HINT:  "(-" <-- parse error at position 2 within geometry
CONTEXT:  COPY my_table, line 1, column geog: "(-122.850334,49.189992)"

copy (select '1,"abc","-122.850334,49.189992"') to '/tmp/my_file.csv';

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

COPY 1

ERROR:  parse error - invalid geometry
HINT:  "-122.850334,4" <-- parse error at position 13 within geometry
CONTEXT:  COPY my_table, line 1, column geog: "-122.850334,49.189992"

copy (select '1,"abc","-122.850334 49.189992"') to '/tmp/my_file.csv';

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

COPY 1

ERROR:  parse error - invalid geometry
HINT:  "-122.850334 4" <-- parse error at position 13 within geometry
CONTEXT:  COPY my_table, line 1, column geog: "-122.850334 49.189992"

copy (select '1,"abc","(-122.850334 49.189992)"') to '/tmp/my_file.csv';

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

COPY 1

ERROR:  parse error - invalid geometry
HINT:  "(-" <-- parse error at position 2 within geometry
CONTEXT:  COPY my_table, line 1, column geog: "(-122.850334 49.189992)"

copy (select '1,"abc","point(-122.850334 49.189992)"') to '/tmp/my_file.csv';

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

select id,description,st_astext(geog) from my_table;

COPY 1

COPY 1

id
description
st_astext

1
abc
POINT(-122.850334 49.189992)

SELECT 1

truncate my_table;
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';
alter table my_table 
  alter column geog type text;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

select *,pg_typeof(geog) from my_table;

update my_table 
  set geog='point'||replace(geog,',',' ')
  returning *,pg_typeof(geog);

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geography(point,4326);

select *,pg_typeof(geog),st_astext(geog) from my_table;

TRUNCATE TABLE

COPY 1

ALTER TABLE

COPY 1

id
description
geog
pg_typeof

1
abc
(-122.850334,49.189992)
text

SELECT 1

id
description
geog
pg_typeof

1
abc
point(-122.850334 49.189992)
text

UPDATE 1

ALTER TABLE

id
description
geog
pg_typeof
st_astext

1
abc
0101000020E6100000522B4CDF6BB65EC0354069A851984840
geography
POINT(-122.850334 49.189992)

SELECT 1

truncate my_table;
copy (select '1,"abc","(-122.850334,49.189992)"') to '/tmp/my_file.csv';
alter table my_table
  drop column if exists geog,
  add column geog point;

copy my_table from '/tmp/my_file.csv' csv delimiter ',' quote '"';

select *,pg_typeof(geog) from my_table;

alter table my_table 
  alter column geog type geography(point,4326) 
  using geog::geometry(point)::geography(point,4326);

select *,pg_typeof(geog),st_astext(geog) from my_table;

TRUNCATE TABLE

COPY 1

ALTER TABLE

COPY 1

id
description
geog
pg_typeof

1
abc
(-122.850334,49.189992)
point

SELECT 1

ALTER TABLE

id
description
geog
pg_typeof
st_astext

1
abc
0101000020E6100000522B4CDF6BB65EC0354069A851984840
geography
POINT(-122.850334 49.189992)

SELECT 1