SQL: convert backup file from copy format to insert format

Question

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 1

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