it:postgresql:partition
Различия
Показаны различия между двумя версиями страницы.
Следующая версия | Предыдущая версия | ||
it:postgresql:partition [2022/12/20 10:29] – создано ura2404 | it:postgresql:partition [2023/04/04 08:42] (текущий) – ura2404 | ||
---|---|---|---|
Строка 3: | Строка 3: | ||
> | > | ||
- | [[https:// | + | [[https:// |
+ | [[https:// | ||
+ | {{it: | ||
+ | |||
+ | |||
+ | <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 (' | ||
+ | |||
+ | CREATE TABLE IF NOT EXISTS foo_02 PARTITION OF foo ( | ||
+ | primary key (c1) | ||
+ | ) FOR VALUES FROM (' | ||
+ | |||
+ | DELETE FROM foo; | ||
+ | INSERT INTO foo SELECT ' | ||
+ | INSERT INTO foo SELECT ' | ||
+ | INSERT INTO foo SELECT ' | ||
+ | |||
+ | |||
+ | -- ----------------------------------------------------------------------------------------- | ||
+ | -- существующая таблица | ||
+ | CREATE TABLE IF NOT EXISTS foo2 ( | ||
+ | ts timestamp NULL | ||
+ | ,c1 int4 NULL primary key | ||
+ | ,c2 text NULL | ||
+ | ); | ||
+ | DELETE FROM foo2; | ||
+ | INSERT INTO foo2 SELECT ' | ||
+ | INSERT INTO foo2 SELECT ' | ||
+ | INSERT INTO foo2 SELECT ' | ||
+ | |||
+ | 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 (' | ||
+ | |||
+ | CREATE TABLE IF NOT EXISTS foo2_02 PARTITION OF foo22 ( | ||
+ | primary key (c1) | ||
+ | ) FOR VALUES FROM (' | ||
+ | |||
+ | alter table foo2 rename to _foo; | ||
+ | alter table foo22 rename to foo2; | ||
+ | --alter table foo2 attach partition _foo for values from (MINVALUE) to (' | ||
+ | --alter table foo2 attach partition _foo for values from (' | ||
+ | |||
+ | INSERT INTO foo2(ts, | ||
+ | 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 ' | ||
+ | INSERT INTO foo31 SELECT ' | ||
+ | INSERT INTO foo31 SELECT ' | ||
+ | |||
+ | </ |
it/postgresql/partition.1671532188.txt.gz · Последнее изменение: 2022/12/20 10:29 — ura2404