Help us understand the problem. What is going on with this article?

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)

coalesceNULLを別の値に置換する。

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)
mkyz08
SIer&バックエンドエンジニア&日曜プログラマー。 Apache Camel/VoltDB/Oracle/Apache karaf。 基本的に仕事外での自分用のメモ(興味があること)として記事を書いています。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away