[GENERAL] is (not) distinct from

Поиск
Список
Период
Сортировка
От Johann Spies
Тема [GENERAL] is (not) distinct from
Дата
Msg-id CAGZ55DSy1_VeT3atN2iupR4Zzn-8VxO3iJ7qh+s_KEktBsEncQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] is (not) distinct from  (Adrian Klaver <adrian.klaver@aklaver.com>)
Список 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)

В списке pgsql-general по дате отправления:

Предыдущее
От: Sasa Vilic
Дата:
Сообщение: Re: [GENERAL] Shared WAL archive between master and standby: WALs notalways identical
Следующее
От: "Sven R. Kunze"
Дата:
Сообщение: Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE