export table to csv on postgres

Question

How can I export a table to .csv in Postgres, when I'm not superuser and can't use the copy command?

I can still import the data to postgres with "import" button on the right click, but no export option.

Zito

Postgres doesn't have an import button. Which SQL client tool are you using? In psql you could use \copy instead of copy (which writes the file to the client, not the server) other SQL tools have other features. – Anywise

I'm using Pgadmin's SQL tool. And the import button is right click on the table name in pgadmin. Which tool do you recommend? – Zito

Answer 1

Use psql and redirect stream to file:

psql -U <USER> -d <DB_NAME> -c "COPY <YOUR_TABLE> TO stdout DELIMITER ',' CSV HEADER;" > file.csv

Zeitler

Thx! And how do you specify <YOUR_TABLE> with quotation marks? (I have a table named user and usually I have to write "user" ) – Colobus

Answer 2

COPY your_table TO '/path/to/your/file.csv' DELIMITER ',' CSV HEADER;

For more details go to this manual

Sensor

Doesn't work for non-superusers because of permissions on the server (and you would need direct file-system access to the Postgres server to retrieve the file) – Anywise

Thanks, but stdin/stdout isn't a good option in my case, as long as I'm dealing with a lot of rows. – Zito

Answer 3

Besides what marvinorez's suggests in his answer you can do, from psql:

\copy your_table TO '/path/to/your/file.csv' DELIMITER ',' CSV HEADER

On the other hand, from pgadmin3, you can also open the table by right clicking on it's name and then selecting View Data. Then you can click on the upper-left corner of the table (where the column name row joins with the row number column, a gray empty square) to select all rows. Finally, you can copy with CtrlC or Edit -> Copy in the menu. The data will be copied to the clipboard in csv format, delimited by semicolon ;. You can then paste it in LibreOffice Calc, MS Excel to display for instance.

If your table is large (what is large depends on the amount of RAM of your machine, among other things) it might not fit in the clipboard, so in that case, I would not use this method but the first one (\copy).

Effulgence

Answer 4

The easiest way would indeed be a COPY to stdout I think. If you can't do this, how about using pg_dump and then transform the output file with sed, AWK or even a text editor? This should work even with search and replace in an acceptable amount of time :)

Wretch

Draugr, Thank you for your answer, but sdtout isn't a good option - i have too many rows to export (more than 100K). Can you please provide more detailed explanation for "pg_dump" option? – Zito

Sure, check out the documentation at postgresql.org/docs/9.3/static/app-pgdump.html. Just use it to dump a table and have a look at the result. You will see quickly how you need to edit it so you only have the data rows. If you have many rows you really should use sed or awk to process it, I used it for such tasks regularly. – Wretch

Answer 5

I was having trouble with superuser and running psql, I took the simple stupid way using PGAdmin III.
1) SELECT * FROM ;
Before running select Query in the menu bar and select 'Query to File' This will save it to a folder of your choice. May have to play with the settings on how to export, it likes quoting and ;.

2) SELECT * FROM ;
run normally and then save the output by selecting export in the File menu. This will save as a .csv

This is not a good approach for large tables. Tables I have done this for are a few 100,000 rows and 10-30 columns. Large tables may have problems.

Boraginaceous