I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export

pg_dump -U username --format plain --section data --inserts --file "t1.backup" --table “table1” --table “table2” --table "addresses" “mydb”

But when I import the data using pg_restore, I get errors

pg_restore -d my_production --data-only t1.backup
… ^
Command was: INSERT INTO table2 VALUES (NULL, NULL, 1762000, '2016-12-10 05:07:41.857082', '2016-12-10 05:07:41.857207', 'Brendon Sch... pg_restore: [archiver (db)] could not execute query: ERROR: invalid input syntax for integer: "a6a40213-dcd8-4312-9ff1-ae6c23b31ade" LINE 1: ..., '8a22784f-91aa-4b1e-a3d4-ec44f5de19ff', 55, 59, 'a6a40213-...

I think this is because although table2 has the same columns on both my Mac and Linux machines, they are in different orders. How do I force INSERT statement to include the column names or barring that, how do I export/import my data successfully? Note, I don’t want to delete the data on the remote machine, just add to it.

  • 1
    A plain text dump is a SQL script. You can't use pg_restore for that. You need to "run" the script using psql
    – user1822
    Dec 28, 2016 at 16:24
  • 2
    Although @a_horse_with_no_name is already pointing the reason why pg_restore isn't working... in case you actually want 'column inserts', you can do it by means of --column-inserts
    – joanolo
    Dec 28, 2016 at 20:29
  • Yup the "--column-inserts" directive was exactly what I was looking for.
    – Dave
    Dec 28, 2016 at 21:21


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.