chrisj wrote:
Thanks Brent, very much appreciated, your first suggestion is perfect.
the translate suggestion assumes that there are no commas in the data, but
that is why I wanted to use tab.
I figured as much :-) Note that you can use -F "|" for a pipe symbol, or
use any other character as the field
separator in the psql command line, then change that to a tab with tr,
if you do have commas in the data.
It also scripts up nicely:
...
FSEP="|"
psql -d .... -F "$FSEP" .... | tr "$FSEP" "\t" > $FILE
...
Brent
Brent Wood wrote:
chrisj wrote:
Thanks Alan,
This helped a lot, but ideally I want a tab field delimiter and -F '\t'
does
not seem to work, any ideas??
I noticed one other post on this same problem of the fieldsep '\t' not
working but the only advise offered was to use \pset. Can \pset be used
on
the command line, I can only get it to work within the psql command
processor.
You can always have a text file (file.sql):
\pset ...
select .....
the run the commands is a single client connection with
psql database -Atf file.sql
This runs a file of sql commands in a single psql connection instead of
opening a new connection for every -c "" command.
Thus the result of the \pset is still in force when the next sql
statement is executed.
or run your command as it is & pipe the output through tr to translate
the commas to tabs.
You can see what tr does using
echo "1,2" | tr "," "\t"
eg:
psql $DATABASEHOST -U $DATABASEUSER -d $DATABASE -q -P footer -A -F , -c
"$DETAIL_SQL" | tr "," "\t" >table.csv
Cheers,
Brent Wood
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
