Re: Exporting data from PostgreSQL

--- Michael Glaesemann <grzm@myrealbox.com> wrote:
>
> On Friday, Jun 13, 2003, at 16:38 Asia/Tokyo, paul
> butler wrote:
> > Go into psql
> >
> > mfx=# \pset fieldsep '\t'       // sets field
> delimiter to tab
> > Field separator is '    '.
> > mfx=# select * from actor;     // make your query
> > mfx=# \o d:\A\tester.txt       // set file to output
> results
> > mfx=# select * from actor;    // execute query
>
> Thanks, Paul! That is exactly what I was looking
> for. I was able to
> make a file! :)
> Though, for some reason still unknown to me, it
> wasn't tab-delimited,
> even though I entered the commands above and I got
> back the "Field
> separator is '  '." line. It came out the standard
> pipes and dashes,

I have done this kind of exporting a lot (really
functional if you are using Samba, although AFAIK that
only works with Windows?).  I have never been able to
make the "\pset fieldsep '\t'" work correctly either.
I usually work around the problem by specifying a
literal tab, using the Ctrl-V/Ctrl-I key combination.

Another thing you may want to do: also issue the "\a"
psql command, which eliminates the space padding
normally used on output to align the result rows for
display.  The existence of superfluous spaces in your
output may or may not matter for whatever you are
doing (evidently not, so far), but I find it safer to
just eliminate the possibility of error.

You can also use "\t" to eliminate the headings etc.
if you want only the data.

> though that's easily cleaned up in BBEdit. I'll do a
> bit more reading
> about psql and see if I can't work it out.
>
> One question. Is there a reason why the query is
> included twice, once
> before and once after the \o line? I tried it
> omitting the first query
> and the results seemed just fine.

The only reason that I can see is that you have the
opportunity to test the output before sending it to a file.

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com