In PostgreSQL, you can get the day name from a date by using the to_char() function. This function returns a string based on the timestamp and the template pattern you provide as arguments..
Get the Short Month Name in PostgreSQL
In PostgreSQL, you can use the to_char() function to get the short month name from a date.
By “short month name” I mean the abbreviated month name, for example Jan, Feb, Mar, etc.
The to_char() function accepts two parameters; the date and the format string with which to format that date.
Set a Default Value for a Column in SQLite: DEFAULT Constraint
When creating a table in SQLite, you have the option of adding constraints to each column.
One such constraint is the DEFAULT constraint.
The DEFAULT constraint allows you to specify a value to be used in the event no value is supplied for that column when a new row is inserted.
If you don’t use a DEFAULT clause, then the default value for a column is NULL.
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.
Add a Generated Column to a Table in SQLite
You can add a generated column to an existing table in SQLite by using the ALTER TABLE statement.
SQLite’s implementation of the ALTER TABLE statement is very limited, but it does allow you to add a column – including generated columns.
Generated columns (also known as “computed columns”) are columns that get their value from an expression that computes values from other columns.
Continue readingHow to Create a Computed Column in SQLite
Generated column support was added to SQLite in version 3.31.0, which was released on 22 January 2020.
Generated columns and computed columns are the same thing. They are columns whose values are a function of other columns in the same row.
In SQLite, generated columns are created using the GENERATED ALWAYS column-constraint when creating or altering the table.
There are two types of generated column; STORED and VIRTUAL. Only VIRTUAL columns can be added when altering a table. Both types can be added when creating a table.
Add a Column to an Existing Table in SQLite
In SQLite, you can use the ALTER TABLE statement to add a column to an existing table.
This is actually one of the few things you can do with the ALTER TABLE statement in SQLite. The only things you can do with the ALTER TABLE statement in SQLite is rename a table, rename a column, and add a new column to an existing table.
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 readingConvert NULL Values to the Column’s Default Value when Inserting Data in SQLite
One of SQLite‘s non-standard extensions to SQL is the ON CONFLICT clause.
This clause allows you to determine what should happen when certain conflicts occur due to a constraint violation.
One of the things you can use this clause for is to replace NULL values with a column’s default value when inserting or updating data in a table.
By default, if you try to explicitly insert NULL into a column with a NOT NULL constraint, it will fail.
And if you try to explicitly insert NULL into a column without a NOT NULL constraint, then NULL will be assigned to that column, even if there’s a DEFAULT clause.
However, you can use the ON CONFLICT clause to set it to the default value instead of NULL.
How 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.