5

I have sql file(single_table_data.sql) that contains data of only one table(I have taken dump of only one table from server)

Now i have to insert this sql file in to only single table in my database,

so how to import sql file in to single table in postgres ?

Edit

For example i had database name SpeedData and table name CurrentTable, so now i want to insert entire sql file data in to this table CurrentTable

Note: The sql file contains only insert statements(not even create statements)

Shiva Krishna Bavandla
  • 25,548
  • 75
  • 193
  • 313
  • Possible duplicate of [import sql dump into postgresql database](http://stackoverflow.com/questions/6842393/import-sql-dump-into-postgresql-database) – Don Apr 11 '17 at 21:18

3 Answers3

17

From the documentation:

psql dbname < infile

This should create a new table with the name of the previously dumped one and insert all data into it. Replace dbname with the name of the new database and infile with the name/path of the file containing the dump, in your case (single_table_data.sql)

If the dump contains only the insert statements, create the table by hand and then execute psql -U your_username -d dbname -f single_table_data.sql

hage
  • 5,966
  • 3
  • 32
  • 42
  • I have only insert statements inside the sql file and not even the table create statement, so how can the above command knows to which table the data is to be inserted ? I have edited my question, please have a look – Shiva Krishna Bavandla Jan 23 '14 at 12:15
  • Oh k there is no need to mention the table name at all ? just the database, username is enough ? – Shiva Krishna Bavandla Jan 23 '14 at 12:19
  • Jep, the table name is given in the insert statements. – hage Jan 23 '14 at 12:21
  • The problem is, if the table referenced in the dump file is not the same as you created, the import will fail. But you can rename tables with `ALTER TABLE name RENAME TO new_name` – hage Jan 23 '14 at 12:23
3

You can simply import sql dump data into your postgres db.

if you have already created DB then don't need to follow 1st step:-

STEP=1

open terminal then run following commands to create postgres database and user:-

sudo -u postgres psql

postgres=# create database mydb;

postgres=# create user myuser with encrypted password 'mypass';

postgres=# grant all privileges on database mydb to myuser;

STEP=2

\c used for selecting your database.

postgres=# \c yourdatabasename

\i used for importing dump data in database.

yourdatabasename=# \i path_of_your_dump_file for example:-

yourdatabasename=# \i /home/developer/projects/django_projects/db_dump.sql

If you face this type of error when you importing data:-

ERROR: role "yourusername" does not exist

so you can make superuser to your "db_user/yourusername" using this command:-

postgres=# ALTER USER fusion WITH SUPERUSER;

ALTER ROLE

Rohit Bhati
  • 57
  • 6
2

If I understood correctly you want to create table from file and fill in with data. Correct command is

PGPASSWORD=<password> psql -f /home/.../filename.sql -h localhost -d database_name -U user_name
awaik
  • 10,143
  • 2
  • 44
  • 50