postgresql - Select between with null intervals

Question

I want to do something like

SELECT * FROM mytable WHERE created_at BETWEEN A AND B

And these variables can be null, if that is null I wish I could fallback to the lower and upper bound on the data, what I mean:

Name	Created_at
First	01-01-2021
Second	02-03-2021
Second	31-12-2021

A would be 01-01-2021 and B 31-12-2021, or something that has the same meaning. How can I do that?

标签： postgresql

Answer 1

使用 的力量coalesce：

SELECT * FROM mytable
WHERE created_at BETWEEN coalesce(
                            CAST (A AS timestamp with time zone),
                            '-infinity'
                         )
                     AND coalesce(
                            CAST (B AS timestamp with time zone),
                            'infinity'
                         );