In addition, the datasets of ???current listings??? and ???all listings??? will have differing
data needs; the former is likely to be used transactionally while the latter is probably
used analytically. It makes sense to store these separately, as they may have different
characteristics.
First, we assume that we already have listing data in a table called listings, and it
has a status column representing the status of the listing. We create the two tables,
116 | Chapter 4: Database
current_listings and non_current_listings, which inherit from the main table. This
way, we can say SELECT * FROM listings and 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 the DELETE statement is safe because no new data has been inserted into
the listings table, thanks to the rewrite rules.
Pages:
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184