5

I have a dump file from a Postgresql db. However, If i run psql -U user < db.sql under Windows, i get

 ERROR:  missing data for column "x"

for certain lines. Thing is, the data for "x" is not missing. I'm guessing there are some characters in the column preceding "x" that somehow corrupt the import, but i have no idea what it might be.

Any ideas? Postgre version is 8.3.7

Edit: It would be fine to omit the offending lines, if restoring process could continue gracefully after encountering the line.

Edit2: It appears the culprit is the Ctrl-Z chracters (ascii 26) in the dump, which are interpreted as end-of-file under windows(?).

4 Answers 4

5

Have you tried passing the file using the -f flag instead of redirecting the input? Perhaps your console's redirection is acting weird:

psql -U user -f db.sql

Considering it's on Windows, I wouldn't be surprised if this was the case.

2
  • Good call-I forgot about the f flag
    – Josh Budde
    Oct 12, 2009 at 13:48
  • Or even better, use pg_dump with the custom format, and then pg_restore. That brings several other advantages as well, such as selective restoring. Oct 12, 2009 at 14:52
1

You shouldn't have any trouble with characters-if they were exported successfully they should import fine. Is it possible you need the schema from the old database server as well?

If you look in the dump file can you find the line that its choking on? Is there anything odd there?

2
  • It appears the culprit is the Ctrl-Z chracters (ascii 26) in the dump, which are interpreted as end-of-file under windows(?)
    – Ash
    Oct 12, 2009 at 13:49
  • Ash-did you try the -f flag that Topher suggested above? It may help.
    – Josh Budde
    Oct 12, 2009 at 18:29
1

I could not restore from .sql dump (got the same error) because my dump was not correctly completed (errors arose when I generated the dump, but somehow I didn't notice them). So always double-check if you have a valid dump file!

0

Try dumping in custom format (-Fc) and then use pg_restore to connect directly to the database to restore it.

You must log in to answer this question.