Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBVHHZL89096 for ; Sun, 31 Dec 2000 12:17:35 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eBVHHKC08416; Sun, 31 Dec 2000 12:17:20 -0500 (EST) To: Ferruccio Zamuner cc: pgsql-sql@postgresql.org Subject: Re: Unable to identify an ordering operator In-reply-to: <200012311455.eBVEtCt11654@tnt.diff.org> References: <200012311455.eBVEtCt11654@tnt.diff.org> Comments: In-reply-to Ferruccio Zamuner message dated "Sun, 31 Dec 2000 15:55:12 +0100" Date: Sun, 31 Dec 2000 12:17:19 -0500 Message-ID: <8413.978283039@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200012/319 X-Sequence-Number: 659 Ferruccio Zamuner writes: > select persons.name,firm.name,persons.tel > from persons,work,firm > where (persons.table_owner=0) and > (work.id_firm=firm.id and work.id_person=persons.id) > union > select persons.name,firm.name,persons.tel > from persons,work,firm > where (persons.table_owner=1) and > (work.id_firm=firm.id and work.id_person=persons.id); > ERROR: Unable to identify an ordering operator '<' for type '_text' UNION is defined to eliminate duplicate rows, so it has to be able to sort and compare rows, which means it needs < and = operators for each selected column's datatype. Evidently one of the name or tel columns is declared as array of text ('_text' means text[]), for which there is no '<' operator. You could probably make one if you were intent on it (see contrib/array for some inspiration). A lazier way out is to use UNION ALL, which doesn't try to eliminate duplicates. regards, tom lane