Write only pure, portable SQL. Using RDBMS extensions is evil. Enligtenment only comes to those who are pure(ly using SQL).
* SQLite is dynamically typed.
* The text types differ in meaning between databases (text, varchar, nvarchar, varchar2, ...). I also beleive char works in different ways in different databases, but I do not use blank padded strings so I am not sure.
* Time types and fucntions are very different.
* Oracle and PostgreSQL run at a different default isolation level than MySQL and MSSQL.
* MSSQL has read locks on rows, which can cause deadlocks which cannot happen in other databases.
* Autoincrement is not implemented in the same way in different databases.
* Text collations and text equality works differently in different datbases. (If they care about trailing blanks, case, etc and the order tuples are sorted in.)
And these are jsut the ones I recall on top of my head.
* Oracle treats the empty string and null as equivalent, PostgreSQL, MySQL and MSSQL treat the empty string and null as distinct.
To paraphrase Tom Kyte from Oracle (Ask Tom): "Your company paid good money for Oracle and all of its features. Use them instead of wasting money reimplementing them poorly yourself."
There are many ways to achieve portability across databases, but limiting yourself to standard SQL is not the best way (at least for everything). And I say this as someone who develops an analytic database (Presto) that tries to follow standard SQL for everything.
This applies just as well to the open source world. You may not have paid money for PostgreSQL, but you did pay for it in that the time you spend maintaining it could be used for other things. Likewise, your time could be spent reinventing its features in your application or just using those features and spending your time building something better for your customers.
