13

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

1
  • I would like to know if its possible invoke psql "login" with the default schema set in the very same line. I dont want to return to shell
    – Whimusical
    Apr 25 '17 at 20:17
6

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.

2
  • 1
    This is the best answer I see on this question. Thanks!
    – jtcloud
    Oct 1 '20 at 14:23
  • Totally agree with @jtcloud. Although the OP answer is also good, this one does not require bash or any other script language, and can be done directly within psql, which helped me a lot. Many thanks! Mar 22 at 18:46
21

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

6

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

$ echo 'set search_path to foo,bar' >> ~/.psqlrc
$ psql ...
2
  • Is it possible in one line? If not ill approve this one! Thanksss
    – Whimusical
    Apr 24 '17 at 19:29
  • 1
    @Whimusical you'd only need to do the .psqlrc thing one time Apr 24 '17 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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