How can I specify the schema to run an sql file against in the Postgresql command line

Solution 1:

You can create one file that contains the set schema ... statement and then include the actual file you want to run:

Create a file run_insert.sql:

set schema 'my_schema_01';
\i myInsertFile.sql

Then call this using:

psql -d myDataBase -a -f run_insert.sql

Solution 2:

More universal way is to set search_path (should work in PostgreSQL 7.x and above):

SET search_path TO myschema;

Note that set schema myschema is an alias to above command that is not available in 8.x.

See also: http://www.postgresql.org/docs/9.3/static/ddl-schemas.html

Solution 3:

Main Example

The example below will run myfile.sql on database mydatabase using schema myschema.

psql "dbname=mydatabase options=--search_path=myschema" -a -f myfile.sql

The way this works is the first argument to the psql command is the dbname argument. The docs mention a connection string can be provided.

If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string

The dbname keyword specifies the database to connect to and the options keyword lets you specify command-line options to send to the server at connection startup. Those options are detailed in the server configuration chapter. The option we are using to select the schema is search_path.

Another Example

The example below will connect to host myhost on database mydatabase using schema myschema. The = special character must be url escaped with the escape sequence %3D.

psql postgres://myuser@myhost?options=--search_path%3Dmyschema

Solution 4:

The PGOPTIONS environment variable may be used to achieve this in a flexible way.

In an Unix shell:

PGOPTIONS="--search_path=my_schema_01" psql -d myDataBase -a -f myInsertFile.sql

If there are several invocations in the script or sub-shells that need the same options, it's simpler to set PGOPTIONS only once and export it.

PGOPTIONS="--search_path=my_schema_01"
export PGOPTIONS

psql -d somebase
psql -d someotherbase
...

or invoke the top-level shell script with PGOPTIONS set from the outside

PGOPTIONS="--search_path=my_schema_01"  ./my-upgrade-script.sh

In Windows CMD environment, set PGOPTIONS=value should work the same.

Solution 5:

I'm using something like this and works very well:* :-)

(echo "set schema 'acme';" ; \
  cat ~/git/soluvas-framework/schedule/src/main/resources/org/soluvas/schedule/tables_postgres.sql) \
  | psql -Upostgres -hlocalhost quikdo_app_dev

Note: Linux/Mac/Bash only, though probably there's a way to do that in Windows/PowerShell too.