query configuration for validate empty quote to zero

Yudie

Re: query configuration for validate empty quote to zero

Tom Lane

Re: query configuration for validate empty quote to zero

Yudie

From:	Yudie
Subject:	query configuration for validate empty quote to zero
Date:	Mon, 17 Jan 2005 12:44:20 -0600

This is a multi-part message in MIME format.

------=_NextPart_000_0008_01C4FC92.43C1DDE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

We just changed the postgresql server and got some problem with sql =
command
in our old server we can exexute this query:=20

SELECT * FROM product WHERE prdtnumber =3D ''

field prdtnumber data type is integer and the empty quote somehow can be =
assumed into 0 (zero)
Now, if I use the same query it return an error:
invalid input syntax for integer: ""

Anyone know what's the problem??

Thanks

Yudie

------=_NextPart_000_0008_01C4FC92.43C1DDE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

charset=3Diso-8859-1">

We just changed the postgresql server =
and got some=20
problem with sql command

in our old server we can exexute this =
query:=20

SELECT * FROM product WHERE prdtnumber =
=3D=20
''

field prdtnumber data type=20
is integer and the empty =
quote somehow=20
can be assumed into 0 (zero)

Now, if I use the same query it =
return an=20
error:

invalid input syntax for integer: =
""

Anyone know what's the =
problem??

Thanks

Yudie

------=_NextPart_000_0008_01C4FC92.43C1DDE0--

From:	Tom Lane
Subject:	Re: query configuration for validate empty quote to zero
Date:	Mon, 17 Jan 2005 14:23:09 -0500

"Yudie" writes:
> Now, if I use the same query it return an error:
> invalid input syntax for integer: ""

> Anyone know what's the problem??

None. That's an intentional change. The fact that it ever accepted
'' as meaning 0 was a bug.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

From:	Yudie
Subject:	Re: query configuration for validate empty quote to zero
Date:	Mon, 17 Jan 2005 13:30:03 -0600

The problem is not stop there, I had a table that can't be updated since
then with this simply command:
update prdttable set webinclude = '0' where prdtnumber = '6002'

Again, the errors (invalid input syntax for integer: "") apears.

Then I found this temporary solution to make replica to this record to
another table in sense of update the value in the new table.
1. Create table prdt_problem as select * form prdttable where prdtnumber =
'6002';

2. Update prdt_problem set webinclude = '0' where prdtnumber = '6002';

3. Delete from prdttable where prdtnumber = '6002';

4. Insert into prdttable select * from prdt_problem

How to fix this bug?

> None. That's an intentional change. The fact that it ever accepted
> '' as meaning 0 was a bug.
>
> regards, tom lane
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings