The need to set schema using command line options
I was looking at ways to set the
search_path in psql using command line options. The reason being that
I had a bunch of auto-generated sql scripts that I wanted to run for
a specific schema in Postgresql. Turns out that there is no out of
the box option to set the schema from command line options. However,
it is fairly straightforward to add the option.
Verifying the default schema
By default, when a user logs into psql,
the search_path includes the public and $user schema. This can be
verified by entering the show search_path command on the psql
terminal as shown below.
=> show search_path;
To change the default schema from the
psql command line, the first step is to edit the .psqlrc file on your
machine.
Editing the ~/.psqlrc file
Postgresql's psql file allows users to
extend the behaviour of the psql tool. Open a terminal window and
type in the following commands.
$ sudo gedit ~/.psqlrc
This will open the existing (or new)
~./psqlrc file on your machine for editing.
Add the following line to the file.
SET search_path TO :schema;
This exposes search_path as a variable
that can be set from the command line. Now, specifying a schema as a
parameter sets the schema(search_path) to the specified value, as
shown below....
In the following statement, nystaging
is the name of the schema I want to set the value to.
$ psql -U nydot -d nywarehouse -v
schema=nystaging
This changes the default schema to
nystaging. This can be verified again by entering the show
search_path command, as shown below....
=> show search_path;
To revert to the default path, you can
specify the default path as a command line parameter, or comment the
line in ~./psqlrc file.
That's it.
