Creating a delimited file

[szaunbr]

I would like to run a select statement and save the results of the statement as a csv. I know it is possible, but not sure how to do it.

I am using this statement:

select * from personalinfo

I would like to save the results in a folder I have set up.

Any help would be appreciated.

 

[feherke]

Hi

I suppose you want to run that [tt]select[/tt] from the [tt]psql[/tt] interactive terminal.

If you want the entire table, is easier with [tt]copy[/tt] :

copy personalinfo to '/the_folder_you_set_up/personalinfo.csv';

Or you can set the output format then redirect the output to the file :

[blue]psql=#[/blue] \f '\t'
Field separator is '    '.
[blue]psql=#[/blue] \a
Output format is unaligned.
[blue]psql=#[/blue] \t
Showing only tuples.
[blue]psql=#[/blue] \o /the_folder_you_set_up/personalinfo.csv
[blue]psql=#[/blue] select * from personalinfo;
[blue]psql=#[/blue] \o

Or you can do it from the command line too ( assuming Linux and [tt]bash[/tt] ) :

[blue]master #[/blue] psql -F $'\t' -A -t -d [green][i]database[/i][/green] -h [green][i]host[/i][/green] -U [green][i]user[/i][/green] -c 'select * from personalinfo' > /the_folder_you_set_up/personalinfo.csv

Feherke.

http://rootshell.be/~feherke/

 

[szaunbr]

I just realized, I am not selecting from a table, I created a view that has all of the personal info that I need, and none of the extra junk. The personalinfo "table" is really a view. I'm not sure if this changes up your previous posts or not. Thank you for the info.

 

[feherke]

Hi

Then you can not use the [tt]copy[/tt] command.

COPY can only be used with plain tables, not with views.

http://postgresql.org/docs/8.1/static/sql-copy.html

But the [tt]select[/tt] with formatted and redirected output will still work.

Feherke.

http://rootshell.be/~feherke/

 

[szaunbr]

thanks a million