Load sql file data in to single table in postgres

Question

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)

Possible duplicate of import sql dump into postgresql database — Don
– Don, Commented Apr 11, 2017 at 21:18

hage · Accepted Answer · 2014-01-23 12:19:03Z

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

edited Jan 23, 2014 at 12:19

answered Jan 23, 2014 at 12:13

hage

6,1933 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shiva Krishna Bavandla Over a year ago

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 Over a year ago

Oh k there is no need to mention the table name at all ? just the database, username is enough ?

hage Over a year ago

Jep, the table name is given in the insert statements.

hage Over a year ago

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

Rohit Bhati · Accepted Answer · 2021-01-08 06:18:28Z

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

awaik · Accepted Answer · 2016-12-22 16:54:20Z

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

answered Dec 22, 2016 at 16:54

awaik

12.6k3 gold badges55 silver badges59 bronze badges

Collectives™ on Stack Overflow

Load sql file data in to single table in postgres

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related