postgresql 的 is distinct from、is not distinct from

os: centos 7.4
db: postgresql 11.5

在 postgresql 开发过程中有时会用到 is 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)

参考: