NickName:takeshin Ask DateTime:2010-05-13T21:20:57

SQL: convert backup file from copy format to insert format

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.

Copyright Notice：Content Author:「takeshin」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/2827072/sql-convert-backup-file-from-copy-format-to-insert-format

Answers

Steffen 2013-09-20T07:25:24

I don't know a way or a tool which can convert \"COPY\" into \"INSERT\" statements.\n\nBut 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).\n\nEDIT:\n\nWhat you can do is:\n\n\nCreate a new postgres database on your local machine\nImport your DB dump file into the new created database\n\npsql -U <dbuser> <database> < dump.sql\n\nAnd create a postgres dump with \"--column-inserts\" from that database\n\npg_dump --column-inserts -U <dbuser> <database> > dumpWithInserts.sql\n\n",

fredz 2017-01-16T13:19:03

I've wrote a tool to convert copy-from dump to inserts dump :\n\nhttps://github.com/freddez/pg-dump2insert\n\nYou can use it to load a dump in another database or search for specific deleted values for example.",

leonbloy 2010-05-13T16:31:22

You can't convert that to SQL format (at least not straightforwardly).\n\nIf you can't re export, then the simpler option is to bypass phpPgadmin,\nlogin in your server and run something like \n\ncat [yourdump.sql] | psql [your connections args]\n\n\nIf 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.\n\nhttp://phppgadmin.sourceforge.net/?page=faq",

Dmitry Ziyatdinov 2015-05-26T16:12:34

You can try COPY FROM PROGRAM syntax, available since PostgreSQL 9.3\n\nFor example:\nCOPY tbl_customers (id, address_id, insurance_id, fullname, secret_code ...) FROM PROGRAM 'echo \"1 2 \\N Ivan Ivanov 42 ...\"'",