更新范围类型的下限/上限 [英] Update lower/upper bound of range type

问题描述

我有 tstzrange 类型的列(带有时区范围的时间戳)，我只需要更新此值的上限或下限(并保持包含/排除边界)

I have column of tstzrange type (timestamp with time zone range) and I need to update only upper or lower bound of this value (and keep inclusive/exclusive boundaries)

我设法改变了

(-infinity,infinity)

与

UPDATE table
SET
    my_column = tstzrange(
        lower(my_column),
        now(),
        '()'
    )

我有

(-infinity, <current timestamp>)

但我不知道如何保持默认范围的边界..这甚至会将 [ ] 更改为 ( )

but I don't know how to keep boundaries from default range.. this would change even [ ] to ( )

推荐答案

I 找到函数 I错过了，可以这样做

I found function I missed, it's possible to do that like this

UPDATE table
SET
    my_column = tstzrange(
        lower(my_column),
        now(),
        concat(
            CASE WHEN lower_inc(my_column) THEN '[' ELSE '(' END,
            CASE WHEN upper_inc(my_column) THEN ']' ELSE ')' END
        )
    )

最好为此创建函数.或者还有其他(更简单/更好)的解决方案吗?

It would be better to create function for this probably. Or is there any other (simpler/better) solution?

这篇关于更新范围类型的下限/上限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！