Using ASCII 31 field separator character as Postgresql COPY delimiter

Question

We are exporting data from Postgres 9.3 into a text file for ingestion by Spark.

We would like to use the ASCII 31 field separator character as a delimiter instead of \t so that we don't have to worry about escaping issues.

We can do so in a shell script like this:

#!/bin/bash
DELIMITER=$'\x1F'
echo "copy ( select * from table limit 1) to STDOUT WITH DELIMITER '${DELIMITER}'" | (psql ...) > /tmp/ascii31

But we're wondering, is it possible to specify a non-printable glyph as a delimiter in "pure" postgres?

edit: we attempted to use the postgres escaping convention per http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html

warehouse=> copy ( select * from table limit 1) to STDOUT WITH DELIMITER '\x1f';

and received

ERROR:  COPY delimiter must be a single one-byte character

Answer 1

Try prepending E before the sequence you're trying to use as a delimter. For example E'\x1f' instead of '\x1f'. Without the E PostgreSQL will read '\x1f' as four separate characters and not a hexadecimal escape sequence, hence the error message.

See the PostgreSQL manual on "String Constants with C-style Escapes" for more information.