Re: iterate over partitions

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