How do I execute a query automatically in PostgreSQL when connecting via shell?

Question

I want to be able to execute a statement automatically when I connect to Postgres with psql and then remain connected so I can type in further commands.

Currently, every time I connect, the first thing I do is type:

SET search_path = 'something';

Or maybe I would want to do something else like:

SELECT COUNT(*) FROM sometable;

I know there is a -c argument to psql that will execute a command and then exit. I'm looking for a way I can execute a command upon connecting and then remain in the client.

(Note: I prefer not to alter the database, schema or role to make the search_path permanent, as all of the solutions I have found seem to dictate. I want to set it every time I connect. But again, this question could apply any SQL statement.)

I have tried this:

echo "SET search_path TO 'mything'" | psql

but that behaves the same way as:

psql -c "SET search_path TO 'mything'"

Is what I'm asking for doable?

Grant Gainey · Accepted Answer · 2015-10-22 18:15:37Z

6

psql will look for, and execute any commands found in, a couple of places every time it starts up. One is the system-wide psqlrc file, and one is in the home-directory of the login that's running psql, ~/.psqlrc.

So, you could add the SET command that you always want to be run, to your .psqlrc file, and it'll get executed every time you start up. See the example below:

~ $ cat ~/.psqlrc
SET search_path='mything';
~ $ psql
SET search_path='mything';
SET
psql (8.4.20, server 9.2.10)
WARNING: psql version 8.4, server version 9.2.
     Some psql features might not work.
Type "help" for help.
rhnschema=# show search_path;
search_path 
-------------
mything
(1 row)
rhnschema=#

edited Oct 22, 2015 at 18:15

answered Oct 22, 2015 at 17:38

Grant Gainey

1245 bronze badges

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

1 Comment

Olivier De Meulder Over a year ago

Welcome to stackoveflow. Consider adding an explanation to your answer.

Collectives™ on Stack Overflow

How do I execute a query automatically in PostgreSQL when connecting via shell?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related