I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings.
My code looks as follows:
COPY products_273 to '/tmp/products_199.csv' delimiters',';
From psql command line:
\COPY my_table TO 'filename' CSV HEADER
no semi-colon at the end.
COPY
command requires admin access
psql
approach, one can save the output anywhere one has access to. I just used the psql
approach to get data from a remote server into a local file. Very slick.
pg_dump -h remote | pg_restore -h localhost
.
psql -c "\COPY (SELECT * FROM mytable) TO STDOUT" > mytable.csv
instead of just table name, you can also write a query for getting only selected column data.
COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;
with admin privilege
\COPY (select id,name from tablename) TO 'filepath/aa.csv' DELIMITER ',' CSV HEADER;
\COPY ...
). And at least in my version of psql (9.5.2) I didn't need to specify 'DELIMITER'; the default was a comma.
When I don't have permission to write a file out from Postgres I find that I can run the query from the command line.
psql -U user -d db_name -c "Copy (Select * From foo_table LIMIT 10) To STDOUT With CSV HEADER DELIMITER ',';" > foo_data.csv
This works
psql dbname -F , --no-align -c "SELECT * FROM TABLE"
-F ,
, and use |
as the separator. Thanks!
COPY
statement which creates a file to be reused
gnumeric out.tsv
works.
For version 9.5 I use, it would be like this:
COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);
The simplest way (using psql
) seems to be by using --csv
flag:
psql --csv -c "SELECT * FROM products_273" > '/tmp/products_199.csv'
>
.
--csv
option for my version: psql (PostgreSQL) 11.7 (Ubuntu 11.7-0ubuntu0.19.10.1)
-P pager=off
.
This solution worked for me using \copy
.
psql -h <host> -U <user> -d <dbname> -c "\copy <table_name> FROM '<path to csvfile/file.csv>' with (format csv,header true, delimiter ',');"
Heres how I got it working power shell using pgsl connnect to a Heroku PG database:
I had to first change the client encoding to utf8 like this: \encoding UTF8
Then dumped the data to a CSV file this:
\copy (SELECT * FROM my_table) TO C://wamp64/www/spider/chebi2/dump.csv CSV DELIMITER '~'
I used ~ as the delimiter because I don't like CSV files, I usually use TSV files, but it won't let me add '\t' as the delimiter, so I used ~ because its a rarely used characeter.
The COPY command isn't what is restricted. What is restricted is directing the output from the TO to anywhere except to STDOUT. However, there is no restriction on specifying the output file via the \o command.
\o '/tmp/products_199.csv';
COPY products_273 TO STDOUT WITH (FORMAT CSV, HEADER);
copy (anysql query datawanttoexport) to 'fileablsoutepathwihname' delimiter ',' csv header;
Using this u can export data also.
I am posting this answer because none of the other answers given here actually worked for me. I could not use COPY
from within Postgres, because I did not have the correct permissions. So I chose "Export grid rows" and saved the output as UTF-8.
The psql
version given by @Brian also did not work for me, for a different reason. The reason it did not work is that apparently the Windows command prompt (I was using Windows) was meddling around with the encoding on its own. I kept getting this error:
ERROR: character with byte sequence 0x81 in encoding "WIN1252" has no equivalent in encoding "UTF8"
The solution I ended up using was to write a short JDBC script (Java) which read the CSV file and issued insert statements directly into my Postgres table. This worked, but the command prompt also would have worked had it not been altering the encoding.
Try this: "COPY products_273 FROM '\tmp\products_199.csv' DELIMITER ',' CSV HEADER"
In pgAdmin
, highlight your query statement just like when you use F5
to execute and press F9
- this will open the file browser so you can pick where you save your CSV.
If you are using Azure Data Studio, the instruction are here: Azure Data Studio: Save As CSV.
I know this isn't a universal solution, but most of the time you just want to grab the file by hand.
Success story sharing
COPY
requires administrator privileges. Use\COPY
instead if you run into issues.COPY products_273 TO '/tmp/products_199.csv' WITH (FORMAT CSV, HEADER);