Email Postgres Query Output

Question

I would like to send out a weekly email to two or three users containing the output of a select statement, run against Postgres. Very simple select all statement to show the number of users in the system.

I am able to query the database via command line and see the result, but not email those results to someone. Is there a script that can be used for this task or a better approach? This is the code I ran to test connection to the database and see if I could pull data from it. Using PostgreSQL 9.1

This solution is for Windows Server 2008.

What OS are you using via command line to run the above script? — FirebladeDan
– FirebladeDan, Commented Aug 4, 2015 at 18:02
I tested on Centos, but the ultimate goal is to use Windows Server 2008 — 530529
– 530529, Commented Aug 4, 2015 at 18:12

Bobby StJacques · Accepted Answer · 2015-08-04 18:22:38Z

2

Not sure what OS you're using, but on Ubuntu I wrote a modified version of your script and piped the output to mail (which, of course, requires that mailutils be installed):

psql -U username -d databasename -c 'SELECT column1, column2 FROM table' | mail -s "query results" [email protected]

This worked just fine for me.

answered Aug 4, 2015 at 18:22

Bobby StJacques

7345 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

530529 Over a year ago

Hi Bobby, thanks for the reply. I will use your code to test. I started playing with unix, but my end goal is to have this solution on windows server, where the prod database resides.

Bobby StJacques Over a year ago

The trick on Windows will be having a mail client that you can execute from a script. There are many to choose from. Blat seems to be popular.

Collectives™ on Stack Overflow

Email Postgres Query Output

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related