7

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
takeshin
  • 49,108
  • 32
  • 120
  • 164

5 Answers5

9

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
Steffen
  • 2,500
  • 4
  • 31
  • 47
4

I've wrote a tool to convert copy-from dump to inserts dump :

https://github.com/freddez/pg-dump2insert

You can use it to load a dump in another database or search for specific deleted values for example.

fredz
  • 570
  • 1
  • 6
  • 8
3

You can't convert that to SQL format (at least not straightforwardly).

If you can't re export, then the simpler option is to bypass phpPgadmin, login in your server and run something like 

cat [yourdump.sql]  | psql [your connections args]

If you don't have shell access to your server, you might try you upload the file (via SFTP or FTP) and load it thorugh phpPgAdmin with "COPY <table> FROM <path_to_file_on_server>". But if there are many tables, you must (I believe) split the file and do it one at a time.

http://phppgadmin.sourceforge.net/?page=faq

leonbloy
  • 73,180
  • 20
  • 142
  • 190
2

You can try COPY FROM PROGRAM syntax, available since PostgreSQL 9.3

For example: COPY tbl_customers (id, address_id, insurance_id, fullname, secret_code ...) FROM PROGRAM 'echo "1 2 \N Ivan Ivanov 42 ..."'

Dmitry Ziyatdinov
  • 95
  • 2
  • 4
0

To add on Steffen answer, it is nice to also add --rows-per-insert (added to pg_dump for v12) if you want to have one big insert for all your values :

pg_dump --column-inserts --rows-per-insert=2147483647 \
        -U <dbuser> <database> > dumpWithOneInsert.sql

(inspired by https://stackoverflow.com/a/60621069/19888529)

kzkb
  • 26
  • 3