欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

postgresql 的 is distinct from、is not distinct from

程序员文章站 2022-04-28 19:17:35
...

os: centos 7.4
db: postgresql 11.5

在 postgresql 开发过程中有时会用到 is not distinct from 和 is not distinct from 这个功能

is distinct from

功能描述
A和B的数据类型、值不完全相同返回 true
A和B的数据类型、值完全相同返回 false
将空值视为相同。

postgres=# \x
Expanded display is on.
postgres=# select 1 is distinct from 1,
1 is distinct from 2,
1 is distinct from '1',
'1' is distinct from '1',
1 is distinct from null,
null is distinct from null
;
-[ RECORD 1 ]
?column? | f
?column? | t
?column? | f
?column? | f
?column? | t
?column? | f

is not distinct from

功能描述
A和B的数据类型、值不完全相同返回 false
A和B的数据类型、值完全相同返回 true
将空值视为相同。

postgres=# \x
Expanded display is on.
postgres=# select 1 is not distinct from 1,
1 is not distinct from 2,
1 is not distinct from '1',
'1' is not distinct from '1',
1 is not distinct from null,
null is not distinct from null
;
-[ RECORD 1 ]
?column? | t
?column? | f
?column? | t
?column? | t
?column? | f
?column? | t

第三条看起来有点不太符合规则

postgres=# select 1 is not distinct from '1';
-[ RECORD 1 ]
?column? | t

为 t ,这怎么理解了?

postgres=# select pg_typeof(1),pg_typeof('1');
-[ RECORD 1 ]------
pg_typeof | integer
pg_typeof | unknown

有意思吧,pg_typeof(‘1’) 居然是 unknown, 而我们把它想象成字符串了。

postgres=# select 1 is not distinct from cast('1' as varchar);
ERROR:  operator does not exist: integer = character varying
LINE 1: select 1 is not distinct from cast('1' as varchar);
                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

postgres=# select 1 is not distinct from cast('1' as int);
 ?column? 
----------
 t
(1 row)

postgres=# select cast('1' as varchar) is not distinct from 1;
ERROR:  operator does not exist: character varying = integer
LINE 1: select cast('1' as varchar) is not distinct from 1;
                                    ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

postgres=# select cast('1' as varchar) is not distinct from cast('1' as varchar);
 ?column? 
----------
 t
(1 row)

参考: