Backslashes and NULL Values

There is potential for confusion if the character used as a column delimiter also exists in user data. If both appear the same way in the file, COPY...FROM would be unable to determine whether the character is a delimiter or user data.

COPY avoids any confusion by specially marking delimiters appearing in user data. It precedes them with a backslash (\). If a pipe is the delimiter, COPY...TO uses pipes (|) for delimiters, and backslash-pipes (\|) for pipes in user data. In Figure , for example, each column is separated by a pipe, but the pipe that appears in user data is output as abc\|def.  

        test=> DELETE FROM copytest;
        DELETE 3
        test=> INSERT INTO copytest
        test-> VALUES (4, 837.20, 'abc|def', NULL);
        INSERT 174786 1
        test=> COPY copytest TO stdout USING DELIMITERS '|';
        4|837.20|abc\|def|\N  

Use of a backslash causes any character that follows it to be treated specially. As a result, a backslash in user data is output as two backslashes (\\).

Another special backslash used in Figure  is \N, which represents NULL. It prevents NULL values from being confused with user values.

To change the default NULL representation, you use WITH NULL AS. For example, the command COPY copytest TO '/tmp/copytest.out' WITH NULL AS '?' will output NULL values as question marks. Unfortunately, it will make a user column containing a single question mark indistinguishable from a NULL in the file. To output NULL values as blank columns, use the command COPY copytest TO '/tmp/copytest.out' WITH NULL AS '\,'. To treat empty columns as NULL values on input, use COPY copytest FROM '/tmp/copytest.out' WITH NULL AS '\,'.

Table  summarizes the delimiter, NULL, and backslash handling of COPY.

 

Table: Backslashes understood by COPY

Backslash String	Description
\TAB	tab if using default delimiter tab
\|	pipe if using pipe as the delimiter
\N	NULL if using the default NULL output
\b	backspace
\f	form feed
\n	newline
\r	carriage return
\t	tab
\v	vertical tab
\###	character represented by octal number ###
\\	backslash

The first two lines in the table show that preceding a character with a backslash prevents the character from being interpreted as a delimiter. The next line shows that \N means NULL under the default representation. The other backslash entries show simple representations for common characters. The last line shows that a double-backslash is required to represent a literal backslash.