current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I use Ruby to generate a bunch of SQL commands, and store this into a file.

I then login to my PostgreSQL database. Then I do something like:

\i /tmp/bla.sql

And this populates my database.

This all works fine as it is, no problem here.

I dislike the manual part where I have to use \i, though (because I need this to work in a cron job eventually, and I think commands like \i are only available when you are directly in the interactive psql prompt).

So my question now is:

Is it possible to use a psql command from the command line that directly will start to read in an external file?

share|improve this question

2 Answers 2

up vote 2 down vote

You can directly use the psql command as shown below. Works for me with Ubuntu and Mint. On Windows it should be quite the same...

psql -U user -d database -f filepath

Example:

psql -U postgres -d testdb -f /home/you/file.sql

For more information take a lock at the official documentation: http://www.postgresql.org/docs/current/static/app-psql.html

share|improve this answer
up vote 0 down vote

When you try to execute an sql file using cron, you will also need to set the environment - database name, password etc. This is a short shell script snippet that does it all

source /var/lib/pgsql/scripts/.pgenv
echo $PATH
psql << AAA
select current_date;
select sp_pg_myprocedure(current_date);
AAA

In .pgenv, you set the values such as 

export PGPORT=<yourport>
export PGHOST=<yourhost>
export PGDATA=<yourdatadir>

Also have a .pgpass file so that the password is supplied. http://www.postgresql.org/docs/current/static/libpq-pgpass.html Replace the part where SELECT is being done with whatever you want to do, or do it as @Kuchi has shown.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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