Changing row delimiter in VSQL

Consider a scenario when you export some data with VSQL and some of the string columns have new line characters as part of column value. Now if you want to load the data file to some other table using COPY statement, or may be parsing the data using some of your custom parser, the new line characters in string column are bound to pose problem.

One of the easiest and cleanest way to tackle this problem is to change VSQL row delimiter to some character other than new line. So that when you export data, the rows will be delimited by the character of your choice and hence your COPY command or your custom parser can tell new line characters in column value to row delimiter.


In the example below, record separator is changed from default \n to '~'. The fields are separated by '|' and records are separated by '~"


At command line:

Use -R option (along with -A for unaligned output) to specify field separator.

$ vsql -o my_table.out -c "select * from my_table" -A -R '~';
$ cat my_table.out
id|status~1001|t~1002|f~1003|t~1004|f~(4 rows)


At vsql prompt:

Use \pset to specify new record separator
vsql=> \pset recordsep '~' 
Record separator is "~". 

Set output format as unaligned
vsql=> \a 
Output format is unaligned. 

vsql=> \o my_table2.out
vsql=> select * from my_table;
vsql=> \o