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

Question

I run scripts against my database like this...

psql -d myDataBase -a -f myInsertFile.sql 

The only problem is I want to be able to specify in this command what schema to run the script against. I could call set search_path='my_schema_01' but the files are supposed to be portable. How can I do this?

Answer 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 

Answer 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