How to export table as CSV with headings on Postgresql

Question

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

User · Answer

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.

User · Answer

copy  anysql query datawanttoexport  to  fileablsoutepathwihname  delimiter     csv header   Using this u can export data also

User · Answer

COPY products 273 TO   tmp products 199 csv  WITH  FORMAT CSV  HEADER     as described in the manual

User · Answer

Try this   COPY products 273  FROM   tmp products 199 csv  DELIMITER     CSV HEADER

User · Answer

From psql command line    COPY my table TO  filename  CSV HEADER   no semi-colon at the end

User · Answer

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

User · Answer

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

User · Answer

The simplest way  using psql  seems to be by using --csv flag   psql --csv -c  SELECT   FROM products 273   gt    tmp products 199 csv

User · Answer

This works   psql dbname -F   --no-align -c  SELECT   FROM TABLE

User · Answer

For version 9 5 I use  it would be like this   COPY products 273 TO   tmp products 199 csv  WITH  FORMAT CSV  HEADER

User · Answer

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        gt  foo data csv

User · Answer

This solution worked for me using  copy    psql -h  lt host gt  -U  lt user gt  -d  lt dbname gt  -c   copy  lt table name gt  FROM   lt path to csvfile file csv gt   with  format csv header true  delimiter

User · Answer

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

[postgresql] How to export table as CSV with headings on Postgresql?

Examples related to postgresql

Examples related to csv

Examples related to export-to-csv

Examples related to heading

Examples related to postgresql-copy