psql --(record|field)-separator NUL

Question

Is there some way to make psql separate the fields and records by \0, aka NUL? It's the only way to be able to pass arbitrary data to Bash scripts.

Based on Matthew Wood's answer, I would expect this to print more that 1 on a newly initialized database:

declare -i count=0
echo "\pset recordsep '\000'
\f '\000'
select typname from pg_type" | \
sudo -iu postgres psql --no-align --quiet --tuples-only -d dbname -U username | while IFS= read -r -d ''
do
    #echo "$REPLY"
    let count++
done
if [ -n "$REPLY" ]
then
    #echo "$REPLY"
    let count++
fi
echo $count

Workaround: Iff the SELECT results are unique, you can use this workaround to handle one at a time:

next_record() {
    psql --no-align --quiet --tuples-only -d dbname -U username <<SQL
SELECT colname
  FROM tablename
 WHERE colname > '${1}'
 ORDER BY colname
 LIMIT 1
SQL
}

last_col=
while true
do
    colx="$(next_record "$last_col"; printf x)"
    if [ "$colx" = x ]
    then
        exit
    fi
    col="${colx%$'\nx'}" # The extra \n character is from psql

    # Do your thing here

    col_escaped="${col//"'"/''}" # Double single quotes
    col_escaped="${col_escaped//\\/\\\\}" # Double backslashes
    last_col="$col_escaped"
done

Peter Eisentraut · Accepted Answer · 2012-02-10T09:21:46.423

4

This is not supported. psql uses C print functions to print out the result tables, and printing a zero byte just doesn't work there.

Update: This is now supported in PostgreSQL 9.2-to-be (git).

edited Feb 10 '12 at 09:21

answered Jul 30 '11 at 06:37

Peter Eisentraut

35,221
12
85
90

+1 for the brutal truth. It's too bad they haven't figured out how to print NUL characters in C - It's supported by `sed`, `grep`, `find` `printf` and many more. – l0b0 Aug 02 '11 at 08:46
Well, "they" = me, to some degree. It wouldn't be hard to implement if a broad enough use case were presented. I'll make a note about it. – Peter Eisentraut Aug 03 '11 at 21:09
That would be the best thing ever - Iff text fields cannot contain null fields, and I'm selecting a known number of them, I can parse *any* values without worrying about garbage data. – l0b0 Aug 04 '11 at 09:05

Matthew Wood · Answer 2 · 2011-07-29T00:15:14.863

1

Try this:

psql --field-separator '\000' --no-align -c '<your query>'

Edit: Maybe not. However, it appear to work in psql using these commands:

\f '\000'
\a

edited Jul 29 '11 at 00:15

answered Jul 29 '11 at 00:08

Matthew Wood

16,017
5
46
35

This ends up setting the field separator to an empty string, not a zero byte, as was requested. – Peter Eisentraut Jul 30 '11 at 06:38

score 0 · Answer 3 · answered May 07 '21 at 11:36

0

Newer versions of psql support the --field-separator-zero flag.

answered May 07 '21 at 11:36

Yuri

4,254
1
29
46

psql --(record|field)-separator NUL

3 Answers3