Setting the schema (search_path) in psql using command line options

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.