pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery configuration for validate empty quote to zero
4+ messages / 3 participants
[nested] [flat]
* query configuration for validate empty quote to zero
@ 2005-01-17 18:44 Yudie <yudie@axiontech.com>
2005-01-17 19:23 ` Re: query configuration for validate empty quote to zero Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Yudie @ 2005-01-17 18:44 UTC (permalink / raw)
To: pgsql-sql
We just changed the postgresql server and got some problem with sql command
in our old server we can exexute this query:
SELECT * FROM product WHERE prdtnumber = ''
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query configuration for validate empty quote to zero
2005-01-17 18:44 query configuration for validate empty quote to zero Yudie <yudie@axiontech.com>
@ 2005-01-17 19:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-17 19:30 ` Re: query configuration for validate empty quote to zero Yudie <yudie@axiontech.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2005-01-17 19:23 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql
"Yudie" <yudie@axiontech.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query configuration for validate empty quote to zero
2005-01-17 18:44 query configuration for validate empty quote to zero Yudie <yudie@axiontech.com>
2005-01-17 19:23 ` Re: query configuration for validate empty quote to zero Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-17 19:30 ` Yudie <yudie@axiontech.com>
0 siblings, 0 replies; 4+ messages in thread
From: Yudie @ 2005-01-17 19:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query configuration for validate empty quote to zero
@ 2005-01-18 16:17 KÖPFERL Robert <robert.koepferl@sonorys.at>
0 siblings, 0 replies; 4+ messages in thread
From: KÖPFERL Robert @ 2005-01-18 16:17 UTC (permalink / raw)
To: pgsql-sql
Did you try to explicitly give the type for the literal?
e.g. ''::varchar
-----Original Message-----
From: Yudie [mailto:yudie@axiontech.com]
Sent: Montag, 17. Jänner 2005 19:44
To: pgsql-sql@postgresql.org
Subject: [SQL] query configuration for validate empty quote to zero
We just changed the postgresql server and got some problem with sql command
in our old server we can exexute this query:
SELECT * FROM product WHERE prdtnumber = ''
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-01-18 16:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-17 18:44 query configuration for validate empty quote to zero Yudie <yudie@axiontech.com>
2005-01-17 19:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-17 19:30 ` Yudie <yudie@axiontech.com>
2005-01-18 16:17 Re: query configuration for validate empty quote to zero KÖPFERL Robert <robert.koepferl@sonorys.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox