Ispirer Website Ispirer Capabilities: PostgreSQL Migration Free Trial

Comparison of Text Values when Migrating from Microsoft SQL Server to PostgreSQL

Often, when migrating from Microsoft SQL Server to PostgreSQL, there is a problem when comparing text values. In contrast to Microsoft SQL Server, where comparing text values is case-insensitive, the PostgreSQL database is case-sensitive. For example, strings 'IsPiRer' and 'ispirer' are equal in Microsoft SQL Server and not equal in PostgreSQL. This difference can lead to a different set of returned results in Microsoft SQL Server and PostgreSQL when executing queries that use text value comparisons. It can also cause a violation of the uniqueness of the primary key if it is created on a text-type column and in many other cases.

To fix this issue during migrating from Microsoft SQL Server to Postgresql, we propose the following solutions: For example, let`s create a table “customers” with two text fields “first_name” and “last_name” and fill it with data.

 create table customers (
   first_name varchar(64),
   last_name varchar(64)
 );

first_name	last_name
'John'	'Le'
'Stive'	'Maison'
'JOHN'	'SMITH'

1. When comparing text data, you need to use 'ilike' instead of '=':

Microsoft SQL Server	PostgreSQL
select * from customers where first_name = 'john'	select * from customers where first_name ilike 'john'

2. For a more complex comparison of text fields, you can use LOWER() or UPPER() functions:

Microsoft SQL Server	PostgreSQL
select * from customers where first_name in ('john', 'Stive')	select * from customers where LOWER(first_name) in (LOWER('John'), LOWER('Stive'))

3.When creating tables, specify the case-insensitive collation parameter for text fields:
3.1. Create a case-insensitive collation. For more information, use the following link: https://www.postgresql.org/docs/current/collation.html

 CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

3.2. Explicitly specify this collation for all text columns in the tables:

 create table customers (
   first_name varchar(64) COLLATE case_insensitive,
   last_name varchar(64) COLLATE case_insensitive
 );

In this case, we don`t need to change the query:

Microsoft SQL Server	PostgreSQL
select * from customers where first_name = 'john'	select * from customers where first_name = 'john'

4. During migartion you can convert the all text data to lower or upper case. In this case, the results of text data comparison will be the same in Microsoft SQL Server and Postgresql:

Microsoft SQL Server

first_name	last_name
'John'	'Le'
'Stive'	'Maison'
'JOHN'	'SMITH'

PostgreSQL

first_name	last_name
'john'	'le'
'stive'	'maison'
'john'	'smith'

5. If there is a column that is often used for search, you can create an additional column that will have the current value in uppercase. Thus, when comparing, it will not be necessary to make the value from the original column uppercase each time, but to use the string already made uppercase from the additional column.

 create table customers (
   first_name varchar(64),
   last_name varchar(64),
   first_name_upper varchar(64) GENERATED ALWAYS AS (UPPER(first_name )) STORED
 );

first_name	last_name	last_name_upper
'john'	'le'	'JOHN'
'stive'	'maison'	'STIVE'
'john'	'smith'	'JOHN'

Microsoft SQL Server	PostgreSQL
select * from customers where first_name = 'john'	select * from customers where first_name_upper = UPPER('john')

Conclusions:

The first two solutions are practically equivalent and fix the case-sensitive comparison issue completely. However, but these solutions reduce query performance. This is especially noticeable if the queries contain a large number of joins and conditions on them.

If you often have to compare values between text columns in a database, we recommend using the third solution as it only involves changes when creating tables and has better performance than the first two solutions. However, when using this solution there may be issues when comparing text variables or constants with each other.

Regarding the fourth solution, We don't recommend using it because it involves data convertion/changing, which in turn increases the likelihood of additional issues. Also there may be issues when converting special characters to upper/lower case.

The fifth solution involves private manual fixes that cannot be automated. But it can be very useful if there are some text columns which are often compared and critical need to improve performance for queries there are it used.

We hope this case will be helpful if you encounter the task of comparison of text values when migrating from MSSQL to PostgreSQL. The fourth solution is automatically executed by our software SQLWays Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com