スタック・オーバーフローに参加する
687万人以上のプログラマーが集まるスタック・オーバーフローに参加しませんか？
簡単な登録後、すぐにご利用いただけます。
登録
up vote 78 down vote favorite
29

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

share|improve this question
up vote 116 down vote

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

share|improve this answer
    
its giving an error the "permission denied" – Badr uz Zaman Aug 3 '10 at 6:46
    
@moon Maybe you don't have access rights to the SQL files? What operating system are you on? – Bolo Aug 3 '10 at 7:00
    
it asks for a password which password i have to write in??? – Badr uz Zaman Aug 3 '10 at 7:21
    
i am using windows XP sp 2 – Badr uz Zaman Aug 3 '10 at 7:22
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 3 '10 at 9:35
up vote 64 down vote accepted

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.

share|improve this answer
    
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 7: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. – user2403573 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 6 '14 at 12:26
up vote 20 down vote

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

\i c:/1.sql
share|improve this answer
up vote 20 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.