Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to run postgres sql script from another script?

Tags:

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

like image 601
Marcin Roguski Avatar asked Jan 14 '15 10:01

Marcin Roguski


People also ask

How do I run a SQL script in PostgreSQL?

Another easiest and most used way to run any SQL file in PostgreSQL is via its SQL shell. Open the SQL shell from the menu bar of Windows 10. Add your server name, database name where you want to import the file, the port number you are currently active on, PostgreSQL username, and password to start using SQL shell.

How do I run a DDL script in PostgreSQL?

To run DDL in the form of an SQL script, you should use psql , the same command you use to connect to the server interactively. I recommend using ON_ERROR_STOP=1 and -1 , so it runs the DDL in a single transaction and aborts on any error.

Can we import .SQL file in PostgreSQL?

To import SQL script file into PostgreSQL database using this tool, take the following steps: Select the database. Navigate to the "SQL" button. Click the "Choose File" (or "Browse") button and select the SQL file from your computer.

How do I run a PostgreSQL script in pgAdmin?

Select the relevant portion and hit the F5 key in the SQL editor of pgAdmin. OR use the "Execute query" button (green arrow) in the toolbar. If nothing is selected, the whole script is executed.


1 Answers

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?

like image 159
user Avatar answered Sep 28 '22 06:09

user