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
