![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```