Differences Between MySQL and Postgres

MySQL and Postgres are two of the most popular SQL databases. They have some key differences in syntax and features which can make jumping between the two a bit tricky.

Here are 5 differences that can have impact in your queries, that I faced personally.

1. MySQL is case insensitive, Postgres is case sensitive

- 𝚜𝚎𝚕𝚎𝚌𝚝 𝙸𝙳, 𝙽𝙰𝙼𝙴 𝚏𝚛𝚘𝚖 𝚞𝚜𝚎𝚛𝚜 𝚠𝚑𝚎𝚛𝚎 𝚗𝚊𝚖𝚎 = '𝙹𝚘𝚑𝚗'

- 𝚜𝚎𝚕𝚎𝚌𝚝 𝚒𝚍, 𝚗𝚊𝚖𝚎 𝚏𝚛𝚘𝚖 𝚞𝚜𝚎𝚛𝚜 𝚠𝚑𝚎𝚛𝚎 𝚗𝚊𝚖𝚎 = '𝚓𝚘𝚑𝚗'

- In Postgres, searching for "john" vs "John" will yield different results, whereas in MySQL it will return the same result set

- Also, selecting "ID, NAME" will throw an error in Postgres if the columns are lowercase (which they should be for Postgres)

2. MySQL allows camelCase for identifiers, Postgres does not

- select dob as 𝚍𝚊𝚝𝚎𝙾𝚏𝙱𝚒𝚛𝚝𝚑 from users where id = 1

- This query will return the dataset with the 𝚍𝚊𝚝𝚎𝙾𝚏𝙱𝚒𝚛𝚝𝚑 column for MySQL, but 𝚍𝚊𝚝𝚎𝚘𝚏𝚋𝚒𝚛𝚝𝚑 for Postgres. Be careful when mapping raw query results to objects in code.

3. MySQL allows ON UPDATE to auto-update column values, but Postgres does not

- For columns like updated_at, in MySQL we can simply declare the table definition as “𝚌𝚘𝚕𝚞𝚖𝚗 𝚞𝚙𝚍𝚊𝚝𝚎𝚍_𝚊𝚝 𝚍𝚊𝚝𝚎𝚝𝚒𝚖𝚎 𝚘𝚗 𝚞𝚙𝚍𝚊𝚝𝚎 𝚌𝚞𝚛𝚛𝚎𝚗𝚝_𝚝𝚒𝚖𝚎𝚜𝚝𝚊𝚖𝚙”

- In postgres, you have to create a separate trigger on the table to achieve the same thing

4. MySQL allows the use of boolean column values interchangeably between 1/0 and TRUE/FALSE, while Postgres only uses TRUE/FALSE values.

- For columns like updated_at, in MySQL we can simply declare the table definition as “column updated_at datetime on update current_timestamp”

- In Postgres, you have to create a separate trigger on the table to achieve the same thing

    - select * from users where is_active = 1

    - let’s say is_active is a boolean column. this query will work in mysql but not in postgres

5. MySQL allows bulk-updating multiple rows for a partial set of columns, while for Postgres you must pass the whole row’s values

- In MySQL you can do something like this, where you can update columns for multiple records by the primary ID (or unique constraint), while keeping other columns intact.

  𝙸𝙽𝚂𝙴𝚁𝚃 𝙸𝙽𝚃𝙾 𝚝𝟷 (𝚒𝚍,𝚗𝚊𝚖𝚎) 𝚅𝙰𝙻𝚄𝙴𝚂 (𝟷,'𝙹𝚘𝚑𝚗'),(𝟸,'𝙹𝚊𝚗𝚎') 𝙰𝚂 𝚗𝚎𝚠

  𝙾𝙽 𝙳𝚄𝙿𝙻𝙸𝙲𝙰𝚃𝙴 𝙺𝙴𝚈 𝚄𝙿𝙳𝙰𝚃𝙴 𝚗𝚊𝚖𝚎 = 𝚗𝚎𝚠.𝚗𝚊𝚖𝚎;

- For Postgres there is a similar "𝙾𝙽 𝙲𝙾𝙽𝙵𝙻𝙸𝙲𝚃 (𝚒𝚍) 𝙳𝙾 𝚄𝙿𝙳𝙰𝚃𝙴” statement, but for that you cannot just have “id,name”, you will need to pass all the columns of the user table for the bulk-update to work.

1. MySQL is case insensitive, Postgres is not

2. MySQL allows camelCase identifiers, Postgres does not

3. MySQL allows "ON UPDATE" for columns, Postgres needs a trigger for the same

4. MySQL allows 1/0 and TRUE/FALSE for Boolean, while Postgres only allows 1/0

5. MySQL allows bulk-updating a partial set of columns for multiple records, Postgres does not

6. Postgres has no autoincrement. Mysql allows for an autoincrement on PRIMARY KEY.

MySQL also has a 'heartbeat' for easy replication/synchronization. Postgres does not.