Case sensitivity in string comparison operations is a known issue in PostgreSQL. There are several ways to solve this, we will consider three of them.

Let's assume that we have such a table in SQL Server:

   create table tlike (id int, title varchar(20), title2 varchar(20));
   insert into tlike values
        (1, 'ispirer', 'IsPiReR'),
        (2, 'IspiRer', 'IsPiReR'),
        (3, 'IsPiReR', 'IsPiReR'),
        (4, 'ISPIRER', 'ispirer'),
        (5, 'ISPIRER', 'IS_IR%R');
        

And we need the following queries to be processed on it exactly the same as in SQL Server after the transfer to PostgreSQL:

   select * from tlike where title = title2;
   select * from tlike where title like title2;
   select * from tlike where title in (title2);

To resolve this issue, we can do one of the following:

1. use the citext type (case-insensitive text). For more details you could read the following documentation: citext — a case-insensitive character string type

To do this, you need to create the extension once in the database:

  create extension citext;

and After that it will be possible to create table columns and variables with the citext type.

In Ispirer Toolkit you can set up type mapping either for the entire database or for each table/field individually.

Usage examples:

  /* citext as column type*/
  create table tlike 
  (
     id integer,
     title citext,
     title2 citext 
  );
  
  insert into tlike values <...>
  
  select * from tlike where title = title2 and id > 0;
  select * from tlike where title ilike title2 and id > 0;
  select * from tlike where title in (title2);

  /* citext as a variable type */
  DO $$
  DECLARE
  title  citext default 'ispirer';
  title2 citext default 'ISPIRER';
  BEGIN
     IF (title = title2 ) THEN
         RAISE NOTICE 'equal';
     END IF;   
     IF (title ilike title2 ) THEN
         RAISE NOTICE 'ilike';
     END IF;   
  END;$$;    

2. You can also add “lower” for each string comparison (variables, table fields, constants).

If you use “=” or “in” to compare strings, then both parts of the comparison should be wrapped in “lower”. And if using “like”, then it will be replaced with “ilike” (as in the previous example).

Usage examples:

  /* compare the column */
  create table tlike 
  (
     id integer,
     title varchar(20),
     title2 varchar(20) 
  );
  
  insert into tlike values <...>
  
  select * from tlike where lower(title) = lower(title2) and id > 0;
  select * from tlike where title ilike title2 and id > 0;
  select * from tlike where lower(title) in (lower(title2));

  /* comparison of variables */
  DO $$
  DECLARE
  title  varchar(20) default 'ispirer';
  title2 varchar(20) default 'ISPIRER';
  BEGIN
     IF (lower(title)  = lower(title2)) THEN
         RAISE NOTICE 'equal';
     END IF;   
     IF (title ilike title2 ) THEN
         RAISE NOTICE 'ilike';
     END IF;   
  END;$$;

3. Alternatively, you can also create your own COLLATIONs and use them as follows:

Explicitly add COLLATION to column types and variables (deterministic = false).

When comparing with like, it will be replaced with ilike and COLLATION (deterministic = true) will be added.

Usage examples:

  CREATE COLLATION IF NOT EXISTS swcol_ci_nondet (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
  CREATE COLLATION IF NOT EXISTS swcol_ci_det (provider = icu, locale = 'und-u-ks-level2', deterministic = true);
  /* compare the column */
  create table tlike 
  (
     id integer,
     title varchar(20)  COLLATE swcol_ci_nondet,
     title2 varchar(20) COLLATE swcol_ci_nondet
  );
  insert into tlike values  <...>
  
  select * from tlike where title = title2 and id > 0;
  select * from tlike where title ilike title2 COLLATE swcol_ci_det and id > 0;
  select * from tlike where title in (title2);

  /* comparison of variables */
  DO $$
  DECLARE
  title  varchar(20) COLLATE swcol_ci_nondet default 'ispirer';
  title2 varchar(20) COLLATE swcol_ci_nondet default 'ISPIRER';
  BEGIN
     IF (title = title2) THEN
         RAISE NOTICE 'equal';
     END IF;   
     IF (title ilike title2 COLLATE swcol_ci_det) THEN
        RAISE NOTICE 'ilike';
     END IF;   
  END;$$;
  

—-

If you have any other questions regarding the usage of our tool, please contact our support team at support@ispirer.com