RE: SQL Server & MySQL

… but if all the known value columns of a unique constraint match, you cannot prove that the value of a null column is different, either. Neither NULL = NULL nor NULL != NULL is allowed. Likewise neither “something” = NULL nor “something != NULL is allowed.  

You can’t say they are equal and you cannot assert they are unequal. Therefore I think Oracle’s evaluation of whether they can assert uniqueness is correct, but I see that an argument can be made that they also cannot be proven to violate the constraint. Oracle won’t allow the row in because they cannot assert the row is compliant with the constraint. Postgres allows the row because they cannot assert the row is the same.  

Yet another problem with NULL values. I Codd well know that we can’t live without them in the practical world, especially in the context of fill them in when you find out what they are, but record what you know now, now. But they sure cause problems!  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, February 14, 2022 8:51 PM
To: Kellyn Pot'Vin-Gorman; kylelf_at_gmail.com Cc: ORACLE-L
Subject: Re: SQL Server & MySQL  

On 2/14/22 14:36, Kellyn Pot'Vin-Gorman wrote:

Innodb is the love child of Oracle and Sql server…:). PostgreSQL is more like Oracle in its ability to handle Pl/Sql, but it still has a long way to go. Tim and I handle a lot of failed Oracle migrations and it’s painful…:(

That's interesting. My current project is about porting application system from Oracle to Postgres. I do confess that it's hard but as long as you don't need global indexes, it's more or less doable. However, there are funny things like this:

mgogala=# create table test1(c1 integer not null,c2 varchar(3),c3 varchar(10)); CREATE TABLE
mgogala=# alter table test1 add constraint test1_uq unique(c1,c2); ALTER TABLE
mgogala=# insert into test1 values(1,NULL,'qwerty'); INSERT 0 1
mgogala=# insert into test1 values(1,NULL,'qwerty'); INSERT 0 1
mgogala=# insert into test1 values(1,NULL,'qwerty'); INSERT 0 1
mgogala=# select * from test1;
 c1 | c2 | c3
----+----+--------

  1 |    | qwerty
  1 |    | qwerty
  1 |    | qwerty

Oracle will not allow things like that. However, when you check standard, PostgreSQL is actually correct here. The problem is in the NULL column which is not equal to anything which means that none of those 3 rows are actually equal to any other of the rows. Another funny stuff with converting from Oracle to Postgres are transactions. Here is what I have in mind:

mgogala=# begin transaction;
BEGIN
mgogala=*# insert into test1 values (NULL,NULL,'qwerty') /* Error expected */; ERROR: null value in column "c1" of relation "test1" violates not-null constraint DETAIL: Failing row contains (null, null, qwerty). mgogala=!# insert into test1 values(1,NULL,'qwerty'); ERROR: current transaction is aborted, commands ignored until end of transaction block  

So, a statement fails and the rest of transaction is aborted. Oracle allows you to continue. The secret is in Oracle's automatic statement level savepoints. Postgres too supports savepoints, so the problem is solvable, but there are many surprises awaiting those who would try it. It's definitely not something for the faint hearted.

However, it's not just Oracle. People are migrating from SQL Server too. There is even an open source project for that: https://babelfishpg.org/

Currently, you need a special version of Postgres for that, but there is one, very popular, version that supports it: AWS Aurora. I have built Babelfish on a VM and it is possible to connect using SQL Server Management Studio 2017 and SQL Server ODBC driver.

Another thing that people don't understand is that Postgres doesn't cache code. By that, I mean PgPL/SQL code and SQL code. There is nothing like shared pool in the world of Postgres. That means that you need much stronger hardware than with Oracle. Fortunately, the price of Oracle more than makes for the difference in the machines.

Having learned this and many other things, our project is a success.  

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com




--
http://www.freelists.org/webpage/oracle-l