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.
Do I have some corruption somewhere?
I have done a dump / reload, any other suggestions?
cheers
Ben
--
Ben Madin
m : +61 448 887 220
e : ben@xxxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
