timescaledb php,postgres表创建

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

-- Table structure for tb_143a7f37a7f04d6fae334f198ed6a474

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

DROP TABLE IF EXISTS "public"."tb_111a7f37a7f04d6fae334f198ed6a474" cascade;

CREATE TABLE "public"."tb_111a7f37a7f04d6fae334f198ed6a474" (

"current_phase_a" float8,

"current_phase_b" float8,

"current_phase_c" float8,

"voltage_phase_a" float8,

"voltage_phase_b" float8,

"voltage_phase_c" float8,

"voltage_line_ab" float8,

"voltage_line_bc" float8,

"voltage_line_ca" float8,

"active_power_a" float8,

"active_power_b" float8,

"active_power_c" float8,

"reactive_power_a" float8,

"reactive_power_b" float8,

"reactive_power_c" float8,

"apparent_power_a" float8,

"apparent_power_b" float8,

"apparent_power_c" float8,

"active_kw_a" float8,

"active_kw_b" float8,

"active_kw_c" float8,

"reactive_kw_a" float8,

"reactive_kw_b" float8,

"reactive_kw_c" float8,

"apparent_kw_a" float8,

"apparent_kw_b" float8,

"apparent_kw_c" float8,

"power_factor_a" float8,

"power_factor_b" float8,

"power_factor_c" float8,

"signal_frequency_a" float8,

"signal_frequency_b" float8,

"signal_frequency_c" float8,

"total_active_power" float8,

"total_reactive_power" float8,

"total_apparent_power" float8,

"total_active_kw" float8,

"total_reactive_kw" float8,

"total_apparent_kw" float8,

"total_power_factor" float8,

"device_uuid" uuid,

"timestamp" timestamptz(6) NOT NULL

)

;

SELECT create_hypertable('tb_111a7f37a7f04d6fae334f198ed6a474', 'timestamp');

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

-- Records of tb_143a7f37a7f04d6fae334f198ed6a474

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

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

-- Indexes structure for table tb_143a7f37a7f04d6fae334f198ed6a474

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

CREATE INDEX "tb_143a7f37a7f04d6fae334f198ed6a474_timestamp_idx" ON "public"."tb_111a7f37a7f04d6fae334f198ed6a474" USING btree (

"timestamp" "pg_catalog"."timestamptz_ops" DESC NULLS FIRST

);

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

-- Triggers structure for table tb_143a7f37a7f04d6fae334f198ed6a474

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

CREATE TRIGGER "ts_insert_blocker" BEFORE INSERT ON "public"."tb_111a7f37a7f04d6fae334f198ed6a474"

FOR EACH ROW

EXECUTE PROCEDURE "_timescaledb_internal"."insert_blocker"();

cascade 删除表级联

SELECT create_hypertable('tb_111a7f37a7f04d6fae334f198ed6a474', 'timestamp'); 创建超表，使用 create_hypertable 函数 将postgres标准表转化为 hypertablecreate_hypertable 有两个参数 ，第一个参数 是 表名，第二个参数 是分区列，一般为 TIMESTAMPTZ类型

(hypertable 是 timescaledb 抽象的 一张表，让用户操作 hypertable 就像 操作 postgres的普通表一样，在内部，timescaledb 自动将hypertable 分割成块， timescaledb 会自动操作和管理 hypertable 的分区表。这对于用户来说是透明的)

create_hypertable可以自动创建索引和触发器