Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table blocks 
(
  id integer, 
  created_at date, 
  data int
)
partition by range (created_at);

create table blocks_p1 
  partition of blocks
  for values from ('2020-01-01') to ('2021-01-01');
  
create table blocks_p2
  partition of blocks
  for values from ('2021-01-01') to ('2022-01-01');
  
insert into blocks values 
(1, date '2020-03-04', 42),
(2, date '2021-03-04', 43);

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 2


select cols.table_name, cols.column_name, cols.ordinal_position
from information_schema.columns cols
  join information_schema.tables tbl on (tbl.table_schema, tbl.table_name) = (cols.table_schema, cols.table_name)
where tbl.table_name like 'blocks%'
order by cols.table_name, cols.ordinal_position


table_name
column_name
ordinal_position


blocks
id
1

blocks
created_at
2

blocks
data
3

blocks_p1
id
1

blocks_p1
created_at
2

blocks_p1
data
3

blocks_p2
id
1

blocks_p2
created_at
2

blocks_p2
data
3



SELECT 9


alter table blocks
   add column client_subnet text;
   

ALTER TABLE


select cols.table_name, cols.column_name, cols.ordinal_position
from information_schema.columns cols
  join information_schema.tables tbl on (tbl.table_schema, tbl.table_name) = (cols.table_schema, cols.table_name)
where tbl.table_name like 'blocks%'
order by cols.table_name, cols.ordinal_position


table_name
column_name
ordinal_position


blocks
id
1

blocks
created_at
2

blocks
data
3

blocks
client_subnet
4

blocks_p1
id
1

blocks_p1
created_at
2

blocks_p1
data
3

blocks_p1
client_subnet
4

blocks_p2
id
1

blocks_p2
created_at
2

blocks_p2
data
3

blocks_p2
client_subnet
4



SELECT 12


select *
from blocks;


id
created_at
data
client_subnet


1
2020-03-04
42
null

2
2021-03-04
43
null



SELECT 2

table_name	column_name	ordinal_position
blocks	id	1
blocks	created_at	2
blocks	data	3
blocks_p1	id	1
blocks_p1	created_at	2
blocks_p1	data	3
blocks_p2	id	1
blocks_p2	created_at	2
blocks_p2	data	3