How to set a search_path default on a psql cmd execution?

Question

I'm looking for an option to create an alias command for a psql console connection which allowed setting the search_path, but I am not finding any option on psql util, nor the option to execute a command without exiting. any idea?

I would like to avoid preconditions as setting environment options and have a oneliner if possible

Answer 1

You can specify a PGOPTIONS-style connstring as an argument to psql instead of using the -d/-h/-U/-p/etc options. For your example:

psql 'host=myHost user=myUser dbname=myDb port=myPort options=--search_path=myschema'

or, if you prefer the URI style,

psql 'postgresql://myUser@myHost:myPort/myDb?options=--search_path%3dmyschema'

See the "Connection strings" section of the manual for details.

Answer 2

I finally found it, its not a psql option, but it fits a oneliner: PGOPTIONS=--search_path=myschema psql -h myHost -U myUser -dmyDb -p myPort.

If anybody improves it with a working version call for both unix and windows ill approve

Answer 3

You could use the .psqlrc file, which is run at psql startup:

$ echo 'set search_path to foo,bar' >> ~/.psqlrc
$ psql ...

Answer 4

one liner for Windows:

set PGOPTIONS=--search_path=MySchema& set PGPASSWORD=MyPassword& psql.exe --host=MyHost -d MyDatabase -U MyUser