Re: Postgresql partitioning

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®
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