Hi ,
We are working on a patch<http://archives.postgresql.org/pgsql-hackers/2009-03/msg00897.php>to automate partitioning in PostgreSQL. For Range partitions, we have proposed the syntax which is as follows – *CREATE TABLE emp (* * emp_id int not null primary key,* * designation text not null,* * location varchar(50) not null,* * jdate date not null,* * ctc float not null* * * *)* *PARTITION BY RANGE (emp_id)* *(* *emp_500 (START 1 END 500),* *emp_1500 (START 500 END 1500),* *emp_4000 (START 1520 END 4000)* *);* As observed in this syntax, user needs to specify explicitly, the min and max values of a range for a given partition. With this design, partition ranges are inherently allowed to be fragmented and non-contiguous. As ‘gaps’ are allowed in the ranges, we’re also supporting an ‘overflow’ partition, so that any row, which does not satisfy constraints of any existing partitions, does not stall a big UPDATE operation and such rows are preserved.(in overflow table) However, Oracle uses user-friendly syntax but makes it compulsion that partition ranges *have* to be contiguous. *PARTITION BY RANGE (emp_id)* *(* *Partition emp_500 values less than (500),* *Partition emp_1500 values less than (1500),* *Partition emp_4000 values less than (4000),* *Partition emp_max values less than (maxvalue)* *);* As it does not allow fragmented ranges, it automatically removes the need for an ‘overflow’ partition. The syntax proposed by us is more flexible and would handle both the cases of ranges with gaps or ranges without gaps. I want to seek general opinion from the community on preferences between user-friendly ‘Oracle’ syntax, and a more generic syntax that allows ‘gaps’ in partition ranges? Regards, -- Kedar
