4 ms·

So there’s some nice things about Postgres, but by far the thing I miss the most when I go back to other systems (incl. MySQL/Maria) is transactional DDL. Knowi

So there’s some nice things about Postgres, but by far the thing I miss the most when I go back to other systems (incl. MySQL/Maria) is transactional DDL. Knowing that a bad migration isn’t going to leave my DB in a half-changed fucked up state is worth it.

pmontra 7y ago
This and window functions and safety of data. I'm using MySQL and PostgreSQL in different projects (customers choice) and sometimes there are things I can't do in the MySQL one or would be much more complicated. Sometimes MySQL silently slips bad data in the db because it truncates strings that don't fit varchars or those impossible 0000-00-00 00:00:00 dates. To be fair, the latter are not much of a problem. I'd go PostgreSQL all the time but I'd be very wary about migrating a production database. It could be long and risky.
- cwyers 7y ago
  MySQL now has window functions: https://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/ https://mysqlserverteam.com/mysql-8-0-2-introducing-window-f... And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
  - zaphar 7y ago
    Strict mode is like 1/4'th of the safety that PostgreSQL gives you. Robust types for columns. Safe transaction semantics. Better tools for enforcing data consistency. MySQL has a long way to go before it can give you the same security for you data that PostgreSQL does.
- tonyarkles 7y ago
  > very wary about migrating a production database. It could be long and risky. Heh, I've only done a few migrations from MySQL to PostgreSQL, and in every case the hardest part has been dealing with bad data in the source DB. It's always been pretty illuminating for the client when they discover how much bad data they actually have in the DB, even though their "ORM Validations" were keeping it "clean" to make up for MySQL's shortfalls.
weberc2 7y ago
DDL?
- rlpb 7y ago
  https://lmgtfy.com/?q=sql+ddl https://lmgtfy.com/?q=sql+ddl
- buckminster 7y ago
  Data definition language - the SQL for defining and updating tables and so on. You want it transactional to enable rollbacks.
- shrimpx 7y ago
  DDL is the sub-language of SQL which alters schema. Eg create table, alter table, drop table are DDL commands.

pmontra 7y ago

This and window functions and safety of data. I'm using MySQL and PostgreSQL in different projects (customers choice) and sometimes there are things I can't do in the MySQL one or would be much more complicated. Sometimes MySQL silently slips bad data in the db because it truncates strings that don't fit varchars or those impossible 0000-00-00 00:00:00 dates. To be fair, the latter are not much of a problem. I'd go PostgreSQL all the time but I'd be very wary about migrating a production database. It could be long and risky.

cwyers 7y ago
MySQL now has window functions: https://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/ https://mysqlserverteam.com/mysql-8-0-2-introducing-window-f... And MySQL has strict mode, which I believe is enabled by default on newer versions but available in any 5.x version: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mo...
- zaphar 7y ago
  Strict mode is like 1/4'th of the safety that PostgreSQL gives you. Robust types for columns. Safe transaction semantics. Better tools for enforcing data consistency. MySQL has a long way to go before it can give you the same security for you data that PostgreSQL does.
tonyarkles 7y ago
> very wary about migrating a production database. It could be long and risky. Heh, I've only done a few migrations from MySQL to PostgreSQL, and in every case the hardest part has been dealing with bad data in the source DB. It's always been pretty illuminating for the client when they discover how much bad data they actually have in the DB, even though their "ORM Validations" were keeping it "clean" to make up for MySQL's shortfalls.

weberc2 7y ago

DDL?

rlpb 7y ago
https://lmgtfy.com/?q=sql+ddl https://lmgtfy.com/?q=sql+ddl
buckminster 7y ago
Data definition language - the SQL for defining and updating tables and so on. You want it transactional to enable rollbacks.
shrimpx 7y ago
DDL is the sub-language of SQL which alters schema. Eg create table, alter table, drop table are DDL commands.