 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```
author | makerhacks |
---|---|
permlink | switching-from-mysql-to-postgresql |
category | technology |
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":[]}" |
created | 2025-05-07 20:38:42 |
last_update | 2025-05-07 20:40:39 |
depth | 0 |
children | 2 |
last_payout | 2025-05-14 20:38:42 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 5.184 HBD |
curator_payout_value | 5.154 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 4,669 |
author_reputation | 156,977,359,570,955 |
root_title | "Switching from MySQL to PostgreSQL" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 0 |
post_id | 142,585,049 |
net_rshares | 27,913,188,181,771 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
kevinwong | 0 | 1,346,821,736 | 0.6% | ||
eric-boucher | 0 | 3,255,940,256 | 0.6% | ||
roelandp | 0 | 58,901,745,748 | 5% | ||
cloh76 | 0 | 806,677,590 | 0.6% | ||
meesterboom | 0 | 228,150,943,279 | 5% | ||
sazbird | 0 | 845,923,774 | 5% | ||
sunshine | 0 | 28,895,053,017 | 5% | ||
lemouth | 0 | 308,244,251,612 | 10% | ||
wisbeech | 0 | 534,326,497 | 5% | ||
steevc | 0 | 330,798,666,621 | 10% | ||
someguy123 | 0 | 3,747,480,790 | 0.6% | ||
jacobtothe | 0 | 600,667,235,553 | 50% | ||
tfeldman | 0 | 1,106,225,761 | 0.6% | ||
metabs | 0 | 1,134,249,326 | 10% | ||
mcsvi | 0 | 103,223,708,004 | 50% | ||
boxcarblue | 0 | 3,286,658,445 | 0.6% | ||
justyy | 0 | 9,743,063,123 | 1.2% | ||
michelle.gent | 0 | 720,813,824 | 0.24% | ||
curie | 0 | 74,258,619,009 | 1.2% | ||
modernzorker | 0 | 576,211,840 | 0.84% | ||
techslut | 0 | 26,232,288,068 | 4% | ||
slider2990 | 0 | 4,683,454,363 | 20% | ||
steemstem | 0 | 183,362,449,658 | 10% | ||
yadamaniart | 0 | 972,811,220 | 0.6% | ||
walterjay | 0 | 122,136,558,130 | 5% | ||
valth | 0 | 874,329,368 | 5% | ||
metroair | 0 | 6,513,337,211 | 1.2% | ||
darth-azrael | 0 | 32,658,590,195 | 10% | ||
driptorchpress | 0 | 463,595,541 | 0.3% | ||
dna-replication | 0 | 342,173,905 | 10% | ||
darth-cryptic | 0 | 6,217,469,089 | 10% | ||
borislavzlatanov | 0 | 17,562,036,797 | 100% | ||
dhimmel | 0 | 5,559,968,872 | 2.5% | ||
freebornsociety | 0 | 2,934,532,167 | 5.07% | ||
detlev | 0 | 7,808,591,734 | 0.36% | ||
dune69 | 0 | 968,810,179 | 1.2% | ||
gamersclassified | 0 | 930,533,523 | 0.6% | ||
iansart | 0 | 2,387,366,179 | 50% | ||
forykw | 0 | 4,183,809,904 | 0.6% | ||
mobbs | 0 | 3,660,276,239 | 1.2% | ||
eliel | 0 | 869,496,963 | 1.2% | ||
jerrybanfield | 0 | 4,221,642,610 | 1.2% | ||
bitrocker2020 | 0 | 2,562,815,733 | 0.24% | ||
ohamdache | 0 | 737,542,004 | 0.6% | ||
helo | 0 | 8,499,315,366 | 5% | ||
arunava | 0 | 3,463,478,197 | 0.48% | ||
juancar347 | 0 | 4,919,444,506 | 0.6% | ||
samminator | 0 | 5,540,007,855 | 5% | ||
anacristinasilva | 0 | 30,981,569,018 | 100% | ||
enjar | 0 | 12,483,617,940 | 1.08% | ||
lorenzor | 0 | 1,236,268,593 | 50% | ||
sam99 | 0 | 12,979,737,456 | 21% | ||
alexander.alexis | 0 | 6,887,106,941 | 10% | ||
princessmewmew | 0 | 1,560,267,128 | 0.6% | ||
gunthertopp | 0 | 12,023,469,521 | 0.24% | ||
livinguktaiwan | 0 | 326,704,730,387 | 8% | ||
pipiczech | 0 | 510,432,733 | 1.2% | ||
empath | 0 | 1,692,409,523 | 1.02% | ||
howo | 0 | 162,320,734,893 | 10% | ||
tsoldovieri | 0 | 1,027,394,725 | 5% | ||
neumannsalva | 0 | 1,077,950,573 | 0.6% | ||
stayoutoftherz | 0 | 40,534,623,684 | 0.3% | ||
abigail-dantes | 0 | 3,778,809,060 | 10% | ||
coindevil | 0 | 631,711,212 | 0.96% | ||
investingpennies | 0 | 3,530,646,841 | 1.2% | ||
martibis | 0 | 1,252,257,040 | 8% | ||
val.halla | 0 | 2,869,692,434 | 10% | ||
vimukthi | 0 | 1,548,169,598 | 20% | ||
shanibeer | 0 | 139,313,523,338 | 5% | ||
iamphysical | 0 | 21,908,672,874 | 90% | ||
azulear | 0 | 668,115,023 | 100% | ||
psicoluigi | 0 | 768,626,021 | 50% | ||
rocky1 | 0 | 168,334,600,673 | 0.18% | ||
aidefr | 0 | 1,148,741,636 | 5% | ||
buttcoins | 0 | 8,961,076,358 | 0.24% | ||
enzor | 0 | 579,367,297 | 10% | ||
bartosz546 | 0 | 561,183,438 | 0.6% | ||
sunsea | 0 | 1,502,113,815 | 0.6% | ||
bluefinstudios | 0 | 994,878,143 | 0.36% | ||
steveconnor | 0 | 1,112,473,671 | 0.6% | ||
aboutcoolscience | 0 | 3,195,022,565 | 10% | ||
citizensmith | 0 | 1,677,128,219 | 5% | ||
mytechtrail | 0 | 18,851,654,904 | 15% | ||
b00m | 0 | 2,648,624,800 | 5% | ||
kenadis | 0 | 2,602,012,239 | 10% | ||
madridbg | 0 | 2,342,018,706 | 10% | ||
robotics101 | 0 | 3,082,712,955 | 10% | ||
sco | 0 | 3,135,809,380 | 10% | ||
ennyta | 0 | 904,126,108 | 50% | ||
deepresearch | 0 | 865,562,729,998 | 22% | ||
juecoree | 0 | 636,247,861 | 7% | ||
gabrielatravels | 0 | 826,996,206 | 0.42% | ||
hetty-rowan | 0 | 437,014,627 | 0.6% | ||
ydavgonzalez | 0 | 4,775,888,454 | 10% | ||
intrepidphotos | 0 | 2,670,610,691 | 7.5% | ||
fineartnow | 0 | 879,963,133 | 0.6% | ||
aiziqi | 0 | 1,086,684,004 | 5% | ||
steemvault | 0 | 487,559,774 | 1.2% | ||
utube | 0 | 850,012,574 | 1.2% | ||
edicted | 0 | 7,762,306,810,836 | 100% | ||
piotrgrafik | 0 | 1,032,126,424,281 | 80% | ||
m1alsan | 0 | 1,046,089,151 | 1.2% | ||
neneandy | 0 | 1,344,198,148 | 1.2% | ||
marc-allaria | 0 | 492,190,848 | 0.6% | ||
sportscontest | 0 | 1,205,692,312 | 1.2% | ||
steemflagrewards | 0 | 386,174,381,492 | 100% | ||
miguelangel2801 | 0 | 724,316,433 | 50% | ||
mproxima | 0 | 800,668,804 | 0.6% | ||
fantasycrypto | 0 | 861,175,602 | 1.2% | ||
upfundme | 0 | 1,075,431,161 | 3.14% | ||
emiliomoron | 0 | 883,580,816 | 5% | ||
photohunt | 0 | 692,046,463 | 1.2% | ||
geopolis | 0 | 614,544,325 | 10% | ||
robertbira | 0 | 1,035,981,885 | 2.5% | ||
alexdory | 0 | 1,815,162,850 | 10% | ||
takowi | 0 | 25,048,619,497 | 1.2% | ||
irgendwo | 0 | 3,687,031,468 | 1.2% | ||
melvin7 | 0 | 2,024,994,739 | 0.6% | ||
francostem | 0 | 1,326,300,002 | 10% | ||
endopediatria | 0 | 669,688,197 | 20% | ||
chrislybear | 0 | 1,440,950,641 | 0.6% | ||
zipporah | 0 | 582,314,419 | 0.24% | ||
superlotto | 0 | 1,214,239,694 | 1.2% | ||
oadissin | 0 | 2,043,615,128 | 0.36% | ||
tomastonyperez | 0 | 15,647,783,948 | 50% | ||
bil.prag | 0 | 591,564,647 | 0.06% | ||
vcclothing | 0 | 617,360,833 | 0.36% | ||
elvigia | 0 | 10,152,467,656 | 50% | ||
sanderjansenart | 0 | 1,236,202,790 | 0.6% | ||
greddyforce | 0 | 910,419,761 | 0.44% | ||
gadrian | 0 | 89,030,847,250 | 6% | ||
therising | 0 | 23,695,479,993 | 1.2% | ||
de-stem | 0 | 5,385,597,401 | 9.9% | ||
josedelacruz | 0 | 4,785,976,267 | 50% | ||
softa | 0 | 913,273,885 | 0.24% | ||
erickyoussif | 0 | 597,788,991 | 100% | ||
deholt | 0 | 525,968,594 | 8.5% | ||
minerthreat | 0 | 871,070,218 | 0.6% | ||
nateaguila | 0 | 59,885,826,213 | 5% | ||
temitayo-pelumi | 0 | 935,919,303 | 10% | ||
andrick | 0 | 787,863,397 | 50% | ||
doctor-cog-diss | 0 | 9,681,979,140 | 10% | ||
musicvoter2 | 0 | 734,168,536 | 1% | ||
acont | 0 | 1,420,065,027 | 50% | ||
uche-nna | 0 | 1,667,499,922 | 0.96% | ||
cheese4ead | 0 | 926,179,193 | 0.6% | ||
bingbabe | 0 | 2,205,621,075 | 100% | ||
nattybongo | 0 | 3,569,221,752 | 10% | ||
talentclub | 0 | 738,906,590 | 0.6% | ||
bflanagin | 0 | 456,836,809 | 0.6% | ||
armandosodano | 0 | 1,299,771,220 | 0.6% | ||
dalz | 0 | 1,794,097,618,274 | 100% | ||
kylealex | 0 | 5,123,842,972 | 10% | ||
pewsplosions | 0 | 139,472,051,199 | 100% | ||
fran.frey | 0 | 3,849,154,723 | 50% | ||
bagpuss | 0 | 654,273,649 | 5% | ||
chops.support | 0 | 15,576,255,977 | 100% | ||
pboulet | 0 | 16,727,659,345 | 8% | ||
stem-espanol | 0 | 2,241,487,818 | 100% | ||
cliffagreen | 0 | 5,631,188,223 | 10% | ||
aleestra | 0 | 14,319,160,009 | 80% | ||
the.success.club | 0 | 734,261,071 | 0.6% | ||
admiralbot | 0 | 7,174,558,561 | 100% | ||
giulyfarci52 | 0 | 1,571,066,524 | 50% | ||
kristall97 | 0 | 685,573,419 | 100% | ||
multifacetas | 0 | 741,825,747 | 0.6% | ||
cakemonster | 0 | 622,895,764 | 1.2% | ||
stem.witness | 0 | 559,373,451 | 10% | ||
dismayedworld | 0 | 1,246,990,862 | 100% | ||
retrodroid | 0 | 3,365,966,821 | 10% | ||
steemstorage | 0 | 1,526,621,058 | 1.2% | ||
aqua.nano | 0 | 588,469,402 | 100% | ||
anonsteve | 0 | 6,618,032,356 | 50% | ||
steemtelly | 0 | 2,204,225,605 | 5.93% | ||
hairgistix | 0 | 690,622,147 | 0.6% | ||
littlesorceress | 0 | 1,191,764,140 | 1.2% | ||
cryptofiloz | 0 | 1,905,044,924 | 1.2% | ||
dawnoner | 0 | 456,175,071 | 0.12% | ||
memehub | 0 | 105,905,284,507 | 100% | ||
wanker | 0 | 6,016,374,098 | 100% | ||
qwerrie | 0 | 1,337,578,375 | 0.09% | ||
aninsidejob | 0 | 7,596,399,968 | 100% | ||
leighscotford | 0 | 755,113,244 | 1.5% | ||
zeruxanime | 0 | 995,386,845 | 5% | ||
kittykate | 0 | 103,123,876,311 | 100% | ||
tiffin | 0 | 1,078,018,811 | 1.2% | ||
reggaesteem | 0 | 494,322,375 | 5% | ||
khalstem | 0 | 1,900,193,902 | 100% | ||
steemstem-trig | 0 | 162,534,398 | 10% | ||
baltai | 0 | 2,827,587,232 | 1.04% | ||
bozz.sports | 0 | 6,714,741,121 | 4% | ||
ibt-survival | 0 | 35,536,693,918 | 10% | ||
gloriaolar | 0 | 32,519,943,907 | 30% | ||
grindle | 0 | 304,707,718,751 | 50% | ||
hive-199963 | 0 | 1,106,352,791 | 1.2% | ||
brofund | 0 | 2,251,626,447 | 4.19% | ||
monica-ene | 0 | 475,041,565 | 0.6% | ||
stemsocial | 0 | 82,017,826,726 | 10% | ||
fabulousfurlough | 0 | 1,179,673,011 | 100% | ||
holoferncro | 0 | 1,938,183,580 | 5% | ||
the100 | 0 | 1,071,531,316 | 0.6% | ||
hextech | 0 | 327,360,868,246 | 100% | ||
kiemurainen | 0 | 1,630,838,122 | 0.5% | ||
noelyss | 0 | 2,178,979,676 | 5% | ||
ykroys | 0 | 499,533,345 | 100% | ||
usainvote | 0 | 10,718,488,496,930 | 50% | ||
altleft | 0 | 5,216,013,158 | 0.01% | ||
meritocracy | 0 | 16,032,974,083 | 0.12% | ||
dcrops | 0 | 4,623,281,541 | 0.6% | ||
szukamnemo | 0 | 37,043,764,378 | 11% | ||
whywhy | 0 | 517,688,449 | 0.33% | ||
yozen | 0 | 1,703,876,974 | 0.6% | ||
tawadak24 | 0 | 938,934,196 | 0.6% | ||
brofi | 0 | 115,989,451,017 | 4.19% | ||
failingforwards | 0 | 721,506,701 | 0.6% | ||
nfttunz | 0 | 36,486,816,771 | 2.09% | ||
okluvmee | 0 | 812,933,673 | 0.6% | ||
brofund-witness | 0 | 1,186,100,915 | 4.19% | ||
merit.ahama | 0 | 901,729,370 | 0.36% | ||
holovision.cash | 0 | 3,546,526,296 | 100% | ||
seinkalar | 0 | 7,339,574,738 | 1.2% | ||
aries90 | 0 | 11,387,464,121 | 1.2% | ||
blingit | 0 | 828,267,145 | 0.6% | ||
beardoin | 0 | 1,930,892,056 | 4.19% | ||
techguard | 0 | 732,058,752 | 11% | ||
newilluminati | 0 | 3,566,443,369 | 0.6% | ||
vickoly | 0 | 635,942,075 | 0.6% | ||
lukasbachofner | 0 | 1,029,445,599 | 0.6% | ||
surrealis | 0 | 54,171,992,986 | 100% | ||
belug | 0 | 1,585,003,300 | 0.36% | ||
bings-cards | 0 | 980,046,633 | 100% | ||
hk-curation | 0 | 1,122,168,960 | 0.84% | ||
becca-mac | 0 | 45,863,088,070 | 20% | ||
inibless | 0 | 1,171,763,159 | 5% | ||
callmesmile | 0 | 705,000,288 | 0.6% | ||
justfavour | 0 | 969,218,680 | 0.6% | ||
jijisaurart | 0 | 547,018,778 | 0.6% | ||
celestegray | 0 | 11,095,090,792 | 20% | ||
wasined | 0 | 3,717,323,207 | 1.2% | ||
clpacksperiment | 0 | 605,445,660 | 0.6% | ||
ambicrypto | 0 | 30,943,512,319 | 1.2% | ||
humbe | 0 | 6,868,639,867 | 2% | ||
ninjakitten | 0 | 16,291,139,941 | 100% | ||
argo8 | 0 | 1,381,217,566 | 0.6% | ||
rhemagames | 0 | 1,138,253,955 | 0.6% | ||
dailydab | 0 | 74,783,726,638 | 2.09% | ||
kathrynkw | 0 | 3,926,086,477 | 8% | ||
clubvote | 0 | 1,165,680,504 | 0.41% | ||
lolz.byte | 0 | 0 | 100% | ||
profwhitetower | 0 | 2,106,568,792 | 5% | ||
dab-vote | 0 | 27,729,419,624 | 2.09% | ||
soundminds | 0 | 1,757,773,504 | 10% | ||
plonketypiano | 0 | 6,413,648,233 | 100% | ||
magic.byte | 0 | 0 | 100% | ||
arka1 | 0 | 1,373,550,062 | 0.6% |
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
author | caffeinepixie |
---|---|
permlink | re-makerhacks-sw7syy |
category | technology |
json_metadata | {"tags":["technology"],"app":"peakd/2025.5.6","image":[],"users":[]} |
created | 2025-05-13 19:33:00 |
last_update | 2025-05-13 19:33:00 |
depth | 1 |
children | 0 |
last_payout | 2025-05-20 19:33:00 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.032 HBD |
curator_payout_value | 0.033 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 252 |
author_reputation | 44,575,029,233 |
root_title | "Switching from MySQL to PostgreSQL" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 10,000 |
post_id | 142,709,637 |
net_rshares | 191,306,082,666 |
author_curate_reward | "" |
voter | weight | wgt% | rshares | pct | time |
---|---|---|---|---|---|
makerhacks | 0 | 191,306,082,666 | 100% |
<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. <br /> <br /> </div>
author | stemsocial |
---|---|
permlink | re-makerhacks-switching-from-mysql-to-postgresql-20250509t001731175z |
category | technology |
json_metadata | {"app":"STEMsocial"} |
created | 2025-05-09 00:17:30 |
last_update | 2025-05-09 00:17:30 |
depth | 1 |
children | 0 |
last_payout | 2025-05-16 00:17:30 |
cashout_time | 1969-12-31 23:59:59 |
total_payout_value | 0.000 HBD |
curator_payout_value | 0.000 HBD |
pending_payout_value | 0.000 HBD |
promoted | 0.000 HBD |
body_length | 646 |
author_reputation | 22,918,491,691,707 |
root_title | "Switching from MySQL to PostgreSQL" |
beneficiaries | [] |
max_accepted_payout | 1,000,000.000 HBD |
percent_hbd | 10,000 |
post_id | 142,614,527 |
net_rshares | 0 |