MySQL -> PostgreSQL

About me

Stjepan "Štef" Hadjić

https://plus.google.com/+StjepanHadjić/posts

Migrations

limit on text fields [4GB vs 1GB]
boolean defaults must be true/false
change_column explicit cast from string to integer/float
can not alter database charset and collate

change_column :table, :some_columns, 'integer USING CAST(some_column AS integer)'

Case sensitivity

MySQL is case insensitive while PG is case sensitve

|id|name|
|--|----|
| 1|stef|

User.where(name: 'Stef')
User.where("name LIKE 'St%'")
User.find_by(username: params[:username])

Quoting

PostgreSQL uses only single quotes for strings
MySQL uses backticks for quoting identifiers whereas PostgreSQL uses double quotes.

INSERT INTO `users` (`id`, `first_name`, `email`)
VALUES (33, 'Asdasd', 'infinum@net.hr');

INSERT INTO "users", ("id", "first_name", "email")
VALUES (33, E'Asdasd', E'infinum@net.hr');

Strict GROUP BY

|id|name|city_name|
|--|----|---------|
| 1|stef|Zagreb   |
| 2|pero|Zagreb   |
| 3|miro|Zadar    |
|--|----|---------|

SELECT * FROM users GROUP BY city_name

|id|name|city_name|
|--|----|---------|
| 3|miro|Zadar    |
| 1|stef|Zagreb   |
|--|----|---------|

MySQL

PostgreSQL

ERROR:  column "users.id" must appear in the 
    GROUP BY clause or be used in an aggregate function
LINE 1: SELECT id, name, city_name FROM users 
    GROUP BY city_name

Default order

MySQL uses 'id asc' as default order

PostgreSQL is random :)

alias columns

PostgreSQL can't use alias columns in anything that is not SELECT

SELECT COUNT(users.id) as employees_count 
FROM users 
GROUP BY users.company 
HAVING employees_count > 5;

SELECT COUNT(users.id) as employees_count 
FROM users 
GROUP BY users.company 
HAVING COUNT(users.id) > 5;

Migrating existing datbases