create account

Switching from MySQL to PostgreSQL by makerhacks

View this thread on: hive.blogpeakd.comecency.com
· @makerhacks · (edited)
$10.34
Switching from MySQL to PostgreSQL
![postgres Background Removed.png](https://files.peakd.com/file/peakd-hive/makerhacks/AK6WSsCSUG5WsiJpfCwfhUWK5psdGbBmdrJQdgza8Var6n8CbbX56d1xULxRgCT.png)


I've never used PostgreSQL from scratch in production before, it's a great system but even so if you're familiar with MySQL and starting a project with PostgreSQL, the transition can feel a bit unfamiliar at first. 

Why would you use it then? Well, in my case it was specified before I joined the project, but actually even though MySQL is plenty sufficient most of the time, Postgres does have a lot of powerful features missing from MySQL, so it is worth at least being familiar with it.


## PostgreSQL vs MySQL: Key Differences

| Feature                        | PostgreSQL                                                                 | MySQL                                                            |
|--------------------------------|-----------------------------------------------------------------------------|------------------------------------------------------------------|
| **Philosophy**                 | Standards-compliant, extensible, correctness-focused                        | Speed-focused, simpler setup                                     |
| **ACID Compliance**            | Fully ACID-compliant by default                                            | ACID only with InnoDB engine                                     |
| **Data Types**                 | Rich: arrays, JSONB, custom types, strict typing                           | Basic: looser typing, simpler types                              |
| **JSON Support**              | Advanced querying with `JSONB` and indexes                                 | Basic JSON support (since 5.7)                                   |
| **Index Types**                | B-tree, GIN, GiST, BRIN, partial indexes, expression indexes                | Mostly B-tree, limited functional index support                  |
| **Stored Procedures**          | PL/pgSQL, Python, Perl, and more                                           | Simpler stored procedures                                        |
| **Extensions**                 | Extensible (e.g. `PostGIS`, `uuid-ossp`)                                   | Less flexible                                                    |
| **Concurrency (MVCC)**         | Excellent MVCC, avoids table-level locks                                   | MVCC via InnoDB, but still locking under load                    |
| **CTEs / Window Functions**    | Fully supported                                                            | Available from MySQL 8.0+                                        |
| **Tooling**                    | `psql`, `pgAdmin`, DBeaver, Postico                                        | `mysql`, Workbench, phpMyAdmin                                   |
| **Licensing**                  | Open source, permissive PostgreSQL license                                 | GPL, owned by Oracle                                             |
| **Replication**                | Streaming + logical replication supported out of the box                   | Mature binlog-based replication                                  |

---

## PostgreSQL vs MySQL SQL Syntax

Here are some differences you'll run into when writing SQL for PostgreSQL after using MySQL.

###  Auto-Increment / Identity

```sql


-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- or (preferred for standards)
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
```

### 'UPSERT':
   
MySQL: ```ON DUPLICATE KEY UPDATE```
PostgreSQL: ```ON CONFLICT (...) DO UPDATE```


## Booleans:
MySQL: ```TINYINT(1)```
PostgreSQL: ```BOOLEAN (TRUE/FALSE)```

## Case sensitivity:
   MySQL: often case-insensitive
   PostgreSQL: case-sensitive, use ```ILIKE``` for insensitive search

   Example:
   ```SELECT * FROM users WHERE name ILIKE 'chris';```

## IF/CASE

```
-- MySQL
SELECT IF(score > 50, 'Pass', 'Fail') FROM exams;

-- PostgreSQL
SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM exams;
```

## Dates

```
-- Current timestamp
SELECT NOW(); -- same in both

-- Extract year
-- MySQL
SELECT YEAR(created_at) FROM orders;

-- PostgreSQL
SELECT EXTRACT(YEAR FROM created_at) FROM orders;

```

## Strings
| Type            | MySQL        | PostgreSQL        |
| --------------- | ------------ | ----------------- |
| String literals | `'text'`     | `'text'`          |
| Identifiers     | `` `name` `` | `"name"` (strict) |

### String Concatenation:
   MySQL: ```CONCAT(a, b)```
   PostgreSQL: ```a || b```
👍  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 191 others
properties (23)
authormakerhacks
permlinkswitching-from-mysql-to-postgresql
categorytechnology
json_metadata"{"app":"peakd/2025.4.6","description":"Cheat sheet for PostgreSQL","format":"markdown","image":["https://files.peakd.com/file/peakd-hive/makerhacks/AK6WSsCSUG5WsiJpfCwfhUWK5psdGbBmdrJQdgza8Var6n8CbbX56d1xULxRgCT.png"],"tags":["technology","developers","programming","python","javascript"],"users":[]}"
created2025-05-07 20:38:42
last_update2025-05-07 20:40:39
depth0
children2
last_payout2025-05-14 20:38:42
cashout_time1969-12-31 23:59:59
total_payout_value5.184 HBD
curator_payout_value5.154 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length4,669
author_reputation156,977,359,570,955
root_title"Switching from MySQL to PostgreSQL"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd0
post_id142,585,049
net_rshares27,913,188,181,771
author_curate_reward""
vote details (255)
@caffeinepixie ·
$0.07
I couldn't wrap my head around PostgreSQL yet and I am not sure why they even invented that 😅 mySQL works just fine and a lot easier to understand. I suppose it's certainly have some extra features than mySQL but for now I am just gonna stick to mySQL 
👍  
properties (23)
authorcaffeinepixie
permlinkre-makerhacks-sw7syy
categorytechnology
json_metadata{"tags":["technology"],"app":"peakd/2025.5.6","image":[],"users":[]}
created2025-05-13 19:33:00
last_update2025-05-13 19:33:00
depth1
children0
last_payout2025-05-20 19:33:00
cashout_time1969-12-31 23:59:59
total_payout_value0.032 HBD
curator_payout_value0.033 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length252
author_reputation44,575,029,233
root_title"Switching from MySQL to PostgreSQL"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id142,709,637
net_rshares191,306,082,666
author_curate_reward""
vote details (1)
@stemsocial ·
re-makerhacks-switching-from-mysql-to-postgresql-20250509t001731175z
<div class='text-justify'> <div class='pull-left'>
 <img src='https://stem.openhive.network/images/stemsocialsupport7.png'> </div>

Thanks for your contribution to the <a href='/trending/hive-196387'>STEMsocial community</a>. Feel free to join us on <a href='https://discord.gg/9c7pKVD'>discord</a> to get to know the rest of us!

Please consider delegating to the @stemsocial account (85% of the curation rewards are returned).

Consider setting @stemsocial as a beneficiary of this post's rewards if you would like to support the community and contribute to its mission of promoting science and education on Hive.&nbsp;<br />&nbsp;<br />
</div>
properties (22)
authorstemsocial
permlinkre-makerhacks-switching-from-mysql-to-postgresql-20250509t001731175z
categorytechnology
json_metadata{"app":"STEMsocial"}
created2025-05-09 00:17:30
last_update2025-05-09 00:17:30
depth1
children0
last_payout2025-05-16 00:17:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length646
author_reputation22,918,491,691,707
root_title"Switching from MySQL to PostgreSQL"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id142,614,527
net_rshares0