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.
asked Dec 19, 2013 at 10:07
5 Answers
Use psql and redirect stream to file:
psql -U <USER> -d <DB_NAME> -c "COPY <YOUR_TABLE> TO stdout DELIMITER ',' CSV HEADER;" > file.csv
-
Thx! And how do you specify <YOUR_TABLE> with quotation marks? (I have a table named user and usually I have to write "user" ) Commented Jun 25, 2015 at 12:35
COPY your_table TO '/path/to/your/file.csv' DELIMITER ',' CSV HEADER;
For more details go to this manual
answered Dec 19, 2013 at 10:22
-
1Doesn'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)– user330315Commented Dec 19, 2013 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. Commented Dec 19, 2013 at 10:46
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).
answered May 27, 2015 at 21:01
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, 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? Commented Dec 19, 2013 at 10:21
-
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.– DraugrCommented Dec 19, 2013 at 10:30
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.
importbutton. Which SQL client tool are you using? Inpsqlyou could use\copyinstead ofcopy(which writes the file to the client, not the server) other SQL tools have other features.