Database migrations from Oracle to PostgreSQL and SQL Server to PostgreSQL can get tricky when certain differences are not known. Unknown differences can lead to migration failures. We have earlier blogged about the behavior of DATE data type and Transaction Control statements between Oracle and SQL Server and PostgreSQL. In this article, we are going to discuss about the behavior of NULL and empty string in Oracle vs PostgreSQL vs SQL Server with Unique Constraint. This is the first article in our series about handling NULLS during migration from Oracle to PostgreSQL and SQL Server to PostgreSQL.
What is a NULL ?
According to ANSI SQL-92 specifications, a null is neither an empty string (for a character or a datetime data type) nor a value of zero (for numeric data types). To ensure that all nulls are handled uniformly, the ANSI SQL-92 specification stipulates that a null must be the same for all data types. When attributes have no data to be stored for a given record, it is represented by the SQL value, NULL.
NULL and Empty String in Oracle vs PostgreSQL vs SQL Server
Oracle treats both NULL as well as an Empty String as a NULL. PostgreSQL treats a NULL as a NULL and an Empty String as an Empty String. Similar to PostgreSQL, SQL Server also treats a NULL as a NULL and an Empty String as an Empty String.
Oracle needs one byte to store a NULL. Whereas, both PostgreSQL and SQL Server won’t require any space to store a NULL.
UNIQUE constraint on NULL and Empty String
In Oracle, a column on which there exists a unique constraint, can store any number of NULL entries and empty strings.
However, SQL Server only allows one NULL and one Empty String to be inserted to a column when there exists a Unique Constraint on it.
In PostgreSQL, multiple NULL entities similar to Oracle are allowed to be inserted. Similar to SQL Server, PostgreSQL only allows one Empty String when there exists a unique constraint on that column.
Subscribe to our Newsletters today, to get notified about such useful information.
Unique NULLS NOT DISTINCT clause
Interestingly, starting from PostgreSQL 15, we may use the NULLS NOT DISTINCT clause to prevent null values from being handled as distinct. This prevents us from inserting more than one null value into a column with a unique constraint on it.
Let us consider the following example where we are creating a table and inserting multiple NULL values to the same column that has a unique constraint on it.
Treating NULLS as DISTINCT ################################# CREATE TABLE _migopstest.null_empty_test(a text, b text, UNIQUE(a)); INSERT INTO _migopstest.null_empty_test VALUES(null); INSERT 0 1 INSERT INTO _migopstest.null_empty_test VALUES(null); INSERT 0 1
We shall now try the new feature added in PostgreSQL 15 to consider NULL as not distinct. This is done by including an additional clause : UNIQUE NULLS NOT DISTINCT, for the respective column.
Treating NULLS as NOT DISTINCT ################################# CREATE TABLE null_empty_test(a text, b text, UNIQUE NULLS NOT DISTINCT(a)); INSERT INTO null_empty_test VALUES(null); INSERT 0 1 postgres=# INSERT INTO null_empty_test VALUES(null); ERROR: duplicate key value violates unique constraint "null_empty_test_a_key" DETAIL: Key (a)=(null) already exists.
In the above example, we could notice an error triggered by PostgreSQL when a duplicate NULL entry is being inserted. This is a feature available from PostgreSQL 15 that can be explicitly enabled when required.
Subscribe to our Newsletters today, to get notified about such useful information.
Conclusion
While migrating from Oracle to PostgreSQL and SQL Server to PostgreSQL, it is important to know such differences. Most of the migration mistakes are observed either when Customers are heavily relying on some handy tools or when there is no guidance from a Migration Expert. Contact MigOps today, if you are looking to migrate to PostgreSQL. You may fill the following form or send an email to sales@migops.com for inquiries. In our next article on NULLS, we shall discuss the behavior of NULLS with a Composite Unique Key and also certain operators and functions.
