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@xxxxxxxxxx> 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@xxxxxxxxxx > 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 > > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
