Обсуждение: ERROR: invalid input syntax for integer: ""

Following query fails in pgAdmin which is OK because the field PLZZ
contains characters:

select * from "PERSONEN" where (CAST("PERSONEN"."PLZZ" AS INTEGER) >=
70000 );

but if using the same query with a cursor ist works:

START TRANSACTION;

DECLARE c21112234 SCROLL CURSOR FOR select * from "PERSONEN" where
(CAST("PERSONEN"."PLZZ" AS INTEGER) >= 70000;

COMMIT;


Why??
In my opinion this query should also fail with cursors.


I use libpq with PG 8.2.1.
The field PLZZ is of type varchar.

"Pit M." <fmi-soft@gmx.de> writes:
> Following query fails in pgAdmin which is OK because the field PLZZ
> contains characters:

> select * from "PERSONEN" where (CAST("PERSONEN"."PLZZ" AS INTEGER) >=
> 70000 );

> but if using the same query with a cursor ist works:

> START TRANSACTION;

> DECLARE c21112234 SCROLL CURSOR FOR select * from "PERSONEN" where
> (CAST("PERSONEN"."PLZZ" AS INTEGER) >= 70000;

> COMMIT;

> In my opinion this query should also fail with cursors.

It would have failed if you had run the cursor far enough to fetch one
of the bad rows.

            regards, tom lane

> It would have failed if you had run the cursor far enough to fetch one
> of the bad rows.
>
>             regards, tom lane
>
The difference is that in one case the query fails and in the other the
FETCH command fails.


Our problem is that if a query succeeds we use a count(*) of that query
to display the result count in the status bar of our application - the
select works but the count(*) fails :-)
When we use this query on MS SQL-Server the query fails directly though
we also use cursors here. So the SQL-Server somehow checks all the data
only for the query -> perhaps because we used a server side cursor.

Will there be any improvements with cursors in PG 8.3?

Thanks a lot tom

Pit M. wrote:
>
>> It would have failed if you had run the cursor far enough to fetch one
>> of the bad rows.
>>
>>             regards, tom lane
>>
> The difference is that in one case the query fails and in the other the
> FETCH command fails.
>
>
> Our problem is that if a query succeeds we use a count(*) of that query
> to display the result count in the status bar of our application - the
> select works but the count(*) fails :-)
> When we use this query on MS SQL-Server the query fails directly though
> we also use cursors here. So the SQL-Server somehow checks all the data
> only for the query -> perhaps because we used a server side cursor.

IIRC, the behavior of MSSQL will depend on the query plan. If it's a
plan that requires doesn't require materialization at all, it won't
figure it out until you get there.

//Magnus

Magnus Hagander <magnus@hagander.net> writes:
> IIRC, the behavior of MSSQL will depend on the query plan. If it's a
> plan that requires doesn't require materialization at all, it won't
> figure it out until you get there.

... which is pretty much what we do, too.

            regards, tom lane