PostgreSQL 과 EDB 모두 아래 파티션을 생성했습니다.
CREATE TABLE emp_root(id int, dept varchar, location int) PARTITION BY LIST(dept);
CREATE TABLE emp_subroot PARTITION OF emp_root FOR VALUES IN ('dept1', 'dept2') PARTITION BY RANGE(location);
CREATE TABLE subroot_part_1 PARTITION OF emp_subroot FOR VALUES FROM (1) to (10);
CREATE TABLE subroot_part_2 PARTITION OF emp_subroot FOR VALUES FROM (11) to (20);
CREATE TABLE emp_subroot2 PARTITION OF emp_root FOR VALUES IN ('dept3', 'dept4');
INSERT INTO emp_root values (1, 'dept1', 5), (2, 'dept2', 16), (3, 'dept3', 7), (4, 'dept4', 17);
SELECT tableoid::regclass, * from emp_root ORDER BY id;
tableoid | id | dept | location
----------------+----+-------+----------
subroot_part_1 | 1 | dept1 | 5
subroot_part_2 | 2 | dept2 | 16
emp_subroot2 | 3 | dept3 | 7
emp_subroot2 | 4 | dept4 | 17
(4 rows)
각각 파티션소속이 바뀌도록 파티션키를 업데이트했습니다
EBB 는 성공이지만 PostgreSQL 에서는 에러납니다. 주의하세요!
===========================================================================================================================
[PostgreSQL]
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
UPDATE emp_root SET dept = 'dept1' WHERE id = 4;
ERROR: new row for relation "emp_subroot2" violates partition constraint
DETAIL: Failing row contains (4, dept1, 17).
===========================================================================================================================
SQL> select version();
+-------------------------------------------------------------------------------------------------------------+
| version |
+-------------------------------------------------------------------------------------------------------------+
| EnterpriseDB 10.4.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit |
+-------------------------------------------------------------------------------------------------------------+
(1 row)
UPDATE emp_root SET dept = 'dept1' WHERE id = 4;
UPDATE 1
