How to run postgres sql script from another script?

Question

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.

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.

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.

In oracle I do it using the following syntax:

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

And everything works like a charm.

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.

The example call is here:

\ir './tables/map_user_groups.sql'

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 :)

In which operating system are you attempting to accomplish this? — rchang, Jan 14 '15 at 11:27
`psql -U postgres -h localhost -d postgres < "path to my main sql file"` : from psql's perspective the main_sql file is just stdin. stdin has no "filename"; use `-f filename` to submit a file **with** a name. — wildplasser, Jan 14 '15 at 11:34
Thank you @wildplasser, that was it :) rchang I do it on Windows 7 Professional — Marcin Roguski, Jan 14 '15 at 12:40
@wildplasser Might I suggest you post this as an answer? It helped me just now. — Tripp Kinetics, Oct 22 '15 at 19:52
The problem is: for unix people this solution is so trivial that is is embarassing to post as an answer... — wildplasser, Oct 22 '15 at 20:03

Evandro Coan · Accepted Answer · 2020-07-30T23:19:20.217

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;

By the @wildplasser comment:

psql -U postgres -h localhost -d postgres < "path to my main sql file"
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

How to run postgres sql script from another script?

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

is there a way to do it from PgAdmin4 or only if I am running it from the console? — Andrey, Dec 01 '21 at 02:40

How to run postgres sql script from another script?

1 Answers1

Linked