原创

PostgreSQL创建超表总结

1. 一般操作过程

基本要求:选择的建超表的字段要不为空

执行:

 SELECT CREATE_HYPERTABLE('bos_data_flag_info', 'create_time', chunk_time_interval=> interval '3 months', associated_table_prefix=>'_bos_data_flag_info');

1.1. 报错1

ERROR: table "bos_data_flag_info_test_5" is not empty HINT: You can migrate data by specifying 'migrate_data => true' when calling this function.

处理:增加migrate_data => true 参数

执行:

 SELECT CREATE_HYPERTABLE('bos_data_flag_info', 'create_time', chunk_time_interval=> interval '3 months', associated_table_prefix=>'_bos_data_flag_info',migrate_data => true );

1.2. 报错2

cannot create a unique index without the column "create_time" (used in partitioning)

处理:把create_time设置成主键2,记得保存(相当于增加一个主键)

创建成功的trunk分表在_timescaledb_internal模式

参考 https://blog.csdn.net/yang_z_1/article/details/114288878

1.3. 最终完整版SQL

ALTER TABLE "bos_data_flag_info" DROP CONSTRAINT "bos_data_flag_info_pkey",
		ADD CONSTRAINT "bos_data_flag_info_pkey" PRIMARY KEY ("id", "create_time");

SELECT CREATE_HYPERTABLE('bos_data_flag_info', 'create_time', chunk_time_interval=> interval '3 months', associated_table_prefix=>'_bos_data_flag_info',migrate_data => true);

2. 测试验证过程

在没有任何主键、没有任何普通索引时执行,可以执行成功,且会自动执行一条create_time的普通索引。

如果继续添加id主键(不包括create_time主键),会报cannot create a unique index without the column "create_time" (used in partitioning)。

如果继续添加create_time主键(不包括id主键),会成功执行。

如果继续添加id主键(已有id主键),会成功执行。

汇总能建成功的几种情况:

1、在没有任何主键、没有任何普通索引时,可创建成功,【且会自动添加一条create_time的普通索引】 

2、在没有任何主键、有create_time单个字段的普通索引时,可创建成功  

3、在没有任何主键、有create_time等其他字段的普通组合索引时,可创建成功 

4、没有id主键、有create_time主键时,可创建成功 

5、有id主键、且有create_time主键时,可创建成功 

6、有id主键,有create_time单个字段的普通索引时,【创建不成功】

7、有id主键,有create_time等其他字段的普通组合索引时,【创建不成功】

综上,创建超表成功总结:

1、没有主键时,可以先 创建带create_time的普通索引(单个字段或组合索引都行)或创建create_time主键 来创建超表

2、已有id主键时,只有 先创建create_time主键 才能创建超表

补充说明:

1、没有id主键,成功创建超表后,需要添加id主键时,必须同时加上create_time作为主键,才能添加主键。

2、migrate_data => true 只针对数据库有无数据场景,跟主键和索引无关,true表示将表中的所有现有数据都将被迁移到新的超表结构中,同时保留原始数据。

3、pgsql中有主键(体现在CREATE TABLE语句中)、索引(有唯一键设置选项,体现在CREATE [UNIQUE] INDEX语句中)、唯一键(体现在CREATE TABLE语句中)的概念。

正文到此结束
本文目录