如何指定模式以在 Postgresql 命令行中运行 sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13770944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I specify the schema to run an sql file against in the Postgresql command line
提问by benstpierre
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?
唯一的问题是我希望能够在此命令中指定运行脚本的架构。我可以调用 set search_path='my_schema_01' 但文件应该是可移植的。我怎样才能做到这一点？
回答by a_horse_with_no_name
You can create one file that contains the set schema ...statement and then include the actual file you want to run:
您可以创建一个包含set schema ...语句的文件，然后包含要运行的实际文件：
Create a file run_insert.sql:
创建一个文件run_insert.sql：
set schema 'my_schema_01';
\i myInsertFile.sql
Then call this using:
然后使用以下方法调用它：
psql -d myDataBase -a -f run_insert.sql
回答by Nux
More universal way is to set search_path (should work in PostgreSQL 7.x and above):
更通用的方法是设置 search_path（应该在 PostgreSQL 7.x 及更高版本中工作）：
SET search_path TO myschema;
Note that set schema myschemais an alias to above command that is notavailable in 8.x.
请注意，这set schema myschema是上述命令的别名，在 8.x 中不可用。
See also: http://www.postgresql.org/docs/9.3/static/ddl-schemas.html
另见：http: //www.postgresql.org/docs/9.3/static/ddl-schemas.html
回答by Marwan Alsabbagh
Main Example
主要示例
The example below will run myfile.sqlon database mydatabaseusing schema myschema.
下面的示例将使用架构myschema在数据库mydatabase上运行myfile.sql。
psql "dbname=mydatabase options=--search_path=myschema" -a -f myfile.sql
The way this works is the first argument to the psqlcommand is the dbnameargument. The docs mention a connection stringcan be provided.
它的工作方式是psql命令的第一个参数是dbname参数。文档提到可以提供连接字符串。
If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string
如果此参数包含 = 符号或以有效的 URI 前缀（postgresql:// 或 postgres://）开头，则将其视为 conninfo 字符串
The dbname keywordspecifies the database to connect to and the options keywordlets 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.
该DBNAME关键字指定连接到数据库和关键字选项可以让你指定命令行选项，以在连接启动发送到服务器。这些选项在服务器配置一章中有详细说明。我们用来选择模式的选项是search_path。
Another Example
另一个例子
The example below will connect to host myhoston database mydatabaseusing schema myschema. The =special character must be url escaped with the escape sequence %3D.
下面的示例将使用模式myschema连接到数据库mydatabase上的主机myhost。该特殊字符必须是网址转义序列逃脱。=%3D
psql postgres://myuser@myhost?options=--search_path%3Dmyschema
回答by Daniel Vérité
The PGOPTIONSenvironment variable may be used to achieve this in a flexible way.
该PGOPTIONS环境变量可以用来以灵活的方式来实现这一目标。
In an Unix shell:
在 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 PGOPTIONSonly once and export it.
如果脚本或子 shell 中有多个调用需要相同的选项，只需设置PGOPTIONS一次并导出它会更简单。
PGOPTIONS="--search_path=my_schema_01"
export PGOPTIONS
psql -d somebase
psql -d someotherbase
...
or invoke the top-level shell script with PGOPTIONSset from the outside
或PGOPTIONS从外部使用set调用顶级 shell 脚本
PGOPTIONS="--search_path=my_schema_01" ./my-upgrade-script.sh
In Windows CMD environment, set PGOPTIONS=valueshould work the same.
在 Windows CMD 环境中，set PGOPTIONS=value应该是一样的。
回答by Hendy Irawan
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.
注意：仅适用于 Linux/Mac/Bash，尽管在 Windows/PowerShell 中也可能有一种方法可以做到这一点。
回答by Matias Thayer
This works for me:
这对我有用：
psql postgresql://myuser:password@myhost/my_schema_01 -f myInsertFile.sql
回答by Kartones
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.
我在尝试对中间模式进行一些 dat 导入时遇到了类似的问题（稍后我们将转到最后一个模式）。由于我们依赖于扩展（例如 PostGIS）之类的东西，“run_insert”sql 文件并没有完全解决问题。
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:
一段时间后，我们发现至少在 Postgres 9.3 中，解决方案要容易得多……只需创建 SQL 脚本，在引用表时始终指定架构：
CREATE TABLE "my_schema"."my_table" (...);
COPY "my_schema"."my_table" (...) FROM stdin;
CREATE TABLE "my_schema"."my_table" (...);
COPY "my_schema"."my_table" (...) FROM stdin;
This way using psql -f xxxxxworks perfectly, and you don't need to change search_paths nor use intermediate files (and won't hit extension schema problems).
这种使用psql -f xxxxx方式非常有效，您不需要更改 search_paths 或使用中间文件（并且不会遇到扩展架构问题）。
