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 postgis;
create table table1(column1,column2,geom) as values
  (2,'01',st_geomfromtext('POLYGON((0 0,0 9,9 9,9 0,0 0))')),
  (3,'01',st_geomfromtext('POLYGON((10 10,10 30,30 30,30 10,10 10))'));
create schema "schema";

CREATE EXTENSION

SELECT 2

CREATE SCHEMA

--https://stackoverflow.com/q/78207893/5298879
CREATE TABLE schema.table2 AS
SELECT "column1", "column2", dp.geom 
FROM table1 WHERE column2 = "01", st_dumppoints(st_generatepoints(table1.geom, CAST(table1.column1 AS INT))) AS dp;

ERROR:  syntax error at or near ","
LINE 4: FROM table1 WHERE column2 = "01", st_dumppoints(st_generatep...
                                        ^

CREATE TABLE schema.table2 AS
SELECT column1, column2, dp.geom 
FROM table1 
CROSS JOIN st_dumppoints(
             st_generatepoints(
                geom
               ,column1::int, seed=>1)) AS dp
WHERE column2 = '01';

select column1, column2, st_astext(geom) from schema.table2;

SELECT 5

column1
column2
st_astext

2
01
POINT(5.86390304259572 1.927184679457311)

2
01
POINT(3.212729817760193 7.706451275408435)

3
01
POINT(16.515447825106357 22.141316310508124)

3
01
POINT(25.303230663190877 25.957851538628983)

3
01
POINT(13.569699797511326 18.562723639342707)

SELECT 5

CREATE TABLE schema.table3 AS
SELECT column1, 
       column2,
       (st_dumppoints(st_generatepoints(geom, column1::int,seed=>1)) ).geom
FROM table1
WHERE column2 = '01';

select column1, column2, st_astext(geom) from schema.table3;

SELECT 5

column1
column2
st_astext

2
01
POINT(5.86390304259572 1.927184679457311)

2
01
POINT(3.212729817760193 7.706451275408435)

3
01
POINT(16.515447825106357 22.141316310508124)

3
01
POINT(25.303230663190877 25.957851538628983)

3
01
POINT(13.569699797511326 18.562723639342707)

SELECT 5

--don't re-use the seed if you want random
CREATE TABLE schema.table4 AS
SELECT column1, 
       column2,
       (st_dumppoints(st_generatepoints(geom, column1::int)) ).geom
FROM table1
WHERE column2 = '01';

select column1, column2, st_astext(geom) from schema.table4;

SELECT 5

column1
column2
st_astext

2
01
POINT(0.642306250331938 4.079748229020555)

2
01
POINT(5.842479219944558 6.430468359305435)

3
01
POINT(13.888718509367235 29.654629319274562)

3
01
POINT(11.248055401297616 15.366008792124106)

3
01
POINT(25.826775075530577 24.232475892529102)

SELECT 5

--This `column2 = "01"` is technically valid, just not what you want
create table please_do_not_name_columns_like_this("01" text,column2 text);

insert into please_do_not_name_columns_like_this values 
  ('text value 1','text value 1'),
  ('text value 1','text value 2');

SELECT * FROM please_do_not_name_columns_like_this
WHERE column2 = "01";

CREATE TABLE

INSERT 0 2

01
column2

text value 1
text value 1

SELECT 1

--This `column2 = "01"` is technically valid, just not what you want
create table definitely_do_not_name_columns_like_this("""" text," " text);

insert into definitely_do_not_name_columns_like_this values 
  ('text value 1','text value 1'),
  ('text value 1','text value 2');

SELECT * FROM definitely_do_not_name_columns_like_this
WHERE " " = """";

select column_name from information_schema.columns 
where table_name='definitely_do_not_name_columns_like_this';

CREATE TABLE

INSERT 0 2

"
 
text value 1
text value 1

SELECT 1

column_name

"

SELECT 2

create table zero_columns_five_rows as 
select/*note there is nothing in here but an in-line comment*/
from generate_series(1,5);

select * from zero_columns_five_rows;

SELECT 5

SELECT 5

column1	column2	st_astext
2	01	POINT(5.86390304259572 1.927184679457311)
2	01	POINT(3.212729817760193 7.706451275408435)
3	01	POINT(16.515447825106357 22.141316310508124)
3	01	POINT(25.303230663190877 25.957851538628983)
3	01	POINT(13.569699797511326 18.562723639342707)

column1	column2	st_astext
2	01	POINT(0.642306250331938 4.079748229020555)
2	01	POINT(5.842479219944558 6.430468359305435)
3	01	POINT(13.888718509367235 29.654629319274562)
3	01	POINT(11.248055401297616 15.366008792124106)
3	01	POINT(25.826775075530577 24.232475892529102)