Thread: copy query results to file

On Dec 14, 2011 3:06 PM, "John Payne" <jcpayne@uw.edu> wrote:
>
> Hi, a very basic question here: I am using an older version of Postgresql (8.1.11 running on Redhat Fedora 5.3) on a remote server that times me out fairly quickly, and I need to run some queries that take a long time to complete.  Therefore I would like to story the query results in a file in a .csv format.  I see from the documentation that there are two basic approaches:
>
>  
>
> 1.        Write a query  using “COPY table TO ‘file’ CSV”.
>
> 2.       Use psql to output the data, as in:
>
> a.       \o
>
> b.      Query here….
>
> c.       \o
>
>  
>
> Neither approach is working for me.  1) The COPY query doesn’t work on views, and gives me an error when I try to run it as “COPY (select  …).  2) The psql produces output in an inconvenient format with pipe symbols and headers and underlines.
>
>  
>
> Can anyone advise?
>

One question: What exactly do you mean with timeout? Timeout of the database connection, or SSH timeout?

If the latter, can't you install screen or tmux?

Rgds,

On Dec 14, 2011 3:06 PM, "John Payne" <jcpayne@uw.edu> wrote:
>

----->8 snip

>
> Neither approach is working for me.  1) The COPY query doesn’t work on views, and gives me an error when I try to run it as “COPY (select  …).  2) The psql produces output in an inconvenient format with pipe symbols and headers and underlines.
>

One more question: Can't you clean up the output with a script?

Rgds,

John Payne, 14.12.2011 02:49:
> 2.Use psql to output the data, as in:
>
> a.\o
>
> b.Query here….
>
> c.\o
>
> The psql produces output in an inconvenient format with pipe symbols and headers and underlines.

You can use the options:

\t
\a
\pset fieldsep ,

to get a more "CSV" like output in psql. If you want a tab character as the field delimiter, you can use:

\pset fieldsep '\t'

So the whole thing would be:

\a
\t
\pset fieldsep ,
\o

SELECT ...

\o

Thomas

On 14 December 2011 10:40, Thomas Kellerer <spam_eater@gmx.net> wrote:
[...]
> You can use the options:
>
> \t
> \a
> \pset fieldsep ,
>
> to get a more "CSV" like output in psql.

I think it's worth mentioning that this is "CSV-like" and not actual
CSV.  It that doesn't work if you have any commas in your data.

As you say you could set the field separator to something else.

--
Michael Wood <esiotrot@gmail.com>