Question: How do I executed PostgreSQL Commands inside a Linux / UNIX shell script?
Answer: With the help of the psql interactive terminal, you can execute the psql commands from the shell script. For this purpose, you should enable the password less login by pg_hba.conf, or .pgpass.
Syntax
psql DBNAME USERNAME << EOF
statement 1;
statement 2;
.
.
statement n;
EOF
PostgreSQL: Executing SQL from shell scripts
Provide all the postgreSQL commands between the EOF block as shown below.
#!/bin/sh dbname="test" username="test" psql $dbname $username << EOF SELECT * FROM test; EOF
PostgreSQL: Using variables in SQL from shell scripts
You can also use shell script variable inside the EOF block as shown below.
#!/bin/sh dbname="test" username="test" wherecond="tgs" psql $dbname $username << EOF SELECT * FROM test WHERE col_name = '$wherecond'; EOF
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
What about PGPASSWORD ?
One more way:
psql -U -c “select * from test”;
You can take advantage of environment variables used by
http://www.postgresql.org/docs/9.1/static/libpq-envars.html , eg, PGDATABASE , PGUSER, PGPASSWORD etc.
Then you don’t need to provide psql parameters. It is useful if you have to run psql multiple times:
export PGDATABASE=test
export PGUSER=test
wherecond=”tgs”
psql << EOF
SELECT * FROM test WHERE col_name = '$wherecond';
EOF
That way the queue is printed in terminal window. How can it be “exported” to CSV (for example) ?
psql << EOF
\copy (select * from table) to '/tmp/file.csv';
EOF
i am getting errors when using a bash variable inside the EOF any idea what might be wrong?
psql $db << EOF
\copy '$tbl' FROM '/Users/some/file/path.csv' DELIMITER ',' CSV HEADER;
EOF
I am trying the following way, but its not working… any suggestion please ?
LD_LIBRARY_PATH=/opt/app/apphome/AAPWebInfrastructurePlatformDataServer/9.4/lib
export LD_LIBRARY_PATH
PGPASSWORD=ZZZ
export PGPASSWORD
/opt/app/apphome/AAPWebInfrastructurePlatformDataServer/9.4/bin/psql -U USR -h localhost -p 9432 -d XXX
<<EOF
select * from baseview where object_type_id=106 and user_id=’USR’;
EOF