OracleからPostgreSQLへの移行（#NULLと空文字の違い）

空文字の扱い

Oracleでは自動的に空文字（""）を nullに変換しますが、PostgreSQLではnullに変換しません。
つまり、PostgreSQLでは空文字（""）とnullは別の値として扱われます。
そのため、システムが使用するDBをOracleからPostgreSQLに変更する場合は、その違いを意識してSQLを修正する必要があります。

実際にどのような動きをするか、Oracle、PostgreSQLの場合それぞれで試してみます。

Oracleの場合

まず、null, '', 文字列の3レコードを作成します。

SQL> create table test_oracle (
  kind varchar2(10),
  txt varchar2(10)
);
表が作成されました。
SQL> insert into test_oracle values('null', null);
1行が作成されました。
SQL> insert into test_oracle values('empty', '');
1行が作成されました。
SQL> insert into test_oracle values('aaa', 'aaa');
SQL> commit;
コミットが完了しました。

以下のように検索すると
　is null：2件
　is not null: 1件
　= '': 0件
となります。

Oracleでは''はと同じ扱いになるので、is nullが2件になっています。

SQL> select * from test_oracle where txt is null;

KIND       TXT
---------- ----------
null
empty

SQL>  select * from test_oracle where txt is not null;

KIND       TXT
---------- ----------
aaa        aaa

SQL> select * from test_oracle where txt = '';

レコードが選択されませんでした。

PostgreSQLの場合

Oracleの場合と同様にnull, '', 文字列が3レコードを作成します。

postgres=# create table test_postgres (
  kind varchar(10),
  txt varchar(10)
);
CREATE TABLE
postgres=# insert into test_postgres values('null', null);
INSERT 0 1
postgres=# insert into test_postgres values('empty', '');
INSERT 0 1
postgres=# insert into test_postgres values('aaa', 'aaa');
INSERT 0 1

以下のように検索すると検索すると
　is null：1件（Oracleは2件）
　is not null: 2件（Oracleは1件）
　= '': 1件（Oracleは0件）
となります。

Oracleと結果が異なることが分かります。

postgres=# select * from test_postgres where txt is null;
 kind | txt 
------+-----
 null | 
(1 row)

postgres=# select * from test_postgres where txt is not null;
 kind  | txt 
-------+-----
 empty | 
 aaa   | aaa
(2 rows)

postgres=# select * from test_postgres where txt = '';
 kind  | txt 
-------+-----
 empty | 
(1 row)

SQLの変更

PostgreSQLでOracleと同じ結果を得るためには以下のようなSQLとなります。

postgres=# select * from test_postgres where coalesce(txt::text, '') = '';
 kind  | txt 
-------+-----
 null  | 
 empty | 
(2 rows)

※coalesce：NULLを別の値に置換する。

postgres=# select * from test_postgres where txt is not null and txt::text <> '';
 kind | txt 
------+-----
 aaa  | aaa
(1 row)

もしくは

postgres=# select * from test_postgres where nullif(txt, '') is not null;
 kind | txt 
------+-----
 aaa  | aaa
(1 row)