Re: Postgresql partitioning

Thanks for the quick response. And I assume that primary key
uniqueness is not tested across tables. Right?

Thanks,
Ram

On Fri, Mar 21, 2008 at 8:59 PM, Erik Jones <erik(at)myemma(dot)com> wrote:
> On Mar 21, 2008, at 7:15 PM, Ram Ravichandran wrote:
>
> > Hey,
> >
> > Suppose I have a table with the following fields:
> >
> > CREATE TABLE distributors (
> > id DECIMAL(3) PRIMARY KEY,
> > name VARCHAR(40),
> > status INTEGER
> > );
> >
> > I would ike to partition this table based on status which can be
> > [0,1,2,3,4].
> >
> > I was wondering if the records can change their status. i.e. If I did
> > UPDATE distributors SET status = 4 WHERE id = 231122;
> >
> > would POSTGRESQL automatically change the record from the current
> > partition (say partition where status = 3) to the partition where
> > status = 4?
>
> No. Assuming you have CHECK constraints on you partition tables and
> constraint_exclusion=on postgres will emit an error on an update like
> that.
>
>
> > Or would I have to explicitly delete it from one partition table, and
> > reinsert it in the other?
>
> Yes. Also, note that this can't be done in an ON UPDATE trigger as
> CHECK constraints are checked before any triggers are run.
>
> Erik Jones
>
> DBA | Emma(R)
> erik(at)myemma(dot)com
> 800.595.4401 or 615.292.5888
> 615.292.0777 (fax)
>
> Emma helps organizations everywhere communicate & market in style.
> Visit us online at http://www.myemma.com
>
>
>
>