PostgreSQL to File

PostgreSQL

PostgreSQL (or simply Postgres) is powerful and extremely popular database management system. It comes with easy to use tool that makes it possible to export output of any query into text file. If you are familiar with Mysql you may know about SELECT … INTO OUTFILE syntax already so here is its equivalent to output PostgreSQL to file:

1. Connect to PostgreSQL DBA:

$ psql -U postgres -p 5432 databasename

2. Set destination for queries’ output into text file:

databasename=> \o /path/to/text/file.txt

3. Execute query that you wish to have saved into text file:

databasename=> select * from mytable where comments != '';

Once done check contents of /path/to/text/file.txt file – it should contain the output of select query.

As usual there is an alternative command for the same purpose:

$ echo "select * from mytable;" | psql -U postgres databasename -o /path/to/text/file.txt

This command is even better as it allows to send various options to PostgreSQL, for example to output query in HTML just add -H key:

$ echo "select * from mytable;" | psql -H -U postgres databasename -o /path/to/text/file.txt

Btw, if you’re migrating to PostgreSQL from MySQL then you may find the following article useful too: PostgreSQL: show tables, show databases, show columns, describe table.

SHARE:
nv-author-image

Stefan Durand

My name is Stefan, I'm the admin of LinuxScrew. I am a full-time Linux/Unix sysadmin, a hobby Python programmer, and a part-time blogger. I post useful guides, tips, and tutorials on common Linux and Programming issues. Feel free to reach out in the comment section.

2 thoughts on “PostgreSQL to File”

  1. The ‘copy’ command is often easier and faster than ‘select *’. It doesn’t write anything into temp space on the way through to the file.

Leave a Reply

Your email address will not be published. Required fields are marked *