Bash - read Postgresql zero separated fields into array

Question

I want to read the output of a psql query produced with --field-separator-zero into an array inside my bash script. The best I have tried was the following:

psql -w  -t --quiet --no-align --field-separator-zero -c $'select nickname,first_name,last_name,email from users' | while IFS= read -d '' -a USERS; do 
     echo ${USERS[0]} ${USERS[1]} ${USERS[2]} ${USERS[3]}; 
done;

The above would return each field of a row as a new array. Changing the delimiter to anything else would make the process work, but the problem is the nickname field might contain any character, so I'm forced to use the safe NUL char as a delimiter. Is there any way to do this ?

Charles Duffy · Accepted Answer · 2014-10-30 06:01:55Z

2

I'm assuming here that nickname is a unique key; make the appropriate modifications if a different field should be used in that role.

The below code reads the data into a series of associative arrays, and emits each row in turn.

Note that associative arrays are a Bash 4 feature; if you're on Mac OS, which ships 3.2, use MacPorts or a similar tool to install a modern release.

declare -A first_names=( ) last_names=( ) emails=( )
while IFS= read -r -d '' nickname && \
      IFS= read -r -d '' first_name && \
      IFS= read -r -d '' last_name && \
      IFS= read -r -d '' email; do
  first_names[$nickname]=$first_name
  last_names[$nickname]=$last_name
  emails[$nickname]=$email
done < <(psql ...)

echo "Found users: "
for nickname in "${!emails[@]}"; do
  printf 'nickname - %q\n' "$nickname"
  printf 'email - %q\n' "${emails[$nickname]}"
  printf 'first name - %q\n' "${first_names[$nickname]}"
  printf 'last name - %q\n' "${last_names[$nickname]}"
  echo
done

This technique is described in BashFAQ #1 -- search for -print0 to find its mention.

edited Oct 30, 2014 at 6:01

answered Oct 30, 2014 at 5:56

Charles Duffy

299k43 gold badges441 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Iman Over a year ago

Really complicated related to what I expected, but I think there's no better way due to bash's limitations

Charles Duffy Over a year ago

Doesn't look complicated to me, but then, I suppose that's largely a function of familiarity. If there's a particular thing you'd like to avoid (such as the use of associative arrays), let me know.

Charles Duffy Over a year ago

@Iman, ...really, the core point here is IFS= read -r -d ''. Granted, 2/3rds of that are clearing unfortunate defaults mandated by standards compliance (without -r, read is defined by POSIX to expand backslash-escape sequences rather than returning data literally; without clearing IFS, trailing whitespace is removed from fields after they're read, again per POSIX). The -d '' is what tells it that the delimiter is NUL -- which works because the shell uses C strings; dereference the first character of an empty string, and you get a NUL.

Collectives™ on Stack Overflow

Bash - read Postgresql zero separated fields into array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related