List Partition in Postgresql

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 defined 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 defining each partition by means of the values IN (value_list) syntax, where value_list is a comma-separated list of values.
In the preceding article, 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 first in the following manner:

CREATE TABLE sales_record_listpart
  (
  id NUMERIC primary key,
  sales_date date,
  sales_amount NUMERIC,
  city text
);

Let’s create the child tables, but this time on the basis of the city list.

Create the sales_record_list1 table in the following manner:

 CREATE TABLE sales_record_list1
  (
  PRIMARY KEY (id, city),
  CHECK (city IN ('new york', 'sydney'))
  )
  INHERITS (sales_record_listpart);

Now, create the sales_record_list2 table in the following manner:

CREATE TABLE sales_record_list2
  (
  PRIMARY KEY (id, city),
  CHECK (city IN ('Islamabad', 'Boston', 'London'))
  )
  INHERITS (sales_record_listpart);

Let’s create the index for the sales_record_list1 table:

CREATE INDEX list1_index ON sales_record_list1(city);
Let’s create the index for the sales_record_list2 table:
warehouse_db=# CREATE INDEX list2_index ON sales_record_list2 (city);

Now, create the trigger function in the following manner:

CREATE OR REPLACE FUNCTION sales_record_list_insert()
RETURNS TRIGGER AS $$
BEGIN
  IF (NEW.city IN ('new york', 'sydney')) THEN
    INSERT INTO sales_record_list1 VALUES (NEW.*);
  ELSEIF (NEW.city IN ('Islamabad', 'Boston', 'London')) THEN
    INSERT INTO sales_record_list2 VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'CITY not present in this lists';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

In the end, we need to create the supporting trigger in the following manner:

CREATE TRIGGER sales_day_trigger
  BEFORE INSERT ON sales_record_listpart
  FOR EACH ROW
  EXECUTE PROCEDURE sales_record_list_insert();

You can verify that the partition is linked with the master table using the following command:

Column	Type	Modifiers	Storage
id	numeric	not null	main
sales_date	date
sales_amount	numeric
city	text

Indexes:

    "sales_record_listpart_pkey" PRIMARY KEY, btree (id)

Triggers:

```Sales_day_trigger BEFORE INSERT ON sales_record_listpart FOR EACH ROW

EXECUTE PROCEDURE sales_record_list_insert()

**Child tables:** `sales_record_list1, sales_record_list2`
**Has OIDs:** `no`


The preceding output is a partial output of the \d+ command
Now, let’s do some quick inserts and verify that our list partition is also working how we expect it do so:

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES
(1,'15-APR-2008',1200,'sydney');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES (2,'15-APR-2008',1500,'Boston');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES (3,'16-APR-2008',1800,'Islamabad');

INSERT INTO sales_record_listpart
(id, sales_date, sales_amount, city)
VALUES
(4,'20-APR-2008',1300,'new york');

>> When you perform the preceding INSERT statements, you will observe that the INSERT query returns the `INSERT 0 0 message;` this is because the record is inserted in the child tables instead of the master tables.

Perform `SELECT` on `select_record_list1` to verify that the record is inserted as expected in the following manner:

SELECT * FROM sales_record_list1;

 id | sales_date | sales_amount |   city
----+------------+--------------+----------
  1 | 2008-04-15 |         1200 |  sydney
  4 | 2008-04-20 |         1300 | new york
(2 rows)


Perform `SELECT` on `select_record_list2` to verify that the record is inserted as expected in the following manner:

SELECT * FROM sales_record_list2;

 id | sales_date | sales_amount |   city
----+------------+--------------+-----------
  2 | 2008-04-15 |         1500 | Boston
  3 | 2008-04-16 |         1800 | Islamabad
(2 rows)