Обсуждение: 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 andinserts 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 iterateover 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