Различия

Показаны различия между двумя версиями страницы.

--- it:postgresql:partition [2022/12/20 10:29] – создано ura2404
+++ it:postgresql:partition [2022/12/20 15:36] – ura2404
@@ Строка 4: / Строка 4: @@
 [[https://ru.stackoverflow.com/questions/1168712/Секционирование-существующей-таблицы-postgresql]] {{it:postgresql:partition:sekcionirovanie_suschestvujuschej_tablicy_postgresql.mht|mht}} - Секционирование существующей таблицы PostgreSQL
+<code sql>
+/*
+-- новая таблица
+CREATE TABLE IF NOT EXISTS foo (
+	 ts timestamp NULL
+	,c1 int4 NULL
+	,c2 text NULL
+)PARTITION BY RANGE (ts);
+CREATE TABLE IF NOT EXISTS foo_01 PARTITION OF foo (
+	primary key (c1)
+) FOR VALUES FROM (MINVALUE) TO ('2020-01-02');
+CREATE TABLE IF NOT EXISTS foo_02 PARTITION OF foo (
+	primary key (c1)
+) FOR VALUES FROM ('2020-01-02') TO ('2020-01-03');
+DELETE FROM foo;
+INSERT INTO foo SELECT '2020-01-01', i, md5(random()::text) FROM generate_series(1, 10) AS i;
+INSERT INTO foo SELECT '2020-01-02', i, md5(random()::text) FROM generate_series(11, 20) AS i;
+INSERT INTO foo SELECT '2000-01-01', i, md5(random()::text) FROM generate_series(100, 110) AS i;
+-- -----------------------------------------------------------------------------------------
+-- существующая таблица
+CREATE TABLE IF NOT EXISTS foo2 (
+	 ts timestamp NULL
+	,c1 int4 NULL primary key
+	,c2 text NULL
+);
+DELETE FROM foo2;
+INSERT INTO foo2 SELECT '2020-01-01', i, md5(random()::text) FROM generate_series(1, 10) AS i;
+INSERT INTO foo2 SELECT '2020-01-02', i, md5(random()::text) FROM generate_series(11, 20) AS i;
+INSERT INTO foo2 SELECT '2000-01-01', i, md5(random()::text) FROM generate_series(100, 110) AS i;
+CREATE TABLE IF NOT EXISTS foo22 (
+	 ts timestamp NULL
+	,c1 int4 NULL
+	,c2 text NULL
+)PARTITION BY RANGE (ts);
+CREATE TABLE IF NOT EXISTS foo2_01 PARTITION OF foo22 (
+	primary key (c1)
+) FOR VALUES FROM (MINVALUE) TO ('2020-01-02');
+CREATE TABLE IF NOT EXISTS foo2_02 PARTITION OF foo22 (
+	primary key (c1)
+) FOR VALUES FROM ('2020-01-02') TO ('2020-01-03');
+alter table foo2 rename to _foo;
+alter table foo22 rename to foo2;
+--alter table foo2 attach partition _foo for values from (MINVALUE) to ('2020-01-02');
+--alter table foo2 attach partition _foo for values from ('2020-01-02') to ('2020-01-03');
+INSERT INTO foo2(ts,c1,c2) select ts,c1,c2 FROM _foo;
+DROP TABLE _foo;
+*/
+-- -----------------------------------------------------------------------------------------
+-- наследование
+CREATE TABLE IF NOT EXISTS foo3 (
+	 ts timestamp NULL
+	,c1 int4 NULL
+)PARTITION BY RANGE (ts);
+CREATE TABLE IF NOT EXISTS foo31 (
+	c2 text NULL
+)INHERITS (foo3);
+DELETE FROM foo31;
+INSERT INTO foo31 SELECT '2020-01-01', i, md5(random()::text) FROM generate_series(1, 10) AS i;
+INSERT INTO foo31 SELECT '2020-01-02', i, md5(random()::text) FROM generate_series(11, 20) AS i;
+INSERT INTO foo31 SELECT '2000-01-01', i, md5(random()::text) FROM generate_series(100, 110) AS i;
+</code>