How to execute *.sql file using psql

Question

I have created an index.sql file which contains index creating script for 95 table

for example

DROP INDEX IF EXISTS gtab03_vrctrlid_idx cascade;
CREATE UNIQUE INDEX gtab03_vrctrlid_idx ON gtab03 USING btree (vrctrlid);

I have consolidated all table's index creating script to a file called index.sql I need to run the entire script at a time, is it possible to execute the index.sql file using psql

Please use the manual when you need it: postgresql.org/docs/current/static/app-psql.html — Frank Heikens
– Frank Heikens, Commented Jun 2, 2014 at 10:01
You might want to consider using a PL/PgSQL function to do this dynamically based on a query against information_schema and a loop over EXECUTE format(...), rather than writing out all the statements manually. Search dba.se for examples. — Craig Ringer
– Craig Ringer, Commented Jun 2, 2014 at 11:03

Sue Mynott · Accepted Answer · 2022-02-21 03:22:54Z

55

Is this what you mean?

\i e:/myFolder/index.sql;

edited Feb 21, 2022 at 3:22

Sue Mynott

1,3271 gold badge10 silver badges14 bronze badges

answered Jun 2, 2014 at 10:04

Sney

5793 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anthony Petrillo Over a year ago

; should not be included because this is a psql command and not SQL.

Matt Over a year ago

That doesn't work for me - parameter \i is not known in psql.

Vivek S. · Accepted Answer · 2020-01-12 11:22:07Z

43

Following script worked for me,

psql -U postgres -d mydb -a -f "D:\index.sql" -- Absolute path to .sql file

-U, -a, -f

edited Jan 12, 2020 at 11:22

answered Jun 2, 2014 at 10:07

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

2 Comments

vaughan Over a year ago

Obviously...but for the good of the community it is easier if they can run the command as it is provided, without having to go digging into the reference to check what this non-obvious flag does. I see you added it, good on you.

Matt Over a year ago

You can also "pipe" it, e.g. psql -U postgres -d mydb < D:\index.sql

Collectives™ on Stack Overflow

How to execute *.sql file using psql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related