v7-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch
application/octet-stream
Filename: v7-0004-Rename-tables-in-tests-of-partition-MERGE-SPLIT-o.patch
Type: application/octet-stream
Part: 4
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v7-0004
Subject: Rename tables in tests of partition MERGE/SPLIT operations
| File | + | − |
|---|---|---|
| src/test/regress/expected/partition_merge.out | 338 | 338 |
| src/test/regress/expected/partition_split.out | 619 | 619 |
| src/test/regress/sql/partition_merge.sql | 80 | 80 |
| src/test/regress/sql/partition_split.sql | 140 | 140 |
From e985c103b43cd6d31da294d8541407dc0c8dc131 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Fri, 19 Apr 2024 16:03:06 +0300
Subject: [PATCH v7 4/7] Rename tables in tests of partition MERGE/SPLIT
operations
Replace "salesman" with "salesperson", "salesmen" with "salespeople". The
names are both gramatically correct and gender-neutral.
Reported-by: Alexander Lakhin
Discussion: https://postgr.es/m/fdaa003e-919c-cbc9-4f0c-e4546e96bd65%40gmail.com
Reviewed-by: Robert Haas
---
src/test/regress/expected/partition_merge.out | 676 ++++-----
src/test/regress/expected/partition_split.out | 1238 ++++++++---------
src/test/regress/sql/partition_merge.sql | 160 +--
src/test/regress/sql/partition_split.sql | 280 ++--
4 files changed, 1177 insertions(+), 1177 deletions(-)
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index cf961b041fb..acacd81767c 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -10,12 +10,12 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -54,7 +54,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -115,68 +115,68 @@ SELECT * FROM pg_indexes WHERE tablename = 'sales_feb_mar_apr2022' and schemanam
(1 row)
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb_mar_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(10 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
-- Use indexscan for testing indexes
SET enable_seqscan = OFF;
SELECT * FROM sales_feb_mar_apr2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(10 rows)
RESET enable_seqscan;
@@ -184,7 +184,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -209,15 +209,15 @@ INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
-- name
ALTER TABLE sales_range MERGE PARTITIONS (sales_jan2022, sales_mar2022, sales_others) INTO sales_others;
select * from sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(7 rows)
-- show partitions with conditions:
@@ -239,7 +239,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -248,219 +248,219 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1);
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
(2 rows)
SELECT * FROM sales_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
(2 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022;
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
(6 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
DROP TABLE sales_date;
--
-- Test: merge partitions of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
-select * from salesmans;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
+select * from salespeople;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(6 rows)
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
- 20 | Smirnoff
- 31 | Popov
- 30 | Ford
+select * from salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
+ 20 | Smirnoff
+ 31 | Popov
+ 30 | Ford
(5 rows)
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -482,88 +482,88 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_apr2022_10_20;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
(1 row)
ALTER TABLE sales_apr2022 MERGE PARTITIONS (sales_apr2022_01_10, sales_apr2022_10_20, sales_apr2022_20_30) INTO sales_apr_all;
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr_all;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
DROP TABLE sales_range;
@@ -574,8 +574,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -607,33 +607,33 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
CREATE TABLE sales_west PARTITION OF sales_list FOR VALUES IN ('Lisbon', 'New York', 'Madrid');
CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok');
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
-- show partitions with conditions:
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
@@ -662,68 +662,68 @@ SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_ge
(3 rows)
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(14 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Helsinki | 1200 | 03-06-2022
- 9 | May | Helsinki | 1200 | 03-11-2022
- 10 | Halder | Oslo | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Helsinki | 1200 | 03-06-2022
+ 9 | May | Helsinki | 1200 | 03-11-2022
+ 10 | Halder | Oslo | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_all;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(10 rows)
-- Use indexscan for testing indexes after merging partitions
SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
RESET enable_seqscan;
@@ -803,7 +803,7 @@ DROP TABLE t;
--
-- Try mixing permanent and temporary partitions.
--
-SET search_path = public, pg_temp;
+SET search_path = partitions_merge_schema, pg_temp, public;
CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2);
@@ -823,16 +823,16 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
tp_1_2 | FOR VALUES FROM (1) TO (2) | p
(2 rows)
-SET search_path = pg_temp, public;
+SET search_path = pg_temp, partitions_merge_schema, public;
-- Can't merge persistent partitions into a temporary partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: cannot create a temporary relation as partition of permanent relation "t"
-RESET search_path;
+SET search_path = partitions_merge_schema, public;
-- Can't merge persistent partitions into a temporary partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO pg_temp.tp_0_2;
ERROR: cannot create a temporary relation as partition of permanent relation "t"
DROP TABLE t;
-SET search_path = pg_temp, public;
+SET search_path = pg_temp, partitions_merge_schema, public;
BEGIN;
CREATE TABLE t (i int) PARTITION BY RANGE (i);
CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1);
@@ -853,7 +853,7 @@ SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
tp_1_2 | FOR VALUES FROM (1) TO (2) | t
(2 rows)
-SET search_path = public, pg_temp;
+SET search_path = partitions_merge_schema, pg_temp, public;
-- Can't merge temporary partitions into a persistent partition
ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2;
ERROR: cannot create a permanent relation as partition of temporary relation "t"
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 660361cd20e..ed25ff08d9f 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -10,7 +10,7 @@ SET search_path = partition_split_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -84,8 +84,8 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-01'));
ERROR: upper bound of partition "sales_apr2022" is greater than upper bound of split partition
-LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
- ^
+LINE 4: PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO...
+ ^
-- ERROR: lower bound of partition "sales_mar2022" conflicts with upper bound of previous partition "sales_feb2022"
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
@@ -109,7 +109,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -132,68 +132,68 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_mar2022 FOR VALUES FROM ('2022-03-01') TO ('2022-04-01'),
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -216,61 +216,61 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
DROP TABLE sales_range CASCADE;
@@ -280,7 +280,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -288,103 +288,103 @@ CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month
CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1) TO (2022, 1, 1);
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(9 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
(4 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
ALTER TABLE sales_date SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(11 rows)
SELECT * FROM sales_dec2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
- Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2021 | 12 | 7 | 2021.12.07 | Sales department
+ Manager2 | 2021 | 12 | 8 | 2021.12.08 | Sales department
(2 rows)
SELECT * FROM sales_jan2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
- Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
- Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager3 | 2022 | 1 | 1 | 2022.01.01 | Sales department
+ Manager2 | 2022 | 1 | 2 | 2022.01.02 | Sales department
+ Manager1 | 2022 | 1 | 10 | 2022.01.10 | Sales department
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
- Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
- Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 2 | 4 | 2022.02.04 | Sales department
+ Manager3 | 2022 | 2 | 1 | 2022.02.01 | Sales department
+ Manager2 | 2022 | 2 | 10 | 2022.02.10 | Sales department
(3 rows)
SELECT * FROM sales_other;
- salesman_name | sales_year | sales_month | sales_day | sales_date | sales_department
----------------+------------+-------------+-----------+------------+------------------
- Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
- Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
- Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
+ salesperson_name | sales_year | sales_month | sales_day | sales_date | sales_department
+------------------+------------+-------------+-----------+------------+------------------
+ Manager1 | 2022 | 3 | 3 | 2022.03.03 | Sales department
+ Manager2 | 2022 | 3 | 4 | 2022.03.04 | Sales department
+ Manager3 | 2022 | 5 | 1 | 2022.05.01 | Sales department
(3 rows)
--ERROR: relation "sales_jan_feb2022" does not exist
@@ -396,7 +396,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -415,19 +415,19 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 6 | Poirot | 150 | 02-11-2022
- 7 | Li | 175 | 03-08-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 11 | Trump | 380 | 04-06-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 7 | Li | 175 | 03-08-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 11 | Trump | 380 | 04-06-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(11 rows)
SELECT * FROM pg_indexes WHERE tablename = 'sales_others' and schemaname = 'partition_split_schema';
@@ -445,34 +445,34 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_feb2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_mar2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
(3 rows)
SELECT * FROM sales_apr2022 where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 11 | Trump | 380 | 04-06-2022
- 5 | Deev | 250 | 04-07-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 11 | Trump | 380 | 04-06-2022
+ 5 | Deev | 250 | 04-07-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_others where sales_date > '2022-01-01';
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -505,7 +505,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
-- ERROR: lower bound of partition "sales_error" conflicts with upper bound of previous partition "sales_dec2022"
@@ -562,7 +562,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022 FOR VALUES FROM (20220201) TO (20220301),
PARTITION sales_others DEFAULT);
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
@@ -574,20 +574,20 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
@@ -596,50 +596,50 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01'));
-- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid;
- pg_get_constraintdef | conname | conkey
--------------------------------------------------------------+--------------------------------+--------
- CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
- FOREIGN KEY (salesman_id) REFERENCES salesmans(salesman_id) | sales_range_salesman_id_fkey | {1}
+ pg_get_constraintdef | conname | conkey
+---------------------------------------------------------------------+---------------------------------+--------
+ CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2}
+ FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1}
(2 rows)
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
DETAIL: Failing row contains (1, 0, 03-11-2022).
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(-1) is not present in table "salesmans".
+ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(-1) is not present in table "salespeople".
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
INSERT INTO sales VALUES (10, 150, '2022-03-01');
@@ -648,280 +648,280 @@ INSERT INTO sales VALUES (19, 200, '2022-03-04');
INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_40;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
- 20 | Smirnoff
- 30 | Ford
+SELECT * FROM salespeople10_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
+ 20 | Smirnoff
+ 30 | Ford
(4 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
-DETAIL: Key (salesman_id)=(40) is not present in table "salesmans".
+ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
+DETAIL: Key (salesperson_id)=(40) is not present in table "salespeople".
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
RAISE NOTICE 'trigger(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (10, 'May');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = STATEMENT
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
-NOTICE: trigger(salesmans) called: action = INSERT, when = AFTER, level = ROW
-SELECT * FROM salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
+NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW
+SELECT * FROM salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 10 | May
- 19 | Ivanov
+SELECT * FROM salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 10 | May
+ 19 | Ivanov
(2 rows)
-SELECT * FROM salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+SELECT * FROM salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-SELECT * FROM salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+SELECT * FROM salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
-INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHAR(30));
+ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5);
+INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-- Split partition has identity column:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO
+ (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3),
+ PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
+SELECT * FROM salespeople1_2;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-SELECT * FROM salesmans2_3;
- salesman_id | salesman_name
--------------+---------------
- 2 | Ivanov
+SELECT * FROM salespeople2_3;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 2 | Ivanov
(1 row)
-SELECT * FROM salesmans3_4;
- salesman_id | salesman_name
--------------+---------------
- 3 | May
+SELECT * FROM salespeople3_4;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 3 | May
(1 row)
-SELECT * FROM salesmans4_5;
- salesman_id | salesman_name
--------------+---------------
- 4 | Ford
+SELECT * FROM salespeople4_5;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 4 | Ford
(1 row)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-- New partitions have identity-columns:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
- attname | attidentity | attgenerated
----------------+-------------+--------------
- salesman_id | a |
- salesman_name | |
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
+ attname | attidentity | attgenerated
+------------------+-------------+--------------
+ salesperson_id | a |
+ salesperson_name | |
(2 rows)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
- salesman_id | salesman_name
--------------+---------------
- 1 | Poirot
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
+select * from salespeople01_10;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 1 | Poirot
(1 row)
-select * from salesmans10_20;
- salesman_id | salesman_name
--------------+---------------
- 19 | Ivanov
- 10 | May
+select * from salespeople10_20;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 19 | Ivanov
+ 10 | May
(2 rows)
-select * from salesmans20_30;
- salesman_id | salesman_name
--------------+---------------
- 20 | Smirnoff
+select * from salespeople20_30;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 20 | Smirnoff
(1 row)
-select * from salesmans30_40;
- salesman_id | salesman_name
--------------+---------------
- 30 | Ford
+select * from salespeople30_40;
+ salesperson_id | salesperson_name
+----------------+------------------
+ 30 | Ford
(1 row)
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -941,31 +941,31 @@ INSERT INTO sales_range VALUES (12, 'Plato', 350, '2022-03-19');
INSERT INTO sales_range VALUES (13, 'Gandi', 377, '2022-01-09');
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
@@ -973,50 +973,50 @@ ALTER TABLE sales_apr2022 SPLIT PARTITION sales_apr_all INTO
PARTITION sales_apr2022_10_20 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20'),
PARTITION sales_apr2022_20_30 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01'));
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 4 | Ivanov | 750 | 04-13-2022
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 3 | Ford | 2000 | 04-30-2022
(4 rows)
SELECT * FROM sales_apr2022_01_10;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_apr2022_10_20;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 4 | Ivanov | 750 | 04-13-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 4 | Ivanov | 750 | 04-13-2022
(1 row)
SELECT * FROM sales_apr2022_20_30;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
(1 row)
DROP TABLE sales_range;
@@ -1027,8 +1027,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1067,8 +1067,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -1092,109 +1092,109 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo');
CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
PARTITION sales_east FOR VALUES IN ('Bejing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));
SELECT * FROM sales_list;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(14 rows)
SELECT * FROM sales_west;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 2 | Smirnoff | New York | 500 | 03-03-2022
- 5 | Deev | Lisbon | 250 | 03-07-2022
- 11 | Muller | Madrid | 650 | 03-05-2022
- 14 | Plato | Lisbon | 950 | 03-05-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 2 | Smirnoff | New York | 500 | 03-03-2022
+ 5 | Deev | Lisbon | 250 | 03-07-2022
+ 11 | Muller | Madrid | 650 | 03-05-2022
+ 14 | Plato | Lisbon | 950 | 03-05-2022
(4 rows)
SELECT * FROM sales_east;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 1 | Trump | Bejing | 1000 | 03-01-2022
- 8 | Li | Vladivostok | 1150 | 03-09-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 1 | Trump | Bejing | 1000 | 03-01-2022
+ 8 | Li | Vladivostok | 1150 | 03-09-2022
(2 rows)
SELECT * FROM sales_nord;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+----------------+--------------+------------
- 3 | Ford | St. Petersburg | 2000 | 03-05-2022
- 7 | May | Oslo | 1200 | 03-06-2022
- 9 | May | Oslo | 1200 | 03-11-2022
- 10 | Halder | Helsinki | 800 | 03-02-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+----------------+--------------+------------
+ 3 | Ford | St. Petersburg | 2000 | 03-05-2022
+ 7 | May | Oslo | 1200 | 03-06-2022
+ 9 | May | Oslo | 1200 | 03-11-2022
+ 10 | Halder | Helsinki | 800 | 03-02-2022
(4 rows)
SELECT * FROM sales_central;
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 6 | Poirot | Berlin | 1000 | 03-01-2022
- 12 | Smith | Kyiv | 350 | 03-10-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 6 | Poirot | Berlin | 1000 | 03-01-2022
+ 12 | Smith | Kyiv | 350 | 03-10-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(4 rows)
-- Use indexscan for testing indexes after splitting partition
SET enable_indexscan = ON;
SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
- 13 | Gandi | Warsaw | 150 | 03-08-2022
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
+ 13 | Gandi | Warsaw | 150 | 03-08-2022
(2 rows)
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
- salesman_id | salesman_name | sales_state | sales_amount | sales_date
--------------+---------------+-------------+--------------+------------
- 4 | Ivanov | Warsaw | 750 | 03-04-2022
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
+ salesperson_id | salesperson_name | sales_state | sales_amount | sales_date
+----------------+------------------+-------------+--------------+------------
+ 4 | Ivanov | Warsaw | 750 | 03-04-2022
(1 row)
SET enable_indexscan = ON;
@@ -1205,7 +1205,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
INSERT INTO sales_range VALUES (2, 'Smirnoff', 500, '2022-02-09');
@@ -1228,42 +1228,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(8 rows)
DROP TABLE sales_range;
@@ -1272,7 +1272,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -1295,42 +1295,42 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_feb2022_1decade FOR VALUES FROM ('2022-02-01') TO ('2022-02-10'),
PARTITION sales_apr2022_1decade FOR VALUES FROM ('2022-04-01') TO ('2022-04-10'));
SELECT * FROM sales_jan2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 13 | Gandi | 377 | 01-09-2022
(1 row)
SELECT * FROM sales_feb2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-09-2022
- 6 | Poirot | 150 | 02-07-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-09-2022
+ 6 | Poirot | 150 | 02-07-2022
(2 rows)
SELECT * FROM sales_mar2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
(1 row)
SELECT * FROM sales_apr2022_1decade;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(2 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 14 | Smith | 510 | 05-04-2022
- 1 | May | 1000 | 01-31-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 8 | Ericsson | 185 | 02-23-2022
- 9 | Muller | 250 | 03-11-2022
- 10 | Halder | 350 | 01-28-2022
- 12 | Plato | 350 | 03-19-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 14 | Smith | 510 | 05-04-2022
+ 1 | May | 1000 | 01-31-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 9 | Muller | 250 | 03-11-2022
+ 10 | Halder | 350 | 01-28-2022
+ 12 | Plato | 350 | 03-19-2022
(8 rows)
DROP TABLE sales_range;
@@ -1338,7 +1338,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -1360,56 +1360,56 @@ ALTER TABLE sales_range SPLIT PARTITION sales_all INTO
PARTITION sales_others DEFAULT);
INSERT INTO sales_range VALUES (14, 'Smith', 510, '2022-05-04');
SELECT * FROM sales_range;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(14 rows)
SELECT * FROM sales_jan2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 1 | May | 1000 | 01-31-2022
- 10 | Halder | 350 | 01-28-2022
- 13 | Gandi | 377 | 01-09-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 1 | May | 1000 | 01-31-2022
+ 10 | Halder | 350 | 01-28-2022
+ 13 | Gandi | 377 | 01-09-2022
(3 rows)
SELECT * FROM sales_feb2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 2 | Smirnoff | 500 | 02-10-2022
- 6 | Poirot | 150 | 02-11-2022
- 8 | Ericsson | 185 | 02-23-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 2 | Smirnoff | 500 | 02-10-2022
+ 6 | Poirot | 150 | 02-11-2022
+ 8 | Ericsson | 185 | 02-23-2022
(3 rows)
SELECT * FROM sales_apr2022;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 3 | Ford | 2000 | 04-30-2022
- 4 | Ivanov | 750 | 04-13-2022
- 5 | Deev | 250 | 04-07-2022
- 11 | Trump | 380 | 04-06-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 3 | Ford | 2000 | 04-30-2022
+ 4 | Ivanov | 750 | 04-13-2022
+ 5 | Deev | 250 | 04-07-2022
+ 11 | Trump | 380 | 04-06-2022
(4 rows)
SELECT * FROM sales_others;
- salesman_id | salesman_name | sales_amount | sales_date
--------------+---------------+--------------+------------
- 7 | Li | 175 | 03-08-2022
- 9 | Muller | 250 | 03-11-2022
- 12 | Plato | 350 | 03-19-2022
- 14 | Smith | 510 | 05-04-2022
+ salesperson_id | salesperson_name | sales_amount | sales_date
+----------------+------------------+--------------+------------
+ 7 | Li | 175 | 03-08-2022
+ 9 | Muller | 250 | 03-11-2022
+ 12 | Plato | 350 | 03-19-2022
+ 14 | Smith | 510 | 05-04-2022
(4 rows)
DROP TABLE sales_range;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index 29ae6a09f13..9e21d8b163f 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -13,13 +13,13 @@ SET search_path = partitions_merge_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_dec2021 PARTITION OF sales_range FOR VALUES FROM ('2021-12-01') TO ('2021-12-31');
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_1 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-15');
CREATE TABLE sales_apr_2 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-15') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -50,7 +50,7 @@ DROP TABLE sales_range;
--
-- Add rows into partitioned table, then merge partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -108,7 +108,7 @@ DROP TABLE sales_range;
--
-- Merge some partitions into DEFAULT partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
@@ -151,7 +151,7 @@ DROP TABLE sales_range;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -162,15 +162,15 @@ CREATE TABLE sales_jan2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1)
CREATE TABLE sales_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -180,8 +180,8 @@ SELECT * FROM sales_other;
ALTER TABLE sales_date MERGE PARTITIONS (sales_jan2022, sales_feb2022) INTO sales_jan_feb2022;
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -193,14 +193,14 @@ DROP TABLE sales_date;
--
-- Test: merge partitions of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_20 PARTITION OF salesmans FOR VALUES FROM (10) TO (20);
-CREATE TABLE salesmans20_30 PARTITION OF salesmans FOR VALUES FROM (20) TO (30);
-CREATE TABLE salesmans30_40 PARTITION OF salesmans FOR VALUES FROM (30) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_20 PARTITION OF salespeople FOR VALUES FROM (10) TO (20);
+CREATE TABLE salespeople20_30 PARTITION OF salespeople FOR VALUES FROM (20) TO (30);
+CREATE TABLE salespeople30_40 PARTITION OF salespeople FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -209,80 +209,80 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_20 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_20 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (30, 'Ford');
+INSERT INTO salespeople10_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: merge partitions with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create partitions with some deleted columns:
-CREATE TABLE salesmans10_20(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-CREATE TABLE salesmans20_30(salesman_id INT PRIMARY KEY, d2 INT, salesman_name VARCHAR(30));
-CREATE TABLE salesmans30_40(salesman_id INT PRIMARY KEY, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_20(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople20_30(salesperson_id INT PRIMARY KEY, d2 INT, salesperson_name VARCHAR(30));
+CREATE TABLE salespeople30_40(salesperson_id INT PRIMARY KEY, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_20 VALUES ('dummy value 1', 19, 'Ivanov');
-INSERT INTO salesmans20_30 VALUES (20, 101, 'Smirnoff');
-INSERT INTO salesmans30_40 VALUES (31, now(), 'Popov');
+INSERT INTO salespeople10_20 VALUES ('dummy value 1', 19, 'Ivanov');
+INSERT INTO salespeople20_30 VALUES (20, 101, 'Smirnoff');
+INSERT INTO salespeople30_40 VALUES (31, now(), 'Popov');
-ALTER TABLE salesmans10_20 DROP COLUMN d1;
-ALTER TABLE salesmans20_30 DROP COLUMN d2;
-ALTER TABLE salesmans30_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_20 DROP COLUMN d1;
+ALTER TABLE salespeople20_30 DROP COLUMN d2;
+ALTER TABLE salespeople30_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20);
-ALTER TABLE salesmans ATTACH PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30);
-ALTER TABLE salesmans ATTACH PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20);
+ALTER TABLE salespeople ATTACH PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30);
+ALTER TABLE salespeople ATTACH PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans MERGE PARTITIONS (salesmans10_20, salesmans20_30, salesmans30_40) INTO salesmans10_40;
+ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40;
-select * from salesmans;
-select * from salesmans01_10;
-select * from salesmans10_40;
+select * from salespeople;
+select * from salespeople01_10;
+select * from salespeople10_40;
-DROP TABLE salesmans;
+DROP TABLE salespeople;
--
-- Test: merge sub-partitions
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr2022_01_10 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-04-10');
CREATE TABLE sales_apr2022_10_20 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-10') TO ('2022-04-20');
CREATE TABLE sales_apr2022_20_30 PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-20') TO ('2022-05-01');
@@ -329,8 +329,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -366,14 +366,14 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, MERGE PARTITIONS with data
--
CREATE TABLE sales_list
-(salesman_id INT GENERATED ALWAYS AS IDENTITY,
- salesman_name VARCHAR(30),
+(salesperson_id INT GENERATED ALWAYS AS IDENTITY,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Oslo', 'St. Petersburg', 'Helsinki');
@@ -382,20 +382,20 @@ CREATE TABLE sales_east PARTITION OF sales_list FOR VALUES IN ('Bejing', 'Delhi'
CREATE TABLE sales_central PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Helsinki', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Oslo', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
-- show partitions with conditions:
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
@@ -420,7 +420,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_all WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
RESET enable_seqscan;
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 576f9f0f63c..4228917654c 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -13,7 +13,7 @@ SET search_path = partition_split_schema, public;
--
-- Test for error codes
--
-CREATE TABLE sales_range (salesman_id int, salesman_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id int, salesperson_name varchar(30), sales_amount int, sales_date date) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -98,7 +98,7 @@ DROP TABLE sales_others;
--
-- Add rows into partitioned table then split partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -135,7 +135,7 @@ DROP TABLE sales_range CASCADE;
--
-- Add split partition, then add rows into partitioned table
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -175,7 +175,7 @@ DROP TABLE sales_range CASCADE;
-- * GENERATED column;
-- * column with DEFAULT value.
--
-CREATE TABLE sales_date (salesman_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
+CREATE TABLE sales_date (salesperson_name VARCHAR(30), sales_year INT, sales_month INT, sales_day INT,
sales_date VARCHAR(10) GENERATED ALWAYS AS
(LPAD(sales_year::text, 4, '0') || '.' || LPAD(sales_month::text, 2, '0') || '.' || LPAD(sales_day::text, 2, '0')) STORED,
sales_department VARCHAR(30) DEFAULT 'Sales department')
@@ -185,15 +185,15 @@ CREATE TABLE sales_dec2022 PARTITION OF sales_date FOR VALUES FROM (2021, 12, 1)
CREATE TABLE sales_jan_feb2022 PARTITION OF sales_date FOR VALUES FROM (2022, 1, 1) TO (2022, 3, 1);
CREATE TABLE sales_other PARTITION OF sales_date FOR VALUES FROM (2022, 3, 1) TO (MAXVALUE, MAXVALUE, MAXVALUE);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2021, 12, 7);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2021, 12, 8);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 1, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 2, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 1, 2);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 2, 1);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 3, 3);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 3, 4);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager3', 2022, 5, 1);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -204,8 +204,8 @@ ALTER TABLE sales_date SPLIT PARTITION sales_jan_feb2022 INTO
(PARTITION sales_jan2022 FOR VALUES FROM (2022, 1, 1) TO (2022, 2, 1),
PARTITION sales_feb2022 FOR VALUES FROM (2022, 2, 1) TO (2022, 3, 1));
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
-INSERT INTO sales_date(salesman_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager1', 2022, 1, 10);
+INSERT INTO sales_date(salesperson_name, sales_year, sales_month, sales_day) VALUES ('Manager2', 2022, 2, 10);
SELECT * FROM sales_date;
SELECT * FROM sales_dec2022;
@@ -221,7 +221,7 @@ DROP TABLE sales_date CASCADE;
--
-- Test: split DEFAULT partition; use an index on partition key; check index after split
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
CREATE INDEX sales_range_sales_date_idx ON sales_range USING btree (sales_date);
@@ -272,7 +272,7 @@ DROP TABLE sales_range CASCADE;
--
-- Test: some cases for splitting DEFAULT partition (different bounds)
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- sales_error intersects with sales_dec2022 (lower bound)
@@ -322,7 +322,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_others INTO
DROP TABLE sales_range;
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date INT) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
-- no error: bounds of sales_noerror are equal to lower and upper bounds of sales_dec2022 and sales_feb2022
@@ -337,11 +337,11 @@ DROP TABLE sales_range;
--
-- Test: split partition with CHECK and FOREIGN KEY CONSTRAINTs on partitioned table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30));
-INSERT INTO salesmans VALUES (1, 'Poirot');
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30));
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE TABLE sales_range (
-salesman_id INT REFERENCES salesmans(salesman_id),
+salesperson_id INT REFERENCES salespeople(salesperson_id),
sales_amount INT CHECK (sales_amount > 1),
sales_date DATE) PARTITION BY RANGE (sales_date);
@@ -363,28 +363,28 @@ SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conre
-- ERROR: new row for relation "sales_mar2022" violates check constraint "sales_range_sales_amount_check"
INSERT INTO sales_range VALUES (1, 0, '2022-03-11');
--- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesman_id_fkey"
+-- ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey"
INSERT INTO sales_range VALUES (-1, 10, '2022-03-11');
-- ok
INSERT INTO sales_range VALUES (1, 10, '2022-03-11');
DROP TABLE sales_range CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
-CREATE TABLE sales (salesman_id INT REFERENCES salesmans(salesman_id), sales_amount INT, sales_date DATE);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
+CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (19, 'Ivanov');
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (19, 'Ivanov');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (30, 'Ford');
INSERT INTO sales VALUES (1, 100, '2022-03-01');
INSERT INTO sales VALUES (1, 110, '2022-03-02');
@@ -395,36 +395,36 @@ INSERT INTO sales VALUES (20, 50, '2022-03-12');
INSERT INTO sales VALUES (20, 170, '2022-03-02');
INSERT INTO sales VALUES (30, 30, '2022-03-04');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_40;
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
--- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesman_id_fkey"
+-- ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey"
INSERT INTO sales VALUES (40, 50, '2022-03-04');
-- ok
INSERT INTO sales VALUES (30, 50, '2022-03-04');
DROP TABLE sales CASCADE;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition of partitioned table with triggers
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
-CREATE TABLE salesmans10_40 PARTITION OF salesmans FOR VALUES FROM (10) TO (40);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (1, 'Poirot');
CREATE OR REPLACE FUNCTION after_insert_row_trigger() RETURNS trigger LANGUAGE 'plpgsql' AS $BODY$
BEGIN
@@ -433,125 +433,125 @@ BEGIN
END;
$BODY$;
-CREATE TRIGGER salesmans_after_insert_statement_trigger
+CREATE TRIGGER salespeople_after_insert_statement_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH STATEMENT
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-CREATE TRIGGER salesmans_after_insert_row_trigger
+CREATE TRIGGER salespeople_after_insert_row_trigger
AFTER INSERT
- ON salesmans
+ ON salespeople
FOR EACH ROW
- EXECUTE PROCEDURE after_insert_row_trigger('salesmans');
+ EXECUTE PROCEDURE after_insert_row_trigger('salespeople');
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (10, 'May');
+INSERT INTO salespeople VALUES (10, 'May');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans10_40 VALUES (19, 'Ivanov');
+INSERT INTO salespeople10_40 VALUES (19, 'Ivanov');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-- 2 triggers should fire here (row + statement):
-INSERT INTO salesmans VALUES (20, 'Smirnoff');
+INSERT INTO salespeople VALUES (20, 'Smirnoff');
-- 1 trigger should fire here (row):
-INSERT INTO salesmans30_40 VALUES (30, 'Ford');
+INSERT INTO salespeople30_40 VALUES (30, 'Ford');
-SELECT * FROM salesmans01_10;
-SELECT * FROM salesmans10_20;
-SELECT * FROM salesmans20_30;
-SELECT * FROM salesmans30_40;
+SELECT * FROM salespeople01_10;
+SELECT * FROM salespeople10_20;
+SELECT * FROM salespeople20_30;
+SELECT * FROM salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
DROP FUNCTION after_insert_row_trigger();
--
-- Test: split partition witch identity column
-- If split partition column is identity column, columns of new partitions are identity columns too.
--
-CREATE TABLE salesmans(salesman_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans1_2 PARTITION OF salesmans FOR VALUES FROM (1) TO (2);
+CREATE TABLE salespeople1_2 PARTITION OF salespeople FOR VALUES FROM (1) TO (2);
-- Create new partition with identity column:
-CREATE TABLE salesmans2_5(salesman_id INT NOT NULL, salesman_name VARCHAR(30));
-ALTER TABLE salesmans ATTACH PARTITION salesmans2_5 FOR VALUES FROM (2) TO (5);
+CREATE TABLE salespeople2_5(salesperson_id INT NOT NULL, salesperson_name VARCHAR(30));
+ALTER TABLE salespeople ATTACH PARTITION salespeople2_5 FOR VALUES FROM (2) TO (5);
-INSERT INTO salesmans (salesman_name) VALUES ('Poirot');
-INSERT INTO salesmans (salesman_name) VALUES ('Ivanov');
+INSERT INTO salespeople (salesperson_name) VALUES ('Poirot');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ivanov');
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- Split partition has identity column:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_5'::regclass::oid;
-ALTER TABLE salesmans SPLIT PARTITION salesmans2_5 INTO
- (PARTITION salesmans2_3 FOR VALUES FROM (2) TO (3),
- PARTITION salesmans3_4 FOR VALUES FROM (3) TO (4),
- PARTITION salesmans4_5 FOR VALUES FROM (4) TO (5));
+ALTER TABLE salespeople SPLIT PARTITION salespeople2_5 INTO
+ (PARTITION salespeople2_3 FOR VALUES FROM (2) TO (3),
+ PARTITION salespeople3_4 FOR VALUES FROM (3) TO (4),
+ PARTITION salespeople4_5 FOR VALUES FROM (4) TO (5));
-INSERT INTO salesmans (salesman_name) VALUES ('May');
-INSERT INTO salesmans (salesman_name) VALUES ('Ford');
+INSERT INTO salespeople (salesperson_name) VALUES ('May');
+INSERT INTO salespeople (salesperson_name) VALUES ('Ford');
-SELECT * FROM salesmans1_2;
-SELECT * FROM salesmans2_3;
-SELECT * FROM salesmans3_4;
-SELECT * FROM salesmans4_5;
+SELECT * FROM salespeople1_2;
+SELECT * FROM salespeople2_3;
+SELECT * FROM salespeople3_4;
+SELECT * FROM salespeople4_5;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans1_2'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople1_2'::regclass::oid;
-- New partitions have identity-columns:
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans2_3'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans3_4'::regclass::oid;
-SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salesmans4_5'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople2_3'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople3_4'::regclass::oid;
+SELECT attname, attidentity, attgenerated FROM pg_attribute WHERE attnum > 0 AND attrelid = 'salespeople4_5'::regclass::oid;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split partition with deleted columns
--
-CREATE TABLE salesmans(salesman_id INT PRIMARY KEY, salesman_name VARCHAR(30)) PARTITION BY RANGE (salesman_id);
+CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id);
-CREATE TABLE salesmans01_10 PARTITION OF salesmans FOR VALUES FROM (1) TO (10);
+CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10);
-- Create new partition with some deleted columns:
-CREATE TABLE salesmans10_40(d1 VARCHAR(30), salesman_id INT PRIMARY KEY, d2 INT, d3 DATE, salesman_name VARCHAR(30));
+CREATE TABLE salespeople10_40(d1 VARCHAR(30), salesperson_id INT PRIMARY KEY, d2 INT, d3 DATE, salesperson_name VARCHAR(30));
-INSERT INTO salesmans10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
-INSERT INTO salesmans10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
+INSERT INTO salespeople10_40 VALUES ('dummy value 1', 19, 100, now(), 'Ivanov');
+INSERT INTO salespeople10_40 VALUES ('dummy value 2', 20, 101, now(), 'Smirnoff');
-ALTER TABLE salesmans10_40 DROP COLUMN d1;
-ALTER TABLE salesmans10_40 DROP COLUMN d2;
-ALTER TABLE salesmans10_40 DROP COLUMN d3;
+ALTER TABLE salespeople10_40 DROP COLUMN d1;
+ALTER TABLE salespeople10_40 DROP COLUMN d2;
+ALTER TABLE salespeople10_40 DROP COLUMN d3;
-ALTER TABLE salesmans ATTACH PARTITION salesmans10_40 FOR VALUES FROM (10) TO (40);
+ALTER TABLE salespeople ATTACH PARTITION salespeople10_40 FOR VALUES FROM (10) TO (40);
-INSERT INTO salesmans VALUES (1, 'Poirot');
-INSERT INTO salesmans VALUES (10, 'May');
-INSERT INTO salesmans VALUES (30, 'Ford');
+INSERT INTO salespeople VALUES (1, 'Poirot');
+INSERT INTO salespeople VALUES (10, 'May');
+INSERT INTO salespeople VALUES (30, 'Ford');
-ALTER TABLE salesmans SPLIT PARTITION salesmans10_40 INTO
- (PARTITION salesmans10_20 FOR VALUES FROM (10) TO (20),
- PARTITION salesmans20_30 FOR VALUES FROM (20) TO (30),
- PARTITION salesmans30_40 FOR VALUES FROM (30) TO (40));
+ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO
+ (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20),
+ PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30),
+ PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40));
-select * from salesmans01_10;
-select * from salesmans10_20;
-select * from salesmans20_30;
-select * from salesmans30_40;
+select * from salespeople01_10;
+select * from salespeople10_20;
+select * from salespeople20_30;
+select * from salespeople30_40;
-DROP TABLE salesmans CASCADE;
+DROP TABLE salespeople CASCADE;
--
-- Test: split sub-partition
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_feb2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_mar2022 PARTITION OF sales_range FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-CREATE TABLE sales_apr2022 (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_apr2022 (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_apr_all PARTITION OF sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
ALTER TABLE sales_range ATTACH PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
@@ -598,8 +598,8 @@ DROP TABLE sales_range;
-- Test: specific errors for BY LIST partitioning
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -635,8 +635,8 @@ DROP TABLE sales_list;
-- * new partitions do not have a value that split partition has.
--
CREATE TABLE sales_list
-(salesman_id INT,
- salesman_name VARCHAR(30),
+(salesperson_id INT,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
@@ -663,34 +663,34 @@ DROP TABLE sales_list;
-- Test: BY LIST partitioning, SPLIT PARTITION with data
--
CREATE TABLE sales_list
-(salesman_id SERIAL,
- salesman_name VARCHAR(30),
+(salesperson_id SERIAL,
+ salesperson_name VARCHAR(30),
sales_state VARCHAR(20),
sales_amount INT,
sales_date DATE)
PARTITION BY LIST (sales_state);
-CREATE INDEX sales_list_salesman_name_idx ON sales_list USING btree (salesman_name);
+CREATE INDEX sales_list_salesperson_name_idx ON sales_list USING btree (salesperson_name);
CREATE INDEX sales_list_sales_state_idx ON sales_list USING btree (sales_state);
CREATE TABLE sales_nord PARTITION OF sales_list FOR VALUES IN ('Helsinki', 'St. Petersburg', 'Oslo');
CREATE TABLE sales_all PARTITION OF sales_list FOR VALUES IN ('Warsaw', 'Lisbon', 'New York', 'Madrid', 'Bejing', 'Berlin', 'Delhi', 'Kyiv', 'Vladivostok');
CREATE TABLE sales_others PARTITION OF sales_list DEFAULT;
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
-INSERT INTO sales_list (salesman_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Trump', 'Bejing', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smirnoff', 'New York', 500, '2022-03-03');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ford', 'St. Petersburg', 2000, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Ivanov', 'Warsaw', 750, '2022-03-04');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Deev', 'Lisbon', 250, '2022-03-07');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Poirot', 'Berlin', 1000, '2022-03-01');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-06');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Li', 'Vladivostok', 1150, '2022-03-09');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('May', 'Oslo', 1200, '2022-03-11');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Halder', 'Helsinki', 800, '2022-03-02');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Muller', 'Madrid', 650, '2022-03-05');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Smith', 'Kyiv', 350, '2022-03-10');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Gandi', 'Warsaw', 150, '2022-03-08');
+INSERT INTO sales_list (salesperson_name, sales_state, sales_amount, sales_date) VALUES ('Plato', 'Lisbon', 950, '2022-03-05');
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
@@ -709,7 +709,7 @@ SET enable_seqscan = OFF;
SELECT * FROM sales_central WHERE sales_state = 'Warsaw';
SELECT * FROM sales_list WHERE sales_state = 'Warsaw';
-SELECT * FROM sales_list WHERE salesman_name = 'Ivanov';
+SELECT * FROM sales_list WHERE salesperson_name = 'Ivanov';
SET enable_indexscan = ON;
SET enable_seqscan = ON;
@@ -721,7 +721,7 @@ DROP TABLE sales_list;
-- * split DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
INSERT INTO sales_range VALUES (1, 'May', 1000, '2022-01-31');
@@ -759,7 +759,7 @@ DROP TABLE sales_range;
-- * split non-DEFAULT partition to partitions with spaces between bounds;
-- * random order of partitions in SPLIT PARTITION command.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-05-01');
CREATE TABLE sales_others PARTITION OF sales_range DEFAULT;
@@ -796,7 +796,7 @@ DROP TABLE sales_range;
-- Test for split non-DEFAULT partition to DEFAULT partition + partitions
-- with spaces between bounds.
--
-CREATE TABLE sales_range (salesman_id INT, salesman_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
+CREATE TABLE sales_range (salesperson_id INT, salesperson_name VARCHAR(30), sales_amount INT, sales_date DATE) PARTITION BY RANGE (sales_date);
CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_all PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01');
--
2.39.3 (Apple Git-145)