Thread: Can you help solve restore problem?
Hi all! Trying to restore one of my db on PG-7.4.3 (on Debian-Sarge): $ psql finanza < finanza.out I get: --------- [...] ERROR: invalid input syntax for type double precision: "-" CONTEXT: COPY dep_tit, line 1, column ultima_quot: "-" ERROR: invalid input syntax for type double precision: "-" CONTEXT: COPY gar_tit, line 1, column ultima_quot: "-" setval -------- 6 (1 row) --------- This is an extract of the dump file: [...] CREATE TABLE "dep_tit" ( "cod_rif" character(3), "titolo" character varying(20), "quantity" integer, "costo_med_fisc" double precision, "data_rif" date, "ultima_quot" double precision, ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ "data_ult_q" date ); [...] COPY dep_tit (cod_rif, titolo, quantity, costo_med_fisc, data_rif, ultima_quot, data_ult_q) FROM stdin; 1 Tit. a 100 3.9112 2004-07-23 - - 2 Tit. b 100 4.78 2004-07-23 - - [...] ^^^^^^ ^^^^^^ \. ---------------- As far as I can understand, the hyphen '-' is not double precision, so PG is complaining; the question is: is there a way to get around the problem? How can I avoid dropping those cols and reconstructing them manually on the restored db? Thanks for your attention. Ennio -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Ennio-Sr <nasr.laili@tin.it> writes: > This is an extract of the dump file: > CREATE TABLE "dep_tit" ( > "cod_rif" character(3), > "titolo" character varying(20), > "quantity" integer, > "costo_med_fisc" double precision, > "data_rif" date, > "ultima_quot" double precision, > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > "data_ult_q" date > ); > COPY dep_tit (cod_rif, titolo, quantity, costo_med_fisc, data_rif, > ultima_quot, data_ult_q) FROM stdin; > 1 Tit. a 100 3.9112 2004-07-23 - - > 2 Tit. b 100 4.78 2004-07-23 - - > [...] ^^^^^^ ^^^^^^ It's pretty much impossible to believe that that's what came out of pg_dump originally, because "-" definitely isn't a possible output value for either double precision or date. The best theory I can come up with is that those columns were actually NULL in the source database, and that something somewhere along the line munged the "\N" null markers into "-". Does this theory seem to you to hold any water? Has the dump file been subjected to any indignities like being emailed or copied onto/off of a Windows machine? If that is the correct analysis then the easiest fix is probably to edit the COPY commands to add WITH NULL AS '-' (this assumes that you don't have any fields where '-' would actually be the value). regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [231104, 14:37]: > Ennio-Sr <nasr.laili@tin.it> writes: > > This is an extract of the dump file: > > [...] > > If that is the correct analysis then the easiest fix is probably to edit > the COPY commands to add WITH NULL AS '-' (this assumes that you don't > have any fields where '-' would actually be the value). > > > May I ask how to 'edit the COPY commands'? Perhaps you mean to edit the > > dump file and add "WITH NULL AS '-' " ? Mmmh, I tried to add that in the dump file (at the end of the COPY command) and got these errors: You are now connected as new user "ennio" SET ERROR: relation "dep_tit" already exists ERROR: relation "s_cod_rif" already exists # this is a sequence ERROR: relation "gar_tit" already exists ERROR: syntax error at or near "Data" at charachter 4 ERROR: syntax error at or near "Type" at charachter 2 ERROR: syntax error at or near "Schema" at charachter 2 ERROR: syntax error at or near "Owner" at charachter 2 invalid comman \. ERROR: syntax error at or near "1" at character 1 I also tried to cut the last two columns to no avail. May be a should correct the original table before dumping it? Regards, Ennio -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ]
* Tom Lane <tgl@sss.pgh.pa.us> [231104, 14:37]: > Ennio-Sr <nasr.laili@tin.it> writes: > > This is an extract of the dump file: > > > > COPY dep_tit (cod_rif, titolo, quantity, costo_med_fisc, data_rif, > > ultima_quot, data_ult_q) FROM stdin; > > 1 Tit. a 100 3.9112 2004-07-23 - - > > 2 Tit. b 100 4.78 2004-07-23 - - > > [...] ^^^^^^ ^^^^^^ > > It's pretty much impossible to believe that that's what came out of > pg_dump originally, because "-" definitely isn't a possible output value > for either double precision or date. The best theory I can come up with > is that those columns were actually NULL in the source database, and > that something somewhere along the line munged the "\N" null markers into > "-". Does this theory seem to you to hold any water? Has the dump file > been subjected to any indignities like being emailed or copied onto/off > of a Windows machine? I myself might have committed those 'indignities', maybe manually adding the hyphen, for sure not using win$ ... :-) [It was a few months ago and I cannot remember. Sorry to have arisen any suspect on pg ;( ] > > If that is the correct analysis then the easiest fix is probably to edit > the COPY commands to add WITH NULL AS '-' (this assumes that you don't > have any fields where '-' would actually be the value). > May I ask how to 'edit the COPY commands'? Perhaps you mean to edit the dump file and add "WITH NULL AS '-' " ? In the meantime, many thanks for your answer. Regards, Ennio -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ]
Ennio-Sr <nasr.laili@tin.it> writes: > May I ask how to 'edit the COPY commands'? Perhaps you mean to edit the > dump file and add "WITH NULL AS '-' " ? Yeah. > Mmmh, I tried to add that in the dump file (at the end of the COPY > command) and got these errors: > You are now connected as new user "ennio" > SET > ERROR: relation "dep_tit" already exists > ERROR: relation "s_cod_rif" already exists # this is a sequence > ERROR: relation "gar_tit" already exists > ERROR: syntax error at or near "Data" at charachter 4 > ERROR: syntax error at or near "Type" at charachter 2 > ERROR: syntax error at or near "Schema" at charachter 2 > ERROR: syntax error at or near "Owner" at charachter 2 > invalid comman \. > ERROR: syntax error at or near "1" at character 1 You didn't show what you did, but this looks a bit like you messed up the comment for the next item in the dump script. regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [231104, 18:31]: > Ennio-Sr <nasr.laili@tin.it> writes: > > May I ask how to 'edit the COPY commands'? Perhaps you mean to edit the > > dump file and add "WITH NULL AS '-' " ? > > Yeah. > > > Mmmh, I tried to add that in the dump file (at the end of the COPY > > command) and got these errors: > > ..... > You didn't show what you did, but this looks a bit like you messed up > the comment for the next item in the dump script. May be; sorry for not being able to tell exactly ;) The only thing I'm sure about is that I edited the dump file to delete the hyphens: may be I cancelled significant blanks! Anyway, I could solve my problem as I had another copy on my laptop which hadn't been tampered with ... Thanks again, Ennio. -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ]