Search code examples
sqlsqlitestandardsstandards-compliancedialect

Does SQLite have any features (other than file-besed-ness) that other SQL flavours do not?

I remember reading somewhere that SQLite follows the standard completely, when possible (as in, switching databases, etc. is not implemented, as well as using dynamic types). One would expect that the other flavors would also follow the standard, and then tack on some extra features.

Are these expectations correct? If so, I would expect that (almost) any valid SQLite code would also be valid PostgreSQL, MySQL, or other SQL flavors as well.

Is this true? Or,

TL;Dr is there any valid SQLite that doesn't use its file-based nature, that isn't valid in other dialects?

Solution

  • I'm not sure if this is the answer you are looking for, but.

    SQLite -- as with every database -- varies from the standard. There are lots of things in SQLite that don't work in other databases. This is highly database-pair centric. But here are some examples:

    • limit doesn't work in SQL Server or Oracle.
    • instr() doesn't work in SQL Server.
    • printf() doesn't work in any other database as far as I know.
    • Nor does likely(), unlikely(), randomblob(), total_changes(), zeroblob(), like(), glob().
    • min() and max() with multiple arguments do not work in any other database, as far as I know.
    • len() doesn't work in Oracle or Postgres.
    • soundex() doesn't work in Postgres.
    • substr() doesn't work in SQL Server.
    • trim() doesn't work in SQL Server.
    • ltrim() and rtrim() don't work in Postgres.

    I decided to stop at 10 (and I apologize for any errors in the above). There are many more examples.

    Code written in SQLite -- or in any database -- simply is not guaranteed to work in any other database, with the exception of closely related database pairs, such as MySQL/MariaDB, SQL Server/Sybase (and they have diverged a lot), and Postgres and a bunch of Postgres-derived databases.