Tag: what is

SQLite JSON()

Posted on by Ian

In SQLite, the json() function converts raw text that looks like JSON into actual JSON.

We pass a JSON string as an argument when we call the function. The json() function then checks that the argument is a valid JSON string and returns a minified version of that JSON string. If the argument is not a well-formed JSON string, then an error is thrown.

However, the json() function was not designed to test whether or not a value is valid JSON. To do that, use the json_valid() function instead.

Continue reading →

PostgreSQL SHOW TABLES Equivalent (psql)

Posted on by Ian

MySQL 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).

Continue reading →

SQLite SUBSTRING() Explained

Posted on by Ian

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.

Continue reading →

SQLite Renames PRINTF() to FORMAT()

Posted on by Ian

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.

Continue reading →

PostgreSQL DATEADD() Equivalent

Posted on by Ian

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 -.

Continue reading →