-1

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?

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
Mark Gardner
  • 442
  • 1
  • 6
  • 18
  • There is a lot of overlap among databases, but there are a lot of differences too. One of the main differences with SQLite is the data types. However, it also has a much more limited set of built-in functions and other differences as well. – Gordon Linoff Jan 18 '20 at 13:00
  • If the down-votes are for lack of research, I have no idea how to go about researching something like this. – Mark Gardner Jan 18 '20 at 13:00
  • @GordonLinoff I know it doesn't have a lot of stuff that others have, I'm asking if it has stuff others don't. – Mark Gardner Jan 18 '20 at 13:01

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786