Hello Yuxia,
As time partitioning is a natively available, I recommend you to use
time-partitioning, as it is.
You can find below an example I used to subpartition my transaction table
by quaterly partition.
I have a repository table named miniexport.tby_part_followup to pilot
partition creation.
FOR live_part_table IN
SELECT id_business_unit,
date_trunc ('quarter', tpf_dt_min) as min_timestamp,
date_trunc ('quarter', tpf_dt_max + interval '3 months') as
max_timestamp
FROM miniexport.tby_part_followup
WHERE tpf_bool_part_BU_Quarter_ok = false
ORDER BY id_business_unit
LIMIT bi_max_bu LOOP
--RAISE NOTICE 'Creating partitions into schema posdata2 for BU %
...', live_part_table.id_business_unit::varchar;
EXECUTE 'CREATE TABLE posdata2.transaction_p' ||
live_part_table.id_business_unit::varchar || ' PARTITION OF
posdata2.transaction FOR VALUES in (' ||
live_part_table.id_business_unit::varchar || ') PARTITION BY range
(transaction_date);';
-- Create quarter partitions
dt_curr_timestamp := live_part_table.min_timestamp;
WHILE dt_curr_timestamp <= live_part_table.max_timestamp LOOP
-- Set running variables
SELECT EXTRACT(YEAR FROM dt_curr_timestamp) INTO int_curr_year;
SELECT EXTRACT(QUARTER FROM dt_curr_timestamp) INTO
int_curr_quarter;
--RAISE NOTICE 'Creating SubPartition for BU %, QUARTER %',
live_part_table.id_business_unit::varchar, int_curr_year::varchar || 'Q' ||
int_curr_quarter::varchar;
EXECUTE 'CREATE TABLE posdata2.transaction_p' ||
live_part_table.id_business_unit::varchar || '_' || int_curr_year::varchar
|| 'Q' || int_curr_quarter::varchar || ' PARTITION OF
posdata2.transaction_p' || live_part_table.id_business_unit::varchar || '
FOR VALUES FROM (''' || dt_curr_timestamp || ''') TO (''' ||
dt_curr_timestamp + interval '3 months' || ''')';
EXECUTE 'CREATE UNIQUE INDEX transaction_p' ||
live_part_table.id_business_unit::varchar || '_' || int_curr_year::varchar
|| 'Q' || int_curr_quarter::varchar || '_idx01 ON posdata2.transaction_p'
|| live_part_table.id_business_unit::varchar || '_' ||
int_curr_year::varchar || 'Q' || int_curr_quarter::varchar || '
(id_transaction, id_business_unit);';
-- Increment dt_curr_timestamp value
dt_curr_timestamp := dt_curr_timestamp + interval '3 months';
END LOOP;
EXECUTE 'UPDATE miniexport.tby_part_followup SET
tpf_bool_part_BU_Quarter_ok = true where id_business_unit = ' ||
live_part_table.id_business_unit::varchar || ';';
a_count = a_count + 1;
--GET DIAGNOSTICS a_count = ROW_COUNT;
END LOOP;
Moreover, Sébastien Lardière wrote a tooling kit to manage time partitions:
https://github.com/slardiere/PartMgr
I hope this helps!
Regards,
Thomas
Le sam. 27 oct. 2018 à 01:05, Yuxia Qiu a écrit :
> HI Dear PostgreSQL team,
>
>I have created a partition table as bellow:
>*CREATE TABLE* measurement_year_month (
> logdate date not null,
> peaktemp int,
> unitsales int
> ) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM
> logdate));
>
> so the content for this column *partexprs* for this table in
> pg_partitioned_table will be:
>
> ({FUNCEXPR :funcid 1384 :funcresulttype 701 :funcretset false
> :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args
> ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1
> :constbyval false :constisnull false :location 122 :constvalue 8 [ 32 0 0 0
> 121 101 97 114 ]} {VAR :varno 1 :varattno 1 :vartype 1082 :vartypmod -1
> :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 132})
> :location 114} {FUNCEXPR :funcid 1384 :funcresulttype 701 :funcretset false
> :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 100 :args
> ({CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1
> :constbyval false :constisnull false :location 150 :constvalue 9 [ 36 0 0 0
> 109 111 110 116 104 ]} {VAR :varno 1 :varattno 1 :vartype 1082 :vartypmod
> -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 162})
> :location 142})
>
> My question is: Can I get string value from this column? and how?
> In the end I want to have the bellow result:
>
>
> *Table name* *Partition
> information*
> measurement_year_month(EXTRACT(YEAR FROM logdate),
> EXTRACT(MONTH FROM logdate))
>
>
> Your help is highly appreciated.
>
> Thanks,
> Yuxia
>
>
>
