In Postgres, the age() function returns the age based on two timestamp or date variables.
The result is returned using years and months, rather than just days.
For example, a result might look like this: 3 years 2 mons 30 days.
Continue readingTag: what is
How to_char() Works in PostgreSQL
In Postgres, to_char() is a data type formatting function that converts its first argument to a string.
The format of the string is determined by the second argument.
The to_char() function can be used to do the following conversions:
- time stamp to string
- interval to string
- integer to string
- real/double precision to string
- numeric to string
How iif() Works in SQLite
In SQLite, iif() is a conditional function that returns the second or third argument based on the evaluation of the first argument.
It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END.
iif() is an abbreviation for Immediate IF.
The iif() function was introduced in SQLite 3.32.0, which was released on 22 May 2020.
What is a Generated Column?
Some database management systems (DBMSs) include a feature called generated columns.
Also known as “computed columns”, generated columns are similar to a normal column, except that a generated column’s value is derived from an expression that computes values from other columns.
In other words, a generated column’s value is computed from other columns.
Continue readingHow ON CONFLICT Works in SQLite
SQLite has the ON CONFLICT clause that allows you to specify how to handle constraint conflicts. It applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints (but not FOREIGN KEY constraints).
There are five possible options you can use with this clause:
ABORTFAILIGNOREREPLACEROLLBACK
This article provides examples and an explanation of each of these options.
The ON CONFLICT clause is used in CREATE TABLE statements, but it can also be used when inserting or updating data by replacing ON CONFLICT with OR.
What is SQLite?
SQLite is a widely-used lightweight, embedded relational database management system (RDBMS).
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is largely SQL standards compliant, and it is fully ACID compliant.
Continue readingWhat is a CHECK Constraint?
In database terms, a CHECK constraint is a type of constraint that checks data before it enters the database.
CHECK constraints help maintain data integrity, because they prevent invalid data entering the database.
Continue readingHow AUTOINCREMENT Works in SQLite
In SQLite, an AUTOINCREMENT column is one that uses an automatically incremented value for each row that’s inserted into the table.
There are a couple of ways you can create an AUTOINCREMENT column:
- You can create it implicitly when you define the column as
INTEGER PRIMARY KEY. - You can create it explicitly with the
AUTOINCREMENTkeyword. One downside of this method is that it uses extra CPU, memory, disk space, and disk I/O overhead.
Both methods cause the column to use an incrementing value each time a new row is inserted with NULL in that column.
However, there are some subtle differences between how each method works.
Continue readingIntroduction to Temporary Tables in SQL Server
In SQL Server, a temporary table is a certain kind of table that exists until goes out of scope (unless it’s explicitly dropped).
This is different to a regular (persistent) table, where the regular table exists permanently in your database until you explicitly drop it.
What is @@SERVICENAME in SQL Server?
In SQL Server, the @@SERVICENAME configuration function returns the name of the registry key under which SQL Server is running.
No argument is required. You can simply use it in a SELECT statement to return the registry key’s name.
Note that SQL Server runs as a service named MSSQLServer. The @@SERVICENAME function returns MSSQLSERVER if the current instance is the default instance. It returns the instance name if the current instance is a named instance.