Execute .sql file from command line in postgresql

Hi all,

Every now and then we have/want to run a .sql file or sql script from command line instead of typing each and every sql commands. So, I will write how to execute .sql file from command line in postgresql today. See this howto to see how to install postgresql in ubuntu.

Lets see how to do it:

We can execute the .sql file in two ways from command line(mention in the comment part if you know others).

In the following example commands, simulator is the database name, qs.sql is the .sql file/script with SQL commands, qs.sql file is located at Desktop and present working directory is /home/manohar i.e my home directory.

  1. From the ubuntu/linux command prompt:

psql -d simulator -f Desktop/qs.sql

Or

psql -U <database_username> -d simulator -f Desktop/qs.sql

From ubuntu command line

From ubuntu command line

  1. From the psql command prompt:

\i Desktop/qs.sql

From sql prompt

From sql prompt

 

 

Hope this helps you all.

Regards,
Manohar Bhattarai (मनोहर भट्टराई)
Personal Website : http://www.manoharbhattarai.com.np/
Personal Blog : http://blog.manoharbhattarai.com.np/

I am Manohar Bhattarai, a vivid java programmer. I am a keen Linux user. I currently boot Ubuntu Linux. I am a Free/Libre Open Source Software enthusiast.

Posted in Linux, Postgres, Ubuntu Tagged with: , , , , , , , , , , , , , , ,
11 comments on “Execute .sql file from command line in postgresql
  1. Thank you. You’ve helped me a lot!

  2. Jaminyah says:

    Used for postgresql-9.3. Worked as explained. Perhaps a sample instruction on adding tables this way then checking that the tables were added would also be helpful. Thanks much.

  3. Jaminyah says:

    simulator=# \dt

    Gives a list of tables just created on running a script such as
    simulator=# \i /path/to/script/create_tables-simulator.sql

  4. aseew says:

    This is awesome. Thanks much.

  5. praveesh says:

    Thank you very much Manohar.
    And one help,
    Is there any way to run the set of .sql file contained in a directory by specifying the directory path?

  6. jayendran says:

    hi, im getting below error,pls advise

    sangamam=# \i dbdump/sangamam_17.05.2014.sql
    dbdump/sangamam_17.05.2014.sql: Permission denied

  7. jayendran says:

    hi , it works now, thanks

  8. Ankit says:

    I got the same error (permission denied). How did you resolve it Jayendran ?

  9. murali krishna says:

    psql: FATAL: database “simulator” does not exist
    i get this error on running

  10. Syed Jani says:

    Thank you Manohar, from few days i am getting problem to execute sql file in ubuntu. Your answer solved my problem. Thank you very much once again.

Leave a Reply