Things to be changed as migrating from MySql to PostgreSQL
| MySql | PostgreSQL |
|---|---|
| START TRANSACTION;, COMMIT;, ROLLBACK; | BEGIN;, COMMIT;, ROLLBACK; |
| `id` | “id” |
| reserved keywords xxx | “xxx” |
| “…” | ‘…’ |
| \” | ” |
| \’ | ’’ |
| \n | |
| case insensitive | case sensitive |
| USE my_db | \c my_db or just remove it as it’s not needed |
| _binary | E’\xFFF’ |
| INSERT 1 TO BIT(X) column | INSERT B’1’ TO BIT(X) column |
| INSERT INSERT IGNORE INTO | INSERT INTO … ON CONFLICT ([COLUMN]) DO NOTHING |
| LIMIT 10 OFFSET 20 | OFFSET 20 FETCH FIRST 10 ROWS ONLY; |
| DELETE t1 FROM table_1 t1 JOIN table_2 t2 ON | DELETE FROM table_1 WHERE id NOT IN (SELECT … FROM table_2) |
| LAST_INSERT_ID() | INSERT…RETURNING id |
| a JOIN b JOIN c ON a.id=b.id AND b.id=c.id | a JOIN b ON a.id=b.id JOIN c ON b.id=c.id |
| SELECT COUNT(col_a), col_b FROM … | SELECT COUNT(col_a), col_b FROM … GROUP BY col_b |
| NOW(),CURDATE(),CURTIME(),UNIX_TIMESTAMP() | CURRENT_TIMESTAMP,CURRENT_DATE,CURRENT_TIME, ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP)) |
| IFNULL(expr1, expr2) | COALESCE(expr1, expr2) |
| if(boolean, integer, integer) | CASE WHEN boolean THEN integer ELSE integer END |
| # | – |
| CONCAT(str1, str2) | str1 || str2 |
| || | OR |
| GRANT ALL PRIVILEGES ON db_name.* TO ‘user_name’@’%’; | GRANT ALL PRIVILEGES ON DATABASE db_name TO user_name; |
| Database.initMySqlDb() | Database.initPostgresDb() |
| Remove mysql dependency in POM | add Postgres dependency in POM |