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.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

--

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general