表的分段

来源：互联网发布：c语言程序代码大全编辑：程序博客网时间：2024/06/06 09:04

按年分段

CREATE TABLE trade (

tdate DATE NOT NULL,

tsymbol VARCHAR(8) NOT NULL,

ttime TIME)

PARTITION BY EXTRACT (year FROM tdate);

按年和月分段

PARTITION BY EXTRACT(year FROM tdate)*100 + EXTRACT(month FROM tdate);

删除分段，2009-01-01至今的年份分段

SELECT DROP_PARTITION('trade', EXTRACT('year' FROM '2009-01-01'::date));

DROP_PARTITION

-------------------

Partition dropped

(1 row)

最大支持1024分段，推荐10到20分段

例如创建dates表

CREATE TABLE dates (year INTEGER NOT NULL,

month VARCHAR(8) NOT NULL)

PARTITION BY year * 12 + month;

删除2010年10月

SELECT DROP_PARTITION('dates', '24130');

DROP_PARTITION

-------------------

Partition dropped

(1 row)

然而这样亦可

SELECT DROP_PARTITION('dates', 2010*12+ 10);

移动分区Partitions

实现步骤

1. Prepare and move the partitions with the MOVE_PARTITIONS_TO_TABLE function.

2. Create an object-level snapshot of the intermediate table.

3. Drop the intermediate table.

MOVE_PARTITIONS_TO_TABLE语法

MOVE_PARTITIONS_TO_TABLE (    '[[db-name.]schema.]source_table',    'min_range_value',    'max_range_value',     [[db-name.]schema.]target_table')

dbadmin=> create schema partn_backup;

CREATE SCHEMA

dbadmin=> SELECT MOVE_PARTITIONS_TO_TABLE ('time','200801','200801','partn_backup.trades_200801');

-[ RECORD 1 ]------------+------------------------------------------------------------------------------------------

MOVE_PARTITIONS_TO_TABLE | Created table partn_backup.trades_200801.

0 distinct partition values moved at epoch 58.

0 0