Database Reference
In-Depth Information
Now, let's perform a simple
DELETE
operation and verify the impact on
partitioned tables:
warehouse_db=# DELETE FROM sales_record where sales_date
= '2014-9-13';
Let's verify that the record is deleted from the child table using the following
statement:
warehouse_db=# SELECT * FROM sales_record_m9_to_m10;
id | sales_amount | sales_date
----+--------------+------------
(0 rows)
Since the
SELECT
statement did not return any record, this conirms that we have
successfully deleted the record with
sales_date = '2014-9-13'
.
List partition
List partition is very much similar to range partition. The table is partitioned by
explicitly listing which key values appear in each partition. In list partition, each
partition is deined and designated based on a column value in one set of value lists,
instead of one set of adjoining ranges of values. This will be done by deining each
partition by means of the
values IN (value_list)
syntax, where
value_list
is a
comma-separated list of values.
In the preceding sections, we have successfully created the range partition. Now
for the purpose of list partition, we have to do the same task again. We will create a
master table that will have a sales record along with the
city
information. The list
partition will use the
city
column as a base to create the child partitions.
Let's create a master table irst in the following manner:
warehouse_db=# CREATE TABLE sales_record_listpart
(
id NUMERIC primary key,
sales_date date,
sales_amount NUMERIC,
city text
);
Now, as we did in the preceding sections, let's create the child tables, but this time on
the basis of the city list.