On 03/01/2017 12:15 AM, Johann Spies wrote:
On 28 February 2017 at 17:06, Adrian Klaver <adrian.kla...@aklaver.com
<mailto:adrian.kla...@aklaver.com>> wrote:


    I have not worked through all this but at first glance I suspect:

    select distinct b.* from b ...

    is distinct from ...

    constitutes a double negative.

    What happens if you eliminate the first distinct?



Thanks Adrian,

The dynamics of the data has changed because of data updates so an exact
comparison is not possible.

Other tests now confirm that the 28 records are identical in both tables.
The results then become more confusing:

If I remove the first distinct
and use "is distinct from"

I get 756 rows

and when I use "is not distinct from"

I get 28.

In the first (756) case when I use "group by" the result of the first
query is exactly the same as the second one.

To be clear you are looking for records in citation that are different from citationbackup over a subset(Are there more fields?) of 8 fields, correct?

What do those 8 fields represent?

Is citationbackup really a backup of citation?

Is there a Primary Key on either/both tables?

What are you grouping by?

Where I am going with this, is that it is not clear to me how you are matching the two sets of records to determine whether they are different or not. Your result that yields 756 rows indicates that the comparison is not an apples to apples comparison, but a comparison of two 'shuffled' sets. Adding the group by seems to sort that out. So some idea of what constitutes a difference and how you determine which records from each table you want to match would be helpful. If you could show the table schema and some sample data it would be even better.


Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)


--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to