Question

loading data onto db in cloud fails in one table

1

Hi, anyone can help with this issue on production environment? I have managed to load data from local postgresDB to cloud one using plain text backup file through psql console, but one particular table out of over 100 keeps failing, even when entering it by itself. All data for the table is fully viewed locally (over 30,000 records), but just not able to loaded it onto server. Fields are just like in any other table, nothing special or extraordinary, that's why just don't understand why only this table is not able to get the data loaded... short extract below: psql:data/database/freedom_plain_addresses.sql:12637: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.039" psql:data/database/freedom_plain_addresses.sql:12638: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.101" psql:data/database/freedom_plain_addresses.sql:12639: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.179" psql:data/database/freedom_plain_addresses.sql:12640: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.273" psql:data/database/freedom_plain_addresses.sql:12641: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.351" psql:data/database/freedom_plain_addresses.sql:12642: ERROR: invalid input syntax for integer: "2011-07-12 07:36:18.569"

asked 2011-07-12

Luis Rodrigues (hotmail)

3 answers

Bart Tolen · Answer 1 · 2011-07-12

I asume 12637 is a row in a sql file. Can you compare the rows mentioned to rows above of below. Maybe there is an empty column or there are issues with escaping character in strings.

And maybe you can delete the 5 rows out of 30000 and re-create them manually?

Sebastiaan van den Broek · Answer 2 · 2011-07-12

You're trying to insert someting into an integer field that is obviously not an integer.

psql:data/database/freedom_plain_addresses.sql:12642: ERROR:  invalid input syntax for integer: "2011-07-12 07:36:18.569"

Looks like a date in the form of a string, or a string containing a date, either way it's not gonna fit into an integer field.

Luis Rodrigues (hotmail) · Answer 3 · 2011-07-14

THE FIX! In order to fix the issue I have installed version 8.3.13 of postgres locally to match version on cloud. Build app to new postgres version db and I produced backup with compressed format. Restoredb kept saying it's unsupported version, even though it was produced from the 8.3.13 version, so maybe I need to get rid of my local 9.0.4 version as well just to make sure... don't know. I have then created a plain text backup from the 8.3.13 version(20 times bigger file) and used psql to load the data onto db. That seemed to be ok, just a few index errors but only beacuse they were already there when db was built. I can see most data ok in app, although have not tested every single piece of data, but so far so good. LR.