15

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.

the
  • 21,007
  • 11
  • 68
  • 101
Luba Weissmann
  • 187
  • 1
  • 1
  • 8
  • 1
    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. –  Dec 19 '13 at 10:22
  • 1
    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? – Luba Weissmann Dec 19 '13 at 10:48

5 Answers5

37

Use psql and redirect stream to file:

psql -U <USER> -d <DB_NAME> -c "COPY <YOUR_TABLE> TO stdout DELIMITER ',' CSV HEADER;" > file.csv
marvinorez
  • 593
  • 4
  • 7
  • Thx! And how do you specify with quotation marks? (I have a table named user and usually I have to write "user" ) – stallingOne Jun 25 '15 at 12:35
3
COPY your_table TO '/path/to/your/file.csv' DELIMITER ',' CSV HEADER;

For more details go to this manual

Thusitha Sumanadasa
  • 1,669
  • 2
  • 22
  • 30
  • 1
    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) –  Dec 19 '13 at 10:23
  • Thanks, but stdin/stdout isn't a good option in my case, as long as I'm dealing with a lot of rows. – Luba Weissmann Dec 19 '13 at 10:46
2

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).

user2553863
  • 682
  • 1
  • 8
  • 17
0

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 :)

Draugr
  • 436
  • 4
  • 11
  • 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? – Luba Weissmann Dec 19 '13 at 10:21
  • Sure, check out the documentation at http://www.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. – Draugr Dec 19 '13 at 10:30
0

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.

Jesse
  • 213
  • 3
  • 13