postgres での空文字とNULL

postgres は空文字とNULLを区別する。

検証用テーブル

test=# \d test 
            Table "public.test"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 text1  | character varying(20) | 
 text2  | character varying(20) | 

test=#

空文字を投入

test=# INSERT INTO test VALUES ('text2 is zero length', '');
INSERT 0 1
test=# SELECT * from test where text2 = '';                                                                                                                                                
        text1         | text2 
----------------------+-------
 text2 is zero length | 
(1 row)

NULLを投入

test=# INSERT INTO test (text1) VALUES ('text2 is null');
INSERT 0 1
test=# SELECT * from test where text2 is null;
     text1     | text2 
---------------+-------
 text2 is null | 
(1 row)

pg_dump してみる

[hoge@fuga]$ pg_dump test -at test
・・・
COPY test (text1, text2) FROM stdin;
text2 is zero length    
text2 is null   \N
\.
・・・

分かりづらいが、空文字は空文字で出力されている（"text2 is zero length"の右にタブと改行）
NULLは"\N"

テキストモードでcopy してみる

[hoge@fuga]$ psql test -c "COPY (SELECT * FROM test) TO STDOUT"
text2 is zero length    
text2 is null   \N
[hoge@fuga]$

pg_dumpと同じ（てか、pg_dumpがテキストモードのCOPYで出力している。）

CSVでcopy してみる

[hoge@fuga]$ psql test -c "COPY (SELECT * FROM test) TO STDOUT CSV"
text2 is zero length,""
text2 is null,
[hoge@fuga]$

空文字は""、NULLは空文字で出力されている。

おまけ：カラム中のタブ

COPYでタブを含むデータを出力すると\tで表現される。マニュアルによると、COPY FROM はこの \t をタブとして認識してくれるらしい。

test=# TRUNCATE test ;
TRUNCATE TABLE
test=# INSERT INTO test VALUES ('text2 include tab', 'a\tb');
WARNING:  nonstandard use of escape in a string literal
LINE 1: INSERT INTO test VALUES ('text2 include tab', 'a\tb');
                                                      ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
INSERT 0 1
test=# SELECT * from test ;
       text1       | text2  
-------------------+--------
 text2 include tab | a\x09b
(1 row)

test=# 

[hoge@fuga]$ psql test -c "COPY test TO STDOUT"
text2 include tab       a\tb
[hoge@fuga]$