Field Separator on select and copy

Tim_1

Hi,
Want to create an extract from one cluster to a flat file and then copy that into another cluster, but need to change the default field separator to . The following is not working:-
vsql -c "select * from poctest.bond limit 5;" -q -t -o BondTest.txt -P format=unaligned -P fieldsep=E'\011'
What is the correct format for the fieldsep parameter?
I'm assuming that the following would then work?
cat BondTest.txt | vsql -At -c "copy POCTEST.bond from stdin delimiter E'\011';"
Thanks in advance.
Tim

Tim_1

Thanks, works perfectly!

Tim_1

My bad, the separator should be a TAB, went missing from the original post

marcothesane

I found it difficult to pass a 'tab' through the command line.

My workaround is to cheat the tab character into an environment variable, using perl, and then to use it in the vsql command:
For the export, two commands - note that, in the vsql call, all configuration switches come before the "-c" switch:

$ export TAB=`perl -e 'print qq(\t)'`
$  vsql -q -t -P format=unaligned -P fieldsep="$TAB"  -c "select * from foo limit 5"
1.000000000000000   Arthur  Dent    2017-02-05 00:00:00
2.000000000000000   Ford    Prefect 2017-02-05 00:00:00
3.000000000000000   Zaphod  Beeblebrox  2017-02-05 00:00:00
4.000000000000000   Tricia  McMillan    2017-02-05 00:00:00
5.000000000000000   Gag Halfrunt    2017-02-05 00:00:00

Bryan_H

Yes, though you can also use \t as the delimiter to indicate TAB:

copy myTable from stdin delimiter E'\t';