I don't have time now to create an example, but I can point you at:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
5.12.2.2. Partition Maintenance
"As an alternative to creating a new partition, it is sometimes more
convenient to create a new table separate from the partition structure
and attach it as a partition later. ... "
Read the section starting above.
Thanks Adrian,
I tried similar things as in the doc, but I found my mistake.
I casually did not put a constraint of not null on the partition column. as a result i think to invalidate any of that data, it does a scan despite the boundary constraint.
which when i used not null for the partition column, the scans stopped.
although my question was asking something else, but maybe i will not be able to put it clearly.
at least this part got addressed well. thanks for your help and time.
/*
postgres=# drop table t;
DROP TABLE
postgres=# drop table t1;
ERROR: table "t1" does not exist
postgres=# create table t(col1 int) partition by list(col1); CREATE TABLE
postgres=# create table t1 (LIKE t INCLUDING DEFAULTS INCLUDING CONSTRAINTS); CREATE TABLE
postgres=# insert into t1 select 0 from generate_series(1, 100000) x;
INSERT 0 100000
postgres=# alter table t1 add constraint col10 check (col1 = 0); ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tables where relname = 't1';
relname | seq_scan | last_seq_scan | age | seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 1 | 2024-10-17 05:33:40.080319+00 | -00:00:02.488752 | 100000
(1 row)
postgres=# alter table t attach partition t1 for values in (0); -- note the scan count increased as col1 was not set to not null
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tables where relname = 't1';
relname | seq_scan | last_seq_scan | age | seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 2 | 2024-10-17 05:33:48.248001+00 | -00:00:01.510694 | 200000
(1 row)
postgres=# drop table t;
DROP TABLE
postgres=# drop table t1;
ERROR: table "t1" does not exist
postgres=# create table t(col1 int not null) partition by list(col1); CREATE TABLE
postgres=# create table t1 (LIKE t INCLUDING DEFAULTS INCLUDING CONSTRAINTS); CREATE TABLE
postgres=# insert into t1 select 0 from generate_series(1, 100000) x; INSERT 0 100000
postgres=# alter table t1 add constraint col10 check (col1 = 0);
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tables where relname = 't1';
relname | seq_scan | last_seq_scan | age | seq_tup_read
---------+----------+-------------------------------+-----------------+--------------
t1 | 1 | 2024-10-17 05:34:41.363401+00 | -00:00:03.19836 | 100000
(1 row)
postgres=# alter table t attach partition t1 for values in (0); -- note no scan count bump as not null on column to avoid scanning to filter those values -
ALTER TABLE
postgres=# select relname,seq_scan,last_seq_scan, age(last_seq_scan, current_timestamp), seq_tup_read from pg_stat_user_tables where relname = 't1';
relname | seq_scan | last_seq_scan | age | seq_tup_read
---------+----------+-------------------------------+------------------+--------------
t1 | 1 | 2024-10-17 05:34:41.363401+00 | -00:00:08.241812 | 100000
(1 row)
*/
