PostgreSQL to File

  • Databases
  • 2 min read

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.


6 thoughts on “PostgreSQL to File”

  1. fruit mocking party

    Howdy just wanted to give you a quick heads up. The words in your article seem to be running off the screen in Opera. I’m not sure if this is a formatting issue or something to do with browser compatibility but I thought I’d post to let you know. The layout look great though! Hope you get the issue fixed soon. Thanks

  2. Pingback: Ubuntu command line hints | Sudopedia

  3. 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 *