could not find pathkey item to sort

Hi All,

Since upgrading to PG 8.2, I’m getting this error on a nightly (bash) script that calls psql (it’s purpose is to find stock codes that share an EAN Barcode with another code – it used to work, now doesn’t):

ERROR: could not find pathkey item to sort

SQL state: XX000

The error is because of the ORDER BY clause of this query:

SELECT  code, description, ean

FROM    $TMPTABLE

WHERE   ean <> ''

 AND    ean IN (SELECT ean FROM $TMPTABLE GROUP BY ean HAVING count(ean) > 1)

ORDER BY ean

The $TMPTABLE is created as per:

CREATE TEMP TABLE $TMPTABLE

(

  code varchar(6),

  description varchar(38),

  grp varchar(4),

  brand text,

  style text,

  supplier varchar(6),

  supplier_code text,

  wholesale numeric(10,2),

  retail numeric(10,2),

  ean varchar(13)

)

WITHOUT OIDS;

-- Import the file from WB Database

COPY $TMPTABLE FROM '$TMPFILE';

I’ve tried creating an index and doing a reindex on the temp table because the select to no avail.

Any help would be great – I’ve “fixed” it at the moment by commenting the ORDER BY clause, but it would be nice to have it sorted properly, and also in case it happens in any other tables.

Thanks,

-p

Phillip Smith
IT Coordinator
Weatherbeeta P/L
8 Moncrief Rd
Nunawading, VIC, 3131
AUSTRALIA
 
E. phillips@weatherbeeta.com.au