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 running 9.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 integer type (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(dot)klaver(at)gmail(dot)com