How to implement case insensitivity in PostgreSQL queries

Aditya Chavan — Thu, 04 Jan 2024 15:54:09 +0000

🚀 Must-read for PostgreSQL enthusiasts!
https://link.medium.com/8LwsRFaz5Fb

How to make postgresql queries case insensitive

Aditya Chavan — Sat, 19 Aug 2023 15:58:54 +0000

Scenarios when we want postgresql to act as case insensitive. We can convert columns to uppercase or lowercase using upper()/lower() to make comparisons case insensitive.
Scenarios where we would need to make such changes -

where conditions
Join conditions
group by — here if put lower/upper on column then that column in select statement should have either lower/upper or other aggregate like max.
order by columns
having conditions
ilike to make like case insensitive
instead of select distinct lower(col1) from… use select distinct on (lower(col1)) col1 from… as it won’t convert value to lower case while being case insensitive.
unique constraints are case sensitive. if using on conflict clause in insert queries then it would be case sensitive. the only solution is to remove the conflict and add extra where condition of “AND NOT EXISTS ( select 1 from … where unique constriants columns wrapped by lower())”. If on conflict () update then add update query before the insert query with extra conditions of lower() wrapped the unique constraint columns and “NOT EXISTS …” in the afterward insert query.

Changes in procedures/functions while migrating from MySQL to PostgreSQL

Aditya Chavan — Sat, 19 Aug 2023 15:42:45 +0000

So, I have listed some of the changes which are needed when migrating our procedures or functions in MySQL to PostgreSQL.

“ ( double quote ) in postgresql don’t signify text but are used to name case sensitive names of columns, tables, etc. as those are case insensitive in postgresql. Instead ‘ ( single quote ) is used for text. for example.

select age from table1 where name = 'harry'; ( postgresql )
select age from table1 where name = "harry"; ( mysql )

common json changes from MySQL to Postgresql include : json_length MySQL = json_array_length convert text variable to json using ::json json_extract = ->> operator json_unquote = trim(‘“’ from abc) we also have json datatype in postgresql
if we are using escape characters in string like \n, \t, etc. “E” should be applied before the string like

E'\t hello \n World'

FIND_IN_SET(value, array) is replaced with

value = ANY(string_to_array(array, ',')::BIGINT[])

“insert ignore” is replaced with “on conflict do nothing”. we can also specify unique columns separately after the conflict in () but not including means all unique constraints are included. Similarly, on duplicate key update is handled by on conflict do update set. EXCLUDED point to the new row for inserting.

insert into t1(s1,s2)
select a,b from t2
on conflict do nothing;

insert into t1(s1,s2)
select a,b from t2
on conflict (any unique columns/columns ) do update set
   s1 = EXCLUDED.a,
   s2 = EXCLUDED.b;

In mysql cursor we declare continue handler with a variable and have a condition where if the variable is equal to 1 we leave the loop
But in postgresql both are not supported and the only change is to attach statement “exit when not found;” after fetching from the cursor.
loops in cursors can’t have names in postgresql
FOUND_ROWS() in replaced by ROW_COUNT.

get diagnostics count = ROW_COUNT;
concat('Inserted records are ', count) into var_output;

for exceptions — declare handler is not supported in postgresql but can use

declare
begin
    statements;
exception
    when condition [or condition...] then
       handle_exception;
   [when condition [or condition...] then
       handle_exception;]
   [when others then
       handle_other_exceptions;
   ]
end;

In delete statement we can delete from the source table only ( unless cascade delete is applied on child tables ). Also, 1st “JOIN” keyword after table is replaced with “USING”. The joining columns of the source table need to be specified in the where clause. for eg

delete from abc 
using def
join efg on def.name = efg.name
where abc.id = def.objectid and abc.col1 = efg.col1;

enum support in postgresql is not provided like in MySQL. All enum columns should be declared as text or varchar.
In update statements, we can update the source table only because table prefix for columns is removed in postgresql. Also, 1st “JOIN” keyword after table is replaced with “FROM”. The joining columns of the source table need to be specified in the where clause. set clause is before the from clause. for eg

update abc a
set 
  col1 = o.name
  col2 = w.type
from def o
join fgh w on o.col3 = w.col3
where 
  a.addressid = o.id
and a.contactid = w.id;

instr( string, sub_string ) is replaced by POSITION(sub_string IN string).
While creating table instead of NOT NULL AUTO_INCREMENT use GENERATED ALWAYS AS IDENTITY
Cannot create index while creating table in postgresql, need to create index explicitly in new SQL statement. Also, cannot create index on variable length of column like col(255)
DROP TEMPORARY TABLE replaced by DROP TABLE. works for both types.
group_concat = string_agg(string, ‘separator’)
In Information_Schema.columns, COLUMN_TYPE is replaced by DATA_TYPE
unique constrainst are created using unique indexes. So, first need the index to create the constraint.
CURDATE() is replaced with CURRENT_DATE
DATE_ADD() is replaced with date_var + anything. [ if anything is integer then days are added if anything is INTERVAL ‘1 WEEK’ then 1 week is added ]

DEV Community: Aditya Chavan

How to implement case insensitivity in PostgreSQL queries

How to make postgresql queries case insensitive

Changes in procedures/functions while migrating from MySQL to PostgreSQL