116 | Chapter 4: Database
current_listingsandnon_current_listings, which inherit from the main table. This
way, we can saySELECT * FROM listingsand Postgres will include the data from the
two inherited tables automatically.
CREATE TABLE current_listings (CHECK (status = 'C'))
INHERITS (listings);
CREATE TABLE non_current_listings (CHECK (status != 'C'))
INHERITS (listings);Next, we create rules that rewrite inserts on the parent table to inserts on the proper
child:
CREATE RULE listings_insert_current AS
ON INSERT TO listings WHERE (status = 'C')
DO INSTEAD INSERT INTO current_listings VALUES(NEW.*);
CREATE RULE listings_insert_non_current AS
ON INSERT TO listings WHERE (status != 'C')
DO INSTEAD INSERT INTO non_current_listings VALUES(NEW.*);Now that the rules are set up, we move the existing data in listings to the proper
subtable:
INSERT INTO current_listings SELECT * FROM listings WHERE STATUS = 'C';
INSERT INTO non_current_listings SELECT * FROM listings WHERE STATUS != 'C';
DELETE FROM listings;We know that theDELETEstatement is safe because no new data has been inserted into
thelistingstable, thanks to the rewrite rules. This is why it is important that the par-
tition conditions are a proper partitioning such asstatus = 'C'andstatus != 'C'
(non-overlapping and completely covering all possibilities). This ensures that every
row is inserted into one of the child tables, not the parent. Note that this would not
be a proper partitioning if thestatuscolumn allowedNULLvalues, as both condi-
tions would befalse.
Now we can insert and select data againstlistingsas if it were one table, while
PostgreSQL transparently handles the partitioning and works with the proper parti-
tion. This is a very simple example. In particular, we need to implement rules for
UPDATEandDELETEqueries before using this scheme. This method can easily be
extended to many partitions, even on complicated conditions.
Connecting to Multiple Databases
Occasionally, you will have the need to connect to several different databases from
one application. This is useful for migrating from an old schema to a new one. It is also
helpful if you have differing data requirements within one application; perhaps some
data is more critical and is stored on a high-availability database cluster. In any case, it
is easy in Rails. First, specify multiple database environments in thedatabase.ymlcon-
figuration file: