Обсуждение: 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) ]