PostgreSQL创建超表总结

最新版本更新
https://code.jiangjiesheng.cn/article/343

一般操作过程

基本要求：选择的建超表的字段要不为空
执行：
SELECT CREATE_HYPERTABLE(‘bos_data_flag_info’, ‘create_time’, chunk_time_interval=> interval ‘3 months’, associated_table_prefix=>‘_bos_data_flag_info’);

报错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 );

报错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
最终完整版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);

测试验证过程
在没有任何主键、没有任何普通索引时执行，可以执行成功，且会自动执行一条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语句中）的概念。

最新版本更新
https://code.jiangjiesheng.cn/article/343