Thread: 'Range partitioning in PGSQL'

'Range partitioning in PGSQL'

From
"Saseendra Babu"
Date:
'Range partitioning in PGSQL'
 
Hello
Is there as in oracle?
My problem is as follows.
I have telephone_ subscriber   table with tel no as Pkey
As the table has to contain millions of rows, I have to partition the table in to multiple partitions  based on range of tel no. 
( At present we did it with inheritance.  but the problem found is: we have to insert  rows in to each child table by referring the table name exclusively ,
based on range of tel nos, keeping the parent table empty.)
What we require is as follows.
  1. In the insert statement we shall specify only the table name ( not the partition name)
  2. The rows are be populated in to the corresponding partions.
  3.  In select also we should only the table name ( not the partition name). rows are to be retrieved  from  corresponding partitions.
  4. While using partition  by inheritance,  we have to specify the partition,  ie  child  table name.
Waiting for your valued suggestions
Saseendra Babu K
Attachment

Re: 'Range partitioning in PGSQL'

From
Michael Glaesemann
Date:
On Apr 11, 2011, at 4:04, Saseendra Babu wrote:

> ?'Range partitioning in PGSQL'
>
> Hello
> Is there as in oracle?
> My problem is as follows.
> I have telephone_ subscriber   table with tel no as Pkey
> As the table has to contain millions of rows, I have to partition the table in to multiple partitions  based on range
oftel no.  
> ( At present we did it with inheritance.  but the problem found is: we have to insert  rows in to each child table by
referringthe table name exclusively ,  
> based on range of tel nos, keeping the parent table empty.)
> What we require is as follows.
>  1.. In the insert statement we shall specify only the table name ( not the partition name)
>  2.. The rows are be populated in to the corresponding partions.
>  3..  In select also we should only the table name ( not the partition name). rows are to be retrieved  from
correspondingpartitions.  
>  4.. While using partition  by inheritance,  we have to specify the partition,  ie  child  table name.
> Waiting for your valued suggestions

You can write a trigger which transparently partitions inserts on the parent table into the appropriate partitions.

Michael Glaesemann
grzm seespotcode net




Re: 'Range partitioning in PGSQL'

From
Vibhor Kumar
Date:
On Apr 11, 2011, at 1:34 PM, Saseendra Babu wrote:

> What we require is as follows.
>     • In the insert statement we shall specify only the table name ( not the partition name)
>     • The rows are be populated in to the corresponding partions

Yes it is possible in PostgreSQL.
http://www.postgresql.org/docs/9.0/interactive/ddl-partitioning.html


>     •  In select also we should only the table name ( not the partition name). rows are to be retrieved  from
correspondingpartitions. 

This is possible. constraint_exclusion=partition/on

>     • While using partition  by inheritance,  we have to specify the partition,  ie  child  table name.
Not require.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com