I don't understand the argument against this. I have had even small sites behave differently when moving between Postgres and SQLite. I <3 SQLite but it's not a golden hammer.
The problem is that it is not jsut the extensions that differ, it is also quite fundamental parts.
* 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.
Unfortunately, limiting yourself to only what is available in standard SQL is not practical. For example, doing idempotent inserts, insert-or-update, sequences, transactional behavior for DDL, data types, date/time manipulation, etc.
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.
> 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."
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.
We aren't discussing religion, we are discussing software development. There is no need to eschew extensions if that's the only database your company uses.
Write only pure, portable SQL. Using RDBMS extensions is evil. Enligtenment only comes to those who are pure(ly using SQL).