Now that we're all basically over the collective hallucination of a "schemaless" future
LOOL
Postgres actually goes a step further by offering a JSONB type which processes input data into a binary format. This means it's a little bit slower to write, but much faster to query.
JSONB is awesome, I built an entire filtering system on top of it with Rails's Arel (relational algebra engine) to compose standard WHERE filters with filters in JSON columns at runtime. Great performance. And now in PostgreSQL 10 they enabled full text search on JSON columns, can't wait to have a use case to try it!
Unless you're using MySQL, in which case the only function you can reference in a DEFAULT clause is now().
What?! I didn't know they differed on this. I always design the tables putting checks and defaults inside the DB first.
However, MySQL's boolean is actually an alias for TINYINT(1).
Been bitten by this once! It's lol. I still remember back in the day when people used to tell me "I use MySQL because it's faster and easier to install than PostgreSQL". Yeah, if you want to have weird data in the DB (but it's gotten way better than those days), I guess after MySQL 5.5
Inlining arrays makes many tasks -- such as tagging records -- much easier.
Also, they have operators which lets you operate on those columns inside the database, without having to do such operations in the code. The fact that I can ask the DB if the array typed columns contains this other array it's awesome!
There are drawbacks to using enums, to be sure: adding new values requires DDL, and you can't remove values at all. But appropriate use cases for them are still reasonably common.
Yeah, that's more or less why they are seldom used. A lot of times you don't know in advance all the possible enum values so you tend to keep them in the code.
About lateral queries, I didn't know about them! Thanks!
I want to share an horror story: a client that had a MySQL database they used to store data scraped with Python. Everything went well for a while, until it didn't. After a bit of research (I had no experience with the database) I found out that MySQL's default UTF-8 type uses 3 bytes, not 4 which meant that a LOT of text was not going in the database. I was not happy. I couldn't convert the tables so I had to write a function in Python that converted 4 bytes utf-8 to 3 bytes. Insane.
Best post BTW, it's definitely the ultimate comparison.
ps. you really hate tinyints :P
pps. thanks for the link to multicore, i didn't know about that as well!
LOOL
JSONB is awesome, I built an entire filtering system on top of it with Rails's Arel (relational algebra engine) to compose standard WHERE filters with filters in JSON columns at runtime. Great performance. And now in PostgreSQL 10 they enabled full text search on JSON columns, can't wait to have a use case to try it!
What?! I didn't know they differed on this. I always design the tables putting checks and defaults inside the DB first.
Been bitten by this once! It's lol. I still remember back in the day when people used to tell me "I use MySQL because it's faster and easier to install than PostgreSQL". Yeah, if you want to have weird data in the DB (but it's gotten way better than those days), I guess after MySQL 5.5
Also, they have operators which lets you operate on those columns inside the database, without having to do such operations in the code. The fact that I can ask the DB if the array typed columns contains this other array it's awesome!
Yeah, that's more or less why they are seldom used. A lot of times you don't know in advance all the possible enum values so you tend to keep them in the code.
About lateral queries, I didn't know about them! Thanks!
I want to share an horror story: a client that had a MySQL database they used to store data scraped with Python. Everything went well for a while, until it didn't. After a bit of research (I had no experience with the database) I found out that MySQL's default UTF-8 type uses 3 bytes, not 4 which meant that a LOT of text was not going in the database. I was not happy. I couldn't convert the tables so I had to write a function in Python that converted 4 bytes utf-8 to 3 bytes. Insane.
Best post BTW, it's definitely the ultimate comparison.
ps. you really hate tinyints :P
pps. thanks for the link to multicore, i didn't know about that as well!
Speaking of
TINYINT(1), I found a bug in Innodb years ago where this type could not be returned.I'm sure mysql devs were not happy for such design decision, using ints instead of a proper bool...