I have faced this type of problem long ago.
It’s not a walk.
All sql queries where aggregate data is used should be changed.
In the same tables it is a bit pricy to use autoincrement.
Cast in Postgree does not work very well for text fields that contain not just numbers
The triggers are completely different
Example MYSQL order | Postgree order with CAST
// Mysql
order by righe.datarif, cast(teste.protocollo AS decimal(10,3));
// Postgree
order by teste.datarif, f_ordnattxt(teste.protocollo),f_ordnatnum(teste.protocollo);
Temp Table
// Mysql
CREATE TEMPORARY TABLE
// Postgree
CREATE OR REPLACE TEMP VIEW
Obtain last autoincrement
// Mysql
SELECT LAST_INSERT_ID();
// Postgree
SELECT last_value from righescontrini_rowid;
Select Distinct for a numeric/text field
// Mysql
select distinct cespiticatministeriali.gruppo, cespiticatministeriali.gruppodescrizione
from cespiticatministeriali
order by cast(cespiticatministeriali.gruppo as decimal(10,3));
// Postgree
select distinct cespiticatministeriali.gruppo, cespiticatministeriali.gruppodescrizione,
f_ordnattxt(cespiticatministeriali.gruppo) as ord1,
f_ordnatnum(cespiticatministeriali.gruppo) as ord2
from cespiticatministeriali
order by f_ordnattxt(cespiticatministeriali.gruppo),f_ordnatnum(cespiticatministeriali.gruppo);
Select distinct other form
// Mysql
SELECT DISTINCT articoli.rowid, articoli.stato, articoli.codice, articoli.descrizione, articoli.alias,
// Postgree
SELECT DISTINCT ON (articoli.codice) articoli.rowid, articoli.stato, articoli.codice, articoli.descrizione, articoli.alias,
This is a bit different, apart from the function ‘f_ordnatnum’ and ‘f_ordnattxt’ that I can not retrieve at this time.
I still think I can post it in the afternoon.
NB: Remember not convert data with tools, manually create your table and use ‘serial’ for autoincrement field.