Starting with SQLite version 3.38.0 (released on 22 February 2022), we can now use the -> and ->> operators to extract subcomponents of JSON documents.
The aim with these operators is to be compatible with the equivalent MySQL and PostgreSQL operators.
Continue readingMySQL and MariaDB have a SHOW TABLES statement, which outputs a list of tables and views in a database. PostgreSQL doesn’t have a SHOW TABLES statement, but it does have a command that produces a similar result.
In Postgres, you can use the \dt command to show a list of tables. This is a psql command (psql is the interactive terminal for PostgreSQL).
In SQLite, substring() is an alias for substr().
It returns a substring from a string, based on a given starting location within the string. Two arguments are required, and a third optional argument is accepted.
The substring() naming was introduced in SQLite 3.34.0, which was released on 1st December 2020. The reason that the substring() syntax was introduced was for compatibility with SQL Server.
SQLite has introduced the FORMAT() function, which can be used to format strings.
More precisely, it has actually just renamed its SQL PRINTF() function to FORMAT(). The reason is for better compatibility with other DBMSs. The original PRINTF() name is retained as an alias for backwards compatibility.
The FORMAT() function (or its naming) was introduced in SQLite 3.38.0, which was released on 22 February 2022.
SQLite doesn’t have a SHOW TABLES statement like MySQL and MariaDB have, but it does have a similar command.
In SQLite, you can use the .tables command to show a list of tables. You can alternatively use the table_list pragma to do the job.
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
SQLite doesn’t have a DATEADD() function like SQL Server (or an ADDDATE() or ADD_DATE() function like in some other DBMSs), but it does have the DATE() function that can make additions to a date value.
SQLite also has a DATETIME() function that can make additions to a datetime value (as well as a TIME() function for time values).
In SQL Server, VALUES is a table value constructor that specifies a set of row value expressions to be constructed into a table.
The VALUES clause is often used with INSERT statements to insert data, but it can also be used as a derived table in either the USING clause of the MERGE statement or the FROM clause.
Updated 20 April 2024 to include the date_add() function.
SQL Server has its DATEADD() function that adds an interval to a date value. MySQL’s DATE_ADD() and ADDDATE() for does the same thing, as does MariaDB’s DATE_ADD() and ADDDATE(). SQLite has a DATE() function that also provides the option of adding an interval to a given date.
Prior to version 16, PostgreSQL didn’t have a DATEADD() or equivalent function. But with PostgreSQL 16 came with the introduction of the date_add() function, which allows us to add an interval to a timestamp with time zone.
We can also add and subtract values from dates with date/time operators such as + and -.
In PostgreSQL, the VALUES command returns a set of one or more rows as a table. It’s a table value constructor that can be used as part of a larger command, or as a standalone SQL statement.