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.kla...@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
