MERGE SQL in cx_Oracle executemany

[Peter J. Holzer]

On 2020-10-18 06:35:03 -0000, Mladen Gogala via Python-list wrote:
> On Sat, 17 Oct 2020 21:23:40 -0600, Jason Friedman wrote:
> >> I'm looking to insert values into an oracle table (my_table) using the
> >> query below. The insert query works when the PROJECT is not NULL/empty
> >> (""). However when PROJECT is an empty string(''), the query creates a
> >> new duplicate row every time the code is executed (with project value
> >> populating as null). I would like to modify my query so a new row is
> >> not inserted when all column values are matched (including when project
> >> code is null).
[...]
> > Perhaps the issue is that NULL is not equal to anything. Oracle provides
> > the IS NULL function to determine if a value is NULL.
> > 
> > Note also you define "cur" but executemany with "cur3".
> > 
> > And is "rows = [tuple(x) for x in df.values]" what you want? Print it.
> 
> Obviously, the "PROJECT" column is causing the issue. NULL in Oracle 
> database is never equal to anything.

Not only in Oracle. This is standard SQL behaviour. NULL means
"unknown", and if you compare two unknown values, the result is of
course also unknown. 

However, Oracle adds an additional complication because it converts ''
(the empty string) to NULL on insert. 

> If :7 is NULL, your "not matched" 
> condition is satisfied and your MERGE statement will insert a new and 
> exciting row. That has nothing to do with Python. The only solution is
> "ALTER TABLE my_table modify(project not null)" or 
> "ALTER TABLE my_table add constraint project_nn check(project is not null)"  I

Yup. In addition, because that won't accept an empty string, you'll have
to use some non-empty string (e.g. '-' or '(no project)') to signify
that there is no project. (One might argue that this is better design
anyway (explicit rather than implicit).)

> If you already have NULL values in the PROJECT column than use "add constraint" 
> with NOVALIDATE option. Other than that, allowing NULL values in key columns is
> a sign of bad design. Namely, Oracle cannot index NULL columns, so PROJECT IS NULL
> predicate cannot be resolved by an index.

It can with a bitmap index. However, last time I looked (admittedly long
ago) this was an enterprise edition feature.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp at hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://mail.python.org/pipermail/python-list/attachments/20201018/80ae697c/attachment.sig>