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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This works for me  psql postgresql   myuser password myhost my db -f myInsertFile sql

User · Answer

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

User · Answer

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

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

Examples related to postgresql