如何使用部分字符字段对PostgreSQL表进行分区

Question

PostgreSQL 11.4

表foo包含字段my_datetime字符可变(254(

我想使用这个字段对foo进行分区，但只使用字符串的年、月、日部分值20200328175432

Substr(my_datetime,0,9)给了我20200328<-这是我想用作"分区密钥"的值

由于这是一个charcated字段，我认为我需要做一些类似to_date(substr(my_datetime),'YYYYMMDD')的事情来确定日期所以我的分区定义如下：

partition by range to_date(substr(datetime,0,9), 'YYYYMMDD');

但这行不通。

那么，我是否必须回到老派，创建一个插入时触发器来移动我的数据(foo-yuck-大约每5分钟100000行(

我不能更改表字段定义，我可能可以添加字段，但我必须添加一个触发器来填充它们，对吧？

创建分区表并不是我所要求的——假设我已经创建了未来5年的每一天，如20200328_foo

有人有什么建议吗？还有什么我可以试试的吗？

Answer 1

您需要创建一个函数来克服软件供应商的可疑(更不用说愚蠢(设计决策：

create function fix_broken_type(p_input text)
returns date
as
$$
select to_date(p_input, 'yyyymmddhh24miss');
$$
language sql
immutable; --<<< this is important

然后您可以使用该函数进行分区定义：

CREATE TABLE data
(
want_to_be_timestamp varchar
)
partition by range (fix_broken_type(want_to_be_timestamp));

然后添加分区：

create table data_2020_01_01 partition of data
for values from ('2020-01-01') TO ('2020-01-02');

当然，如果插入了一个不能使用to_date()进行转换的值，那么这将非常失败。

在线示例