I am using PostgreSQL 8.4, and I have some *.sql files to import into a database. How can I do so?
Add a comment
|
6 Answers
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
6 Comments
Bolo
@moon Maybe you don't have access rights to the SQL files? What operating system are you on?
Bolo
@moon It's the password associated with your PostgreSQL user (the user-pass pair is stored in PostgreSQL).
Bolo
@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.
A.H.
@Bolo: Would you put the three stages into the answer?
|
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.
3 Comments
swdev
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?
shevy
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.
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.
Comments
Be careful with "/" and "\". Even on Windows the command should be in the form:
\i c:/1.sql
Comments
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.
Comments
use following command :-
C:\Program Files\PostgreSQL\12\bin>psql -U username -d databasename -f D:\file.sql
Comments
