Changes in procedures/functions while migrating from MySQL to PostgreSQL

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 ]