Re: is (not) distinct from

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)