SQL: convert backup file from copy format to insert format

sql postgresql phppgadmin
takeshin picture takeshin · May 13, 2010 · Viewed 9.4k times · Source

I have a PostgreSQL backup made with PHPPgadmin using Export > Copy (instead Copy > SQL which is actually what I need).

File contains entries like this:

COPY tablename(id, field) FROM stdin;
...

How to convert this file to SQL format?

INSERT INTO tablename...

I want to use Pgadmin to to import this file using execute SQL command.

Answer

Steffen picture Steffen · Sep 20, 2013

I don't know a way or a tool which can convert "COPY" into "INSERT" statements.

But with the "pg_dump" command line tool you can create a DB dump file with "INSERT" instead of "COPY" statements. Simple use the "--column-inserts" argument (may be "--inserts" is also ok for you).

EDIT:

What you can do is:

  1. Create a new postgres database on your local machine

  2. Import your DB dump file into the new created database

    psql -U <dbuser> <database> < dump.sql

  3. And create a postgres dump with "--column-inserts" from that database

    pg_dump --column-inserts -U <dbuser> <database> > dumpWithInserts.sql

Related questions

Postgres sql insert query syntax error from phpPgAdmin

Trying to execute a standard insert query, but it doesn't work. INSERT INTO users (vk_id, eu_name, eu_society, eu_notes, eu_want_team) VALUES ("123123133","Eu name","Eu society","Eu notes","true") The error I get is the following: …

Read More
Transaction roll back not working in Postgresql

I am working on PostgreSQL 9.1.4 . I am inserting the data into 2 tables its working nicely. I wish to apply transaction for my tables both table exist in same DB. If my 2nd table going fail on any moment that time …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More