From: | Johann Spies <johann(dot)spies(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | is (not) distinct from |
Date: | 2017-02-28 08:08:14 |
Message-ID: | CAGZ55DSy1_VeT3atN2iupR4Zzn-8VxO3iJ7qh+s_KEktBsEncQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
When I query table a I get 18 rows.
The same query on table b results in 28 rows.
Both tables have the same structure.
When I export the results to csv-files and do a diff it confirms that all
18 rows from a are also in b. Table b has 10 new rows.
When I combine these queries and use "is (not) distinct from"
I get strange results:
with a as (select citing_article, cited_article, pubyear, year_cited,
cited_author, cited_title, cited_work, doi
from wos_2017_1.citation
where citing_article='abcdefg'
order by 3,4,5,6,8),
b as (
select citing_article, cited_article, pubyear, year_cited, cited_author,
cited_title, cited_work, doi
from wos_2017_1.citationbackup
where citing_article='abcdefg'
order by 3,4,5,6,8)
select distinct b.* from b , a
where
( B.citing_article,
B.cited_article,
B.pubyear,
B.year_cited,
B.cited_author,
B.cited_title,
B.cited_work,
B.doi)
is distinct from
(A.citing_article,
A.cited_article,
A.pubyear,
A.year_cited,
A.cited_author,
A.cited_title,
A.cited_work, A.doi)
The result of this query is 28 rows - thus that of b.
I expected this to be 10.
If I change the query to "is not distinct from" it results in 18 rows
which is what I would have expected.
Regards
Johann.
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
From | Date | Subject | |
---|---|---|---|
Next Message | Sven R. Kunze | 2017-02-28 09:35:31 | Re: ERROR: functions in index expression must be marked IMMUTABLE |
Previous Message | Sasa Vilic | 2017-02-28 07:14:20 | Re: Shared WAL archive between master and standby: WALs not always identical |