如何从另一个脚本运行postgres sql脚本？ [英] How to run postgres sql script from another script?

问题描述

我有一堆在数据库中创建表的SQL脚本。每个表都位于单独的文件中，因此编辑起来更加容易。

I have a bunch of SQL scripts that create tables in the database. Each table is located in a separate file so that editing them is much easier.

我想准备一个SQL脚本，该脚本将创建完整的架构，创建表，插入测试数据并为表生成序列。

I wanted to prepare a single SQL script that will create the full schema, create tables, insert test data and generate sequences for the tables.

我能够对oracle数据库执行此操作，但是postgres出现了问题。
问题是-我不知道如何从另一个脚本运行表创建脚本。

I was able to do such thing for oracle database but I am having problems with postgres. The thing is - I do not know how to run the table creating script from another script.

在oracle中，我使用以下语法进行操作：

In oracle I do it using the following syntax:

@@'path of the script related to the path of the currently running sql file'

一切都像魅力一样。

在postgres中，我试图搜索某些东西相似，并发现以下内容：

In postgres I was trying to search for something alike and found this:

\ir 'relative path to the file'

不幸的是，当我运行主脚本时，收到消息：

Unfortunately when I run my main script I get the message:

No such file or directory.

示例调用在此处：

\ir './tables/map_user_groups.sql'

我使用Postgres 9.3。我尝试使用psql运行脚本：

I use Postgres 9.3. I tried to run the script using psql:

psql -U postgres -h localhost -d postgres < "path to my main sql file"

除了调用那些其他脚本之外，该文件执行得很好。

The file executes fine except for the calling of those other scripts.

有人知道如何解决该问题吗？

Does anybody know how to solve the problem ?

如果问题不清楚-请让我知道：）

If something in the question is unclear - just let me know :)

推荐答案

基于答案可以在PostgreSQL上引用SQL脚本中的另一个SQL文件，只需使用 \i 语法即可包含另一个SQL文件。我刚刚测试过，并且在PostgreSQL 9.6上运行良好：

Based on the answer It is possible to reference another SQL file from SQL script, on PostgreSQL, you can include another SQL's files just using the \i syntax. I just tested and is working good on PostgreSQL 9.6:

\i other_script.sql
SELECT * FROM table_1;
SELECT * FROM table_2;

通过@wildplasser评论：

By the @wildplasser comment:

psql -U postgres -h localhost -d postgres < "path to my main sql file"

从psql的角度来看， main_sql 文件只是标准输入，标准输入没有文件名。使用 -f文件名提交具有以下名称的文件：

From psql's perspective the main_sql file is just stdin, and stdin has no "filename". Use -f filename to submit a file with a name:

psql -U postgres -h localhost -d postgres -f filename.sql

如何从另一个脚本运行postgres sql脚本？

How to run postgres sql script from another script?

似乎与以下问题相同：如何从PostgreSQL中的sql脚本导入外部sql脚本？

Seems the same question as: How to import external sql scripts from a sql script in PostgreSQL?

这篇关于如何从另一个脚本运行postgres sql脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！