Re: is (not) distinct from

[Johann Spies <johann.spies@xxxxxxxxx>]

Thanks (again Adrian) and Tom.

The situation was that I had a table with 731million records which I wanted to copy into a partitioned one where there was a unique constraint on the fields used in my query.

The "backup" table was the single one.

While inserting into the partitioned table from the backup one, several (about 120000) records failed to insert.

I wanted to find out which records were involved and found that some had "'" characters in the values which broke some of the functions used to do some calculations.. 

As there were fields that might have null values I have tried the "is not distinct from".

Both sides of the query had primary keys and I did not use group by.  That was why I used "distinct".

Anyhow in the end, I made some progress with a modified query:

where
s.citing_article = A.citing_article
   and
          s.cited_article !=  A.cited_article
   and
   s.pubyear is  distinct from A.pubyear
   and
   s.year_cited is distinct from A.year_cited
   and
   s.cited_author is distinct from A.cited_author
   and 
        regexp_replace(s.cited_title,  $$'$$, $$''$$,'g') is distinct from
        regexp_replace(A.cited_title,  $$'$$, $$''$$,'g')
   and 
        regexp_replace(s.cited_work,  $$'$$, $$''$$,'g') is distinct from regexp_replace(A.cited_work,   $$'$$, $$''$$,'g')
   and
        s.doi is distinct from A.doi

Regards.

Johann

--

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