MySQL -> PostgreSQL

About me

Stjepan "Štef" Hadjić

Webapps team leader @ http://infinum.co

https://github.com/d4be4st

https://twitter.com/_Beast_

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

MySQL -> Postgresql

By Stjepan Hadjić

Made with Slides.com

MySQL -> Postgresql

  • 1,051

More from Stjepan Hadjić