Re: ERROR: invalid input syntax for integer: "" - more confusion

On 02/05/2013 08:38 PM, Ben Madin wrote:
> G'day again,
>
> inconceivably, on a completely different issue, I've run into the above error again - this time on both machines, one
running9.1.6, and the other running 9.1.7. 
>
> This time, I have a table with locations, some states (level = 1) and some shires (level = 2). level is defined as an
integertype (no Modifiers or indexes) 
>
> The distribution of these values is best shown by :
>
> SELECT level, count(*) FROM locations GROUP BY level ORDER BY level;
>   level | count
> -------+-------
>       1 |    18
>       2 |   876
> (2 rows)
>
> If I run this query :
>
> SELECT l.id, l.location, l.abbrev, l.locationcode
> FROM locations l
> WHERE l.level = 2
> ORDER BY split_part(locationcode, '.', 1)::int, split_part(locationcode, '.', 2)::int;
>
> I get many hundreds of results in the correct order. If I change the level to 1:
>
> SELECT l.id, l.location, l.abbrev, l.locationcode
> FROM locations l
> WHERE l.level = 1
> ORDER BY split_part(locationcode, '.', 1)::int, split_part(locationcode, '.', 2)::int;
>
> I get:
>
> ERROR:  invalid input syntax for integer: ""
>
> even more confusing, if I take away the ORDER BY clause, it works.

This one I could see if the split_part yielded an empty string.

What type is locationcode and could you provide an example?


> Do I have some corruption somewhere?
>
> I have done a dump / reload, any other suggestions?
>
> cheers
>
> Ben
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com