except on nulls?

From: Daniel Kalchev <daniel(at)digsys(dot)bg>
To: pgsql-sql(at)postgresql(dot)org
Subject: except on nulls?
Date: 2000-10-27 06:02:43
Message-ID: 200010270602.JAA16927@dcave.digsys.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Grant Finnemore 2000-10-27 08:30:19 Re: [SQL] renaming columns... danger?
Previous Message Tom Lane 2000-10-27 05:33:41 Re: HELP! ... pg_locale ???