Bug 776

*** Bug 776 has been marked as a duplicate of this bug. ***

PLEASE REDIRECT THIS BUG REPORT TO THE PostgreSQL
BUILDERS.  I could not find this component listed.

Hello,

There is a serious bug in the PostgreSQL 6.3.2-10 release
that is bundled with Redhat 5.2 (we purchased the MacMillan
Digital Publishing version CDs if that matters).

This bug (and many others) has already been fixed by the
PostgreSQL people, and so it should not be much of a
problem for you to release a new RPM package of the
latest version, which is PostgreSQL 6.4.2.

1) The bug in particular that is difficult to work around
is a fundamental type conversion problem between text
and varchar types.  The following SQL illustrates (run
this from a psql prompt):

create table atable(data varchar(25));
insert into atable values ('dd/mm/yyyy');
select * from atable;
select ((substr(data,7,4) || substr(data,4,2))
           || substr(data,1,2)) as newform from atable;
update atable set data=((substr(data,7,4)
           || substr(data,4,2)) || substr(data,1,2));
select * from atable;

==================

2) When run from the Redhat bundled 6.3.2.10, the type
conversion error manifests itself...

create table atable(data varchar(25));
insert into atable values ('dd/mm/yyyy');
select * from atable;
CREATE
INSERT 322249 1
data
----------
dd/mm/yyyy
(1 row)

select ((substr(data,7,4) || substr(data,4,2))
           || substr(data,1,2)) as newform from atable;
newform
--------
yyyymmdd
(1 row)

update atable set data=((substr(data,7,4)
           || substr(data,4,2)) || substr(data,1,2));
ERROR:  parser: attribute 'data' is of type 'varchar' but
expression is of type
'text'
select * from atable;
data
----------
dd/mm/yyyy
(1 row)


==================

3) The same script executed under 6.4.2 demonstrates
that this bug has been fixed...

create table atable(data varchar(25));
insert into atable values ('dd/mm/yyyy');
select * from atable;
CREATE
INSERT 632073 1
data
----------
dd/mm/yyyy
(1 row)

select ((substr(data,7,4) || substr(data,4,2))
           || substr(data,1,2)) as newform from atable;
newform
--------
yyyymmdd
(1 row)

update atable set data=((substr(data,7,4)
           || substr(data,4,2)) || substr(data,1,2));
select * from atable;
UPDATE 1
data
--------
yyyymmdd
(1 row)

===================

4) The latest sources are available from the web site
   http://www.postgresql.org/ but are not available
   from them in RPM format.

===================

5) Note that it is necessary to dump the database
   before moving from the 6.3.2 version (using the
   command documented in the INSTALL file of the
   6.4.2 src directory:

         pg_dumpall -o > db.out

   After 6.4.2 is installed, the data can be
   restored using the command:

         psql -e template1 < db.out

================
Please issue an RPM upgrade for PostgreSQL as soon as
possible.

Thanks,
Alvin Austin
alvin.ca

Our 5.9 beta release contains postgresql 6.4.2, which should fix the
problem you mention.  Our forthcoming official Red Hat Linux 6.0
release will of course have the fix too.