sql - Postgres 案例说明

Question

我目前正在使用我老师的一些旧 Postgres SQL 代码并对其进行修改以符合我的需要。但是，有一段代码我似乎无法在网上的任何地方找到解释。在代码中，他有一个带有 ~~* 的 case 语句，用作看起来像比较的东西。我不熟悉这种语法，非常感谢任何帮助。提前致谢。

CASE
        WHEN sessions.status ~~* 'data%finder%'::text THEN ((sessions.minutes_in_data_ || ' ('::text) || sessions.minutes_in_idle) || ')'::text
        WHEN sessions.status ~~* 'text%edit%'::text THEN ((sessions.minutes_in_text_editor || ' ('::text) || sessions.minutes_in_idle) || ')'::text
        WHEN sessions.status ~~* 'admin%module%'::text AND sessions.minutes_in_module<> 0 THEN ((sessions.minutes_in_module|| ' ('::text) || sessions.minutes_in_idle) || ')'::text
        WHEN sessions.status ~~* 'client%module%'::text AND sessions.minutes_in_module<> 0 THEN ((sessions.minutes_in_module|| ' ('::text) || sessions.minutes_in_idle) || ')'::text
        ELSE NULL::text
    END AS "Duration (Idle)",

Answer 1

它们等同于like/ilike:

来自documentation

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.