90

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?

benstpierre
  • 32,833
  • 51
  • 177
  • 288

8 Answers8

93

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
86

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

Nux
  • 9,276
  • 5
  • 59
  • 72
  • 2
    I prefer this answer, as you can set the search path to more than one schema - handy if you're using schemas as they are intended. – e_i_pi Jun 08 '17 at 00:18
57

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
Marwan Alsabbagh
  • 25,364
  • 9
  • 55
  • 65
  • This is the solution I was looking for, having already created large file without the schema specified. – Tregoreg May 22 '17 at 14:08
  • This is exactly what I needed - I wanted to create an alias on my machine to connect directly to a database set the search_path and this worked like a charm. – lusional Aug 29 '17 at 18:15
  • 1
    In the "Another example", you forgot to include the name of the database in the URI. It should be `psql postgres://myuser@myhost/mydatabase?options=--search_path%3Dmyschema` – Inego Dec 30 '19 at 06:21
15

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.

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
2

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.

Hendy Irawan
  • 20,498
  • 11
  • 103
  • 114
0

This works for me:

psql postgresql://myuser:password@myhost/my_db -f myInsertFile.sql
Matias Thayer
  • 571
  • 3
  • 8
  • 5
    The part `my_schema_01` after the slash is in fact the name of the database and not schema. – Inego Dec 30 '19 at 06:17
0

In my case, I wanted to add schema to a file dynamically so that whatever schema name user will provide from the cli, I will run sql file with that provided schema name. For this, I replaced some text in the sql file. First I added {{schema}} in the file like this

CREATE OR REPLACE FUNCTION {{schema}}.usp_dailygaintablereportdata(

then replace {{schema}} dynamically with user provided schema name with the help of sed command

sed -i "s/{{schema}}/$pgSchemaName/" $filename
result=$(psql -U $user -h $host -p $port -d $dbName -f "$filename" 2>&1)
sed -i "s/$pgSchemaName/{{schema}}/" $filename

First replace is done, then target file is run and then again our replace is reverted back

Muhammad Awais
  • 1,608
  • 1
  • 21
  • 21
-2

I was facing similar problems trying to do some dat import on an intermediate schema (that later we move on to the final one). As we rely on things like extensions (for example PostGIS), the "run_insert" sql file did not fully solved the problem.

After a while, we've found that at least with Postgres 9.3 the solution is far easier... just create your SQL script always specifying the schema when refering to the table:

CREATE TABLE "my_schema"."my_table" (...); COPY "my_schema"."my_table" (...) FROM stdin;

This way using psql -f xxxxx works perfectly, and you don't need to change search_paths nor use intermediate files (and won't hit extension schema problems).

Kartones
  • 40
  • 4