118

I am using PostgreSQL 8.4, and I have some *.sql files to import into a database. How can I do so?

informatik01
  • 16,038
  • 10
  • 74
  • 104
Badr
  • 10,384
  • 15
  • 70
  • 104

6 Answers6

158

From the command line:

psql -f 1.sql
psql -f 2.sql

From the psql prompt:

\i 1.sql
\i 2.sql

Note that you may need to import the files in a specific order (for example: data definition before data manipulation). If you've got bash shell (GNU/Linux, Mac OS X, Cygwin) and the files may be imported in the alphabetical order, you may use this command:

for f in *.sql ; do psql -f $f ; done

Here's the documentation of the psql application (thanks, Frank): http://www.postgresql.org/docs/current/static/app-psql.html

Bolo
  • 11,542
  • 7
  • 41
  • 60
  • @moon Maybe you don't have access rights to the SQL files? What operating system are you on? – Bolo Aug 03 '10 at 07:00
  • @moon It's the password associated with your PostgreSQL user (the user-pass pair is stored in PostgreSQL). – Bolo Aug 03 '10 at 09:12
  • 8
    @moon I suggest that you split your problem into three stages: 1) make sure that you can get `psql` running. 2) make sure your user has the necessary write privileges, such as: `CREATE`, `INSERT`, `UPDATE`, etc. 3) import the SQL files. As far as I understand, you're at stage 1 now. – Bolo Aug 03 '10 at 09:35
  • @Bolo: Would you put the three stages into the answer? – A.H. Sep 22 '11 at 13:53
  • @Bolo , As you mentioned I'm now in third steps. but how I can import .sql file from my local machine. I tried \i [full path with extension] but I get the error message .sql no such file or directory. Could you please give an example. –  Jun 29 '13 at 11:47
  • Okay, it`s working. How to update the new version of file to the same database? So I have changed the first version of file and now I would like to make this changes also in database from file. Is it possible to do it exaclty the same way? psql -f 1.sql e.g. 1.sql has the same name but include changes inside. – andrew Feb 23 '14 at 18:48
84

in command line first reach the directory where psql is present then write commands like this:

psql [database name] [username]

and then press enter psql asks for password give the user password:

then write

> \i [full path and file name with extension]

then press enter insertion done.

Badr
  • 10,384
  • 15
  • 70
  • 104
  • I use this too. And it works. But, I change the supplied SQL statements. Previously the existing *.sql didn't use any character ; as the termination of a line. And I also must remove the GO. Do you t hink the sql script is not a psql script? – swdev Feb 27 '12 at 07:02
  • I tried to use \i [full path with extension] but I get the error message .sql no such file or directory. Could you please upload an example. Thanks. –  Jun 29 '13 at 10:54
  • If \i says no such file then it did not find a file at that position. It is best to give the absolute URL. For me on Windows, this commandline worked: \i /tmp/robert/test.sql of course you must have valid SQL commands in that file. – shevy Feb 06 '14 at 12:26
40

Well, the shortest way I know of, is following:

psql -U {user_name} -d {database_name} -f {file_path} -h {host_name}

database_name: Which database should you insert your file data in.

file_path: Absolute path to the file through which you want to perform the importing.

host_name: The name of the host. For development purposes, it is mostly localhost.

Upon entering this command in console, you will be prompted to enter your password.

Arslan Ali
  • 17,418
  • 8
  • 58
  • 76
27

Be careful with "/" and "\". Even on Windows the command should be in the form:

\i c:/1.sql
informatik01
  • 16,038
  • 10
  • 74
  • 104
sofiane
  • 271
  • 3
  • 2
1

Always preferred using a connection service file (lookup/google 'psql connection service file')

Then simply:

psql service={yourservicename} < {myfile.sql}

Where yourservicename is a section name from the service file.

ruvim
  • 7,151
  • 2
  • 27
  • 36
0

enter image description here

use following command :-

C:\Program Files\PostgreSQL\12\bin>psql -U username -d databasename -f D:\file.sql