Received: from dcave.digsys.bg (root@dcave.digsys.bg [192.92.129.5]) by hub.org (8.10.1/8.11.0) with ESMTP id e9R62pq20071 for ; Fri, 27 Oct 2000 02:02:52 -0400 (EDT) (envelope-from daniel@dcave.digsys.bg) Received: from dcave.digsys.bg (daniel@localhost.digsys.bg [127.0.0.1]) by dcave.digsys.bg (8.9.3/8.9.3) with ESMTP id JAA16927 for ; Fri, 27 Oct 2000 09:02:44 +0300 (EEST) Message-Id: <200010270602.JAA16927@dcave.digsys.bg> X-Mailer: exmh version 2.1.0 09/18/1999 From: Daniel Kalchev To: pgsql-sql@postgresql.org Subject: except on nulls? Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Fri, 27 Oct 2000 09:02:43 +0300 X-Archive-Number: 200010/254 X-Sequence-Number: 1921 I had the following weird experience. Hopefuly iy's my ignorance on the matter (but I sort of think this worked before...) On PostgreSQL 7.0.2, I have an database with two tables, holding different versions of the data set (some rows missing in one of the tables). Example is: CREATE TABLE "test1" ( "f1" text, "f2" date, "f3" "_text", "f4" int4, "f5" character varying(16) ); Then table test2 with the same definition. SELECT f1 FROM test2 EXCEPT SELECT f1 from test1; produced empty result, although test2 contained few more rows. Closer examination showed that both tables contained an 'null' row, with all fields null. After removing both rows, the query would return the proper results. Another query, SELECT * from test2 EXCEPT SELECT * from test1; failed too, giving the following error: ERROR: Unable to identify an operator '<>' for types '_text' and '_text' You will have to retype this query using an explicit cast Any idea why this is happening? Daniel