MySQL -> PostgreSQL
About me
Stjepan "Štef" Hadjić
Webapps team leader @ http://infinum.co
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
MySQL -> Postgresql
By Stjepan Hadjić
MySQL -> Postgresql
- 1,039