Re: selective export for subsequent import (COPY)

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
>>
>>
>>
>
>