iterate over partitions
Hi All,
I have a large table partioned by month. I would like to run a query -
which adds derived data to the current data and inserts the data into a new
table. The new table is the target for users.
How can I iterate over the partition tables to insert data and build the
new table?
This is done monthly, am I better off creating a materalized view each
month?
Best,
Glenn
On 28/6/19 10:11 μ.μ., Glenn Schultz wrote:
Hi All,
I have a large table partioned by month. I would like to run a query - which adds derived data to the current data and inserts the data into a new table. The new table is the target for users.
How can I iterate over the partition tables to insert data and build the new table?
This is done monthly, am I better off creating a materalized view each month?
Well not using partitions yet in production, I can't say I can fully grasp your use case, but anyways, in order to iterate over partitions you'll have to look at : pg_partitioned_table , pg_inherits
or just enable log_statement , issue \d+ <your partitioned table> and look at the SQL produced.
Or just do :
select inhrelid::regclass from pg_inherits where inhparent = 'your partitioned table'::regclass;
Best,
Glenn
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt