f



Upgrading 7.2.4 to 7.4.1

Dear all,

I've got an old 7.2.4 DB which I hoped to upgrade to 7.4.1.
The old syntax used for creating tables had lines like
...
id integer DEFAULT '',

which translates to this error:

pg_restore: [archiver (db)] could not execute query: ERROR:  invalid 
input syntax for integer: ""

I found out that integer with '' as default is not acceptable after 7.3. 
  Also, some complaints were given with the data, particulary numerous 
"\N" lines when restoring (this can be worked out by using -d switch 
when dumping).

Also, schemas are not going to be created (some 7.3 documents gave this up).

Thus, must I update every DB creation script by hand and try to restore 
only the data?


Regards,
Jarmo


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
Jarmo
2/16/2004 12:59:26 PM
comp.postgresql.admin 1725 articles. 0 followers. ashok (20) is leader. Post Follow

2 Replies
1130 Views

Similar Articles

[PageSpeed] 53

> I've got an old 7.2.4 DB which I hoped to upgrade to 7.4.1.
> The old syntax used for creating tables had lines like
> ..
> id integer DEFAULT '',
>
> which translates to this error:
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  invalid
> input syntax for integer: ""
>
> I found out that integer with '' as default is not acceptable after 7.3.
>   Also, some complaints were given with the data, particulary numerous
> "\N" lines when restoring (this can be worked out by using -d switch
> when dumping).
>
> Also, schemas are not going to be created (some 7.3 documents
> gave this up).
>
> Thus, must I update every DB creation script by hand and try to restore
> only the data?

Not only dbase create scripts, but most likly you have to do serious app
level reviews to get things working.
As you mentioned, if you used stuff like: WHERE intField = '' (in 7.2.4 ''
was converted to 0 (zero), in 7.4.1 you get pg_atoi error) etc. you are in
trouble. There is also other things, for example in our application, we used
statements like:
now() + intValue (in 7.2.4 it added given amount of days to timestamp), in
7.4.1 it is not permitted anymore.
And in 7.2.4 statements like: somevalue/7 + 1 worked, IIRC in 7.4.1 it
complains about "no operator float + integer" or something like that.

We had to stop migration to 7.4.1 because of all this, so if anyone knows
how to do it relatively fast or/and painless, or knows where is complete
list of this kind of changes/gotchas, please let me know.

Rigmor Ukuhe


>
>
> Regards,
> Jarmo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

0
rigmor
2/16/2004 1:54:00 PM
You might get better results using 7.4's pg_dump against the 7.2 database... 
worth a try anyway.

Robert Treat

On Monday 16 February 2004 07:59, Jarmo J�rvenp�� wrote:
> Dear all,
>
> I've got an old 7.2.4 DB which I hoped to upgrade to 7.4.1.
> The old syntax used for creating tables had lines like
> ..
> id integer DEFAULT '',
>
> which translates to this error:
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  invalid
> input syntax for integer: ""
>
> I found out that integer with '' as default is not acceptable after 7.3.
>   Also, some complaints were given with the data, particulary numerous
> "\N" lines when restoring (this can be worked out by using -d switch
> when dumping).
>
> Also, schemas are not going to be created (some 7.3 documents gave this
> up).
>
> Thus, must I update every DB creation script by hand and try to restore
> only the data?
>
>
> Regards,
> Jarmo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
xzilla
2/22/2004 2:09:39 AM
Reply: