Thread: How Copy from a view to CSV file

I want to copy view's data into a .CSV file. But postgresql is generating an error as
ERROR:  cannot copy from view 'myview'

I am executing the command

COPY temp_error_view TO '/tmp/tempError.csv' USING DELIMITERS ',' WITH  CSV ;

I think postgresql doesn't allow to copy from view to csv directly. Do i have to create a temp table from the view and then copy that data to the csv file??

Vijay Sharma wrote:
> I want to copy view's data into a .CSV file. But postgresql is
> generating an error as
> ERROR:  cannot copy from view 'myview'
>
> I am executing the command
>
> COPY temp_error_view TO '/tmp/tempError.csv' USING DELIMITERS ','
> WITH  CSV ;
>
> I think postgresql doesn't allow to copy from view to csv directly. Do
> i have to create a temp table from the view and then copy that data to
> the csv file??

Right, you can only COPY directly from a table, not a view.  But you can
copy out anything you can select, so this should work:

COPY (SELECT * FROM temp_error_view) TO '/tmp/tempError.csv' USING
DELIMITERS ',' WITH  CSV ;

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

Greg Smith <greg@2ndquadrant.com> wrote:
>> I think postgresql doesn't allow to copy from view to csv directly. Do
>> i have to create a temp table from the view and then copy that data to
>> the csv file??
>
> Right, you can only COPY directly from a table, not a view.  But you can
> copy out anything you can select, so this should work:
>
> COPY (SELECT * FROM temp_error_view) TO '/tmp/tempError.csv' USING
> DELIMITERS ',' WITH  CSV ;

But this works only with version 8.2+, not with 8.1 or lesser.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°