While deciding on the database platform to use for a recent project, I noted a couple of major differences between two major players.
Below are some notes I made while comparing MySQL with PostgreSQL.
NOTE: I’m coming from an Oracle background, hence the reference/fall-back to the Oracle ways. Please don’t hold that against me.
- There is no “before insert” trigger. It is checked AFTER the fact, so for not null constraints they are checked AFTER an insert is attempted. This means that triggers that should populate not null fields when a record is inserted with a null value is redundant and will not work.
- No pl/sql… or equivalent
- There isn’t an equivalent option to load data from a CSV file like ‘load data’ does for MySQL
- Dates work like Oracle, e.g. to_char and to_date
- There is PL/SQL, in the form of PL/pgSQL
- Netbeans could not execute a function containing dollar quoted strings (I tried the same driver as dbVisualizer, which worked)