Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you how PostgreSQL implementation LIST partition, I believe most people still do not know how to share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

1. Table construction

create table tbl(a int, b varchar(10));

create table tbl_1 (check ( b = 'jason')) INHERITS (tbl);

create table tbl_2 (check ( b = 'lucy')) INHERITS (tbl);

create table tbl_other (check( b not in ('lucy','jason'))) INHERITS (tbl);

2. Create a function

CREATE OR REPLACE FUNCTION tbl_part_tg()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.b = 'jason' ) THEN

INSERT INTO tbl_1 VALUES (NEW.*);

ELSIF ( NEW.b = 'lucy') THEN

INSERT INTO tbl_2 VALUES (NEW.*);

ELSIF (NEW.b not in ('jason','lucy')) THEN

INSERT INTO tbl_other VALUES (NEW.*);

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

3. Create a trigger

CREATE TRIGGER insert_tbl_part_tg

BEFORE INSERT ON tbl

FOR EACH ROW EXECUTE PROCEDURE tbl_part_tg();

4. Insert data

insert into tbl values(1,'jason');

insert into tbl values(2,'lucy');

insert into tbl values(3,'baichi');

insert into tbl values(4,'baichi1');

insert into tbl values(5,'baichi2');

5. Results

postgres=# select * from tbl_1;

a | b

---+-------

1 | jason

(1 row)

postgres=# select * from tbl_2;

a | b

---+------

2 | lucy

(1 row)

postgres=# select * from tbl_other;

a | b

---+---------

3 | baichi

4 | baichi1

5 | baichi2

(3 rows)

postgres=# select * from tbl;

a | b

---+---------

1 | jason

2 | lucy

3 | baichi

4 | baichi1

5 | baichi2

(5 rows)

postgres=#

That's all for PostgreSQL How to Implement LIST Partitioning. Thanks for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.