3

I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed.

Ideally, I'd like to have the results of a few queries saved to local text files that I can then parse as needed, but if SQL dumping everything works, I'm fine with that too.

While I have experience with databases and programming, I'm not strong with either Windows or PostgreSQL.

Should I just install PostgreSQL on this Windows machine and use the command line tools that would then be found there to create a batch file? Or is there a better option?

| improve this question | |
  • 1
    No need to install Postgres (although that is quite easy). But it's probably the easiest way to get the command line client psql. There are many other SQL clients for Postgres. Several of them also support exporting data into different formats: wiki.postgresql.org/wiki/… – a_horse_with_no_name Aug 19 '15 at 22:19
4

Install the psql client (I believe it's in the pgAdmin III install). Add it to your PATH.

Put the following in %appdata%\postgresql\pgpass.conf (you may need to create that file):

hostname:port:database:username:password

In cmd:

psql -c "\copy (select * from foo) to 'fooLocal.txt';"

then fooLocal.txt will be a tab-delimited representation of the output of your query.

\copy (as opposed to COPY) can copy data from a remote server and doesn't need superuser privileges.

If you want to run that psql command on a schedule, you can use Task Scheduler (GUI) or schtasks (CLI), which is somewhat like cron for Windows.

| improve this answer | |
  • schtasks is just the command line UI for the Scheduled Tasks system. A Windows admin may be much more comfortable setting up the task via the UI. For that matter, I use cron daily and I still like the task scheduler UI on Windows. – Craig Ringer Aug 20 '15 at 13:09
1

The best way to copy a result set into a file is through COPY.

i.e.:

COPY (SELECT ...) TO 'filename.csv';

To load the data, you can use the FROM clause. This command is Operating System agnostic, as it is a plain formatted text file.

| improve this answer | |
  • 1
    But that will create the file on the server not on the client. – a_horse_with_no_name Aug 19 '15 at 22:53
  • 2
    @a_horse_with_no_name We really need a prominent note in the COPY docs that points people at psql's \copy command. It's non-obvious to most people (understandably) and not very discoverable. I should send a patch... – Craig Ringer Aug 20 '15 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.