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

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 =3D 1) and =
some shires (level =3D 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=20
-------+-------
     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 =3D 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 =3D 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.

Do I have some corruption somewhere?
=20
I have done a dump / reload, any other suggestions?

cheers

Ben



--=20

Ben Madin

m : +61 448 887 220
e : ben@ausvet.com.au