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