Thread: 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

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

G'day Adrian,

On 2013-02-06, at 12:44 , Adrian Klaver <adrian.klaver@gmail.com> wrote:

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

You are right on this one - the higher level components don't have a =
split part 2. Thank you.

>=20
> What type is locationcode and could you provide an example?


Varchar   an example would be 6 for level 1 field, and 6.34 for level 2.

cheers

Ben



--=20

Ben Madin

t : +61 8 6102 5535
m : +61 448 887 220
e : ben@ausvet.com.au

AusVet Animal Health Services
P.O. Box 5467
Broome   WA   6725
Australia

AusVet's website:  http://www.ausvet.com.au

This transmission is for the intended addressee only and is confidential =
information. If you have received this transmission in error, please =
delete it and notify the sender. The contents of this email are the =
opinion of the writer only and are not endorsed by AusVet Animal Health =
Services unless expressly stated otherwise. Although AusVet uses virus =
scanning software we do not accept liability for viruses or similar in =
any attachments. Thanks for reading.