Postgresql Query to Excel Sheet

PostgreSQL Query to Excel Sheet

You could install the PostgreSQL ODBC driver on the Windows machine, and then connect Excel to the database like explained in this blog post (except using ODBC rather than OLEDB).

I haven't tested this. I'm not really sure if this makes it any easier than exporting CSV and fixing it (given what Excel is like).

EDIT (thanks @Tometzky): The best solution is to use the PostgreSQL ODBC driver to connect Microsoft Access to the database and from Access export to Excel.

PostgreSQL query output as a excel file

You could use csv to save your data and open/work with them in Excel. Syntax would be something like:

Copy (SELECT column_1,
             column_2,
      FROM company; 
     ) 
To '/tmp/test.csv' With CSV;

This query would run on server. You could also make usage of \query which is running on your client and saving the data locally. See also another SE question

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

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

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

How to export a PostgreSQL query output to a csv file

Modern syntax:

COPY (SELECT * FROM ...) TO '/tmp/filename.csv' (FORMAT csv);

So the 162 rows of my output table have been copied in the shell. How
can I paste or move them to a csv file?

The result is the CSV file. Open it with any spreadsheet program using matching delimiters. The manual:

The default is a tab character in text format, a comma in CSV format

The psql meta command \copy is a wrapper around the SQL COPY function. It writes and reads files local to the client (while COPY uses files local to the server) and does not require superuser privileges.

See:

Export specific rows from a PostgreSQL table as INSERT SQL script
PostgreSQL: export resulting data from SQL query to Excel/CSV

Unable to create a table and import the data from excel using SQL query in PostgreSQL

As @a_horse_with_no_name clarified,

The query needs to be run one by one.

Save PL/pgSQL output from PostgreSQL to a CSV file

Do you want the resulting file on the server, or on the client?

Server side

If you want something easy to re-use or automate, you can use Postgresql's built in COPY command. e.g.

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

This approach runs entirely on the remote server - it can't write to your local PC. It also needs to be run as a Postgres "superuser" (normally called "root") because Postgres can't stop it doing nasty things with that machine's local filesystem.

That doesn't actually mean you have to be connected as a superuser (automating that would be a security risk of a different kind), because you can use the SECURITY DEFINER option to CREATE FUNCTION to make a function which runs as though you were a superuser.

The crucial part is that your function is there to perform additional checks, not just by-pass the security - so you could write a function which exports the exact data you need, or you could write something which can accept various options as long as they meet a strict whitelist. You need to check two things:

Which files should the user be allowed to read/write on disk? This might be a particular directory, for instance, and the filename might have to have a suitable prefix or extension.
Which tables should the user be able to read/write in the database? This would normally be defined by GRANTs in the database, but the function is now running as a superuser, so tables which would normally be "out of bounds" will be fully accessible. You probably don’t want to let someone invoke your function and add rows on the end of your “users” table…

I've written a blog post expanding on this approach, including some examples of functions that export (or import) files and tables meeting strict conditions.

Client side

The other approach is to do the file handling on the client side, i.e. in your application or script. The Postgres server doesn't need to know what file you're copying to, it just spits out the data and the client puts it somewhere.

The underlying syntax for this is the COPY TO STDOUT command, and graphical tools like pgAdmin will wrap it for you in a nice dialog.

The psql command-line client has a special "meta-command" called \copy, which takes all the same options as the "real" COPY, but is run inside the client:

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

Note that there is no terminating ;, because meta-commands are terminated by newline, unlike SQL commands.

From the docs:

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

Your application programming language may also have support for pushing or fetching the data, but you cannot generally use COPY FROM STDIN/TO STDOUT within a standard SQL statement, because there is no way of connecting the input/output stream. PHP's PostgreSQL handler (not PDO) includes very basic pg_copy_from and pg_copy_to functions which copy to/from a PHP array, which may not be efficient for large data sets.

Postgresql Query to Excel Sheet