数据库是PostgreSQL15。
我正在编写一个查询,该查询将返回我需要修剪的表分区列表。我的分区命名如下:table_YYYYDOY,其中 YYYY 是年份,DOY 是一年中的日期。例如,今天的分区将命名为 mytable_2023283。
在查询中,我想过滤所有超过 2 天的分区,以便我可以删除它们。我获取表列表,然后加入该列表以选择主表的分区。示例设置包含在以下链接的 db-fiddle 中:
https://www.db-fiddle.com/#&togetherjs=QLQyTaPQkO
查询如下:
select t.table_schema as schema_name,
t.table_name,
right(t.table_name, 7)::int8 as partition_postfix,
(date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as threshold,
right(t.table_name, 7)::int8 - (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as diff
from information_schema.tables t
join pg_inherits i on i.inhrelid = (t.table_schema || '.' || t.table_name)::regclass
where t.table_schema = 'public'
-- and right(t.table_name, 7)::int8 < (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8;
一切似乎都很好 - 但是当我尝试在 WHERE 子句中包含任何形式的计算时,查询失败并显示Query Error: error: invalid input syntax for type bigint: "ref_sys"。
似乎只要有 WHERE,未分区的系统表就会包含在列表中。我尝试将其放入 CTE 中,然后在“diff”字段上进行操作 - 这也不起作用。
谁能向我解释一下这是怎么回事?