如何在 postgres 中识别具有空/未定义枚举值的行? [英] How do I identify rows with empty / undefined enum values in postgres?

问题描述

假设我有一个 Postgres 数据库，其中包含一个名为test"的简单表:

Suppose I have a Postgres database with a simple table called "test':

id
--
1
2
3

和枚举定义:

CREATE TYPE MyEnum AS ENUM ('UNKNOWN', 'TEST')

现在，我可以向表中添加一个枚举值列:

Now, I can add an enum-valued column to my table:

ALTER TABLE test ADD COLUMN my_enum MyEnum;
UPDATE test SET my_enum = 'UNKNOWN'::MyEnum WHERE id = 1;
UPDATE test SET my_enum = 'TEST'::MyEnum WHERE id = 2;

结果是:

> SELECT * FROM test;
id  | my_enum
----|--------
1   | UNKNOWN
2   | TEST
3   |

my_enum 在第 3 行有什么值?有没有办法表达?我试过 SELECT COUNT(*) FROM test WHERE my_enum != ''::MyEnum 和 SELECT COUNT(*) FROM test WHERE my_enum = null::MyEnum 和SELECT COUNT(*) FROM test WHERE my_enum != ALL(enum_range(null::MyEnum)).在第一种情况下，我收到一个错误(enum myenum 的输入值无效:"").在另外两个中，我只得到计数:0".

What value does my_enum have in row 3? Is there a way to express it? I've tried SELECT COUNT(*) FROM test WHERE my_enum != ''::MyEnum and SELECT COUNT(*) FROM test WHERE my_enum = null::MyEnum and SELECT COUNT(*) FROM test WHERE my_enum != ALL(enum_range(null::MyEnum)). In the first case, I get an error ("invalid input value for enum myenum: """). In the other two, I just get "count: 0".

似乎没有办法在 Postgres 的第 3 行中表达 my_enum 的值 :(.如何只过滤那些设置了 my_enum 的行?

There seems to be no way to express the value of my_enum in row 3 in Postgres :(. How can I filter only those rows which have my_enum set?

推荐答案

AlexM 回答了我的问题.我正在寻找的代码是:

AlexM answered my question. The code I was looking for is:

SELECT COUNT(*) FROM test WHERE my_enum IS NOT NULL

我在测试 my_enum = null::MyEnum 时的错误是认为 null = null 是正确的.Postgres 文档 说:

My mistake in testing my_enum = null::MyEnum was thinking that null = null would be true. The Postgres documentation says:

不要写 expression = NULL 因为 NULL 不等于"NULL.(空值代表一个未知值，不知道两个未知值是否相等.)

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

我也被这种行为绊倒了(在测试 my_enum != ALL(enum_range(null::MyEnum)))

I was also tripped up (in testing my_enum != ALL(enum_range(null::MyEnum))) by the behavior that

当任一输入为空时，普通比较运算符产生空值(表示未知")，不是真或假.(文档)

Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. (documentation)

这篇关于如何在 postgres 中识别具有空/未定义枚举值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！