Re: Question about partition table

[Thomas Boussekey <thomas.boussekey@xxxxxxxxx>]

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 <yuxiaqiu1@xxxxxxxxx> 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