PostgreSQL export resulting data from SQL query to Excel CSV

Question

I need to export the resulting data from a query in PostgreSQL to Excel CSV  I use PostgreSQL 8 2 11   SQL error   ERROR   relative path not allowed for COPY to file In statement   COPY  select distinct m price  from m product TO   c  auto new txt

User · Answer

In PostgreSQL 9 4 to create to file CSV with the header in Ubuntu   COPY  SELECT   FROM tbl  TO   home user Desktop result sql csv  WITH CSV HEADER    Note  The folder must be writable

User · Answer

If you have error like  ERROR  could not open server file   file   Permission denied  you can fix it that   Ran through the same problem  and this is the solution I found  Create a new folder  for instance  tmp  under  home   cd  home make postgres the owner of that folder   chown -R postgres postgres tmp copy in tmp the files you want to write into the database  and make sure they also are owned by postgres  That s it  You should be in business after that

User · Answer

Several GUI tools like Squirrel  SQL Workbench J  AnySQL  ExecuteQuery can export to Excel files   Most of those tools are listed in the PostgreSQL wiki   http   wiki postgresql org wiki Community Guide to PostgreSQL GUI Tools

User · Answer

The correct script for postgres  Ubuntu  is   COPY  SELECT   FROM tbl  TO   var lib postgres myfile1 csv

User · Answer

Example with Unix-style file name   COPY  SELECT   FROM tbl  TO   var lib postgres myfile1 csv  format csv    Read the manual about COPY  link to version 8 2   You have to use an absolute path for the target file  Be sure to double quote file names with spaces  Example for MS Windows   COPY  SELECT   FROM tbl  TO E  C   Documents and Settings  Tech Desktop  myfile1 csv   format csv    In PostgreSQL 8 2  with standard conforming strings   off per default  you need to double backslashes  because   is a special character and interpreted by PostgreSQL  Works in any version  It s all in the fine manual      filename       emsp The absolute path name of the input or output file  Windows users might need to use an E   string and double backslashes used as path separators    Or the modern syntax with standard conforming strings   on  default since Postgres 9 1    COPY tbl  -- short for  SELECT   FROM tbl  TO   C  Documents and Settings Tech Desktop myfile1 csv    format csv     Or you can also use forward slashes for filenames under Windows   An alternative is to use the meta-command  copy of the default terminal client psql   You can also use a GUI like pgadmin and copy   paste from the result grid to Excel for small queries   Closely related answer    Copy results from a PostgreSQL view in one DB to a table in another   Similar solution for MySQL    Exporting MYSQL data into Excel CSV via php

User · Answer

This worked for me    COPY  SELECT   FROM table       TO E C   Program Files  x86   PostgreSQL  8 4  data  try csv     In my case the problem was with the writing permission to a special folder  though I work as administrator   after changing the path to the original data folder under PostgreSQL I had success

[postgresql] PostgreSQL: export resulting data from SQL query to Excel/CSV

Examples related to postgresql

Examples related to file-io

Examples related to postgresql-copy