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.
DBMS
Database Management Systems
Prepend a Plus/Minus Sign to a Number in PostgreSQL
In PostgreSQL, you can use the to_char() to output a number in a certain format, including with a plus or minus sign to indicate whether it’s a positive or negative number.
To do this, use one of S, MI, PL, or SG in your format string.
Another option is to use PR in order to enclose any negative values in angle brackets.
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
Template Patterns & Modifiers for Date/Time Formatting in PostgreSQL
This page contains the full list of template patterns and template pattern modifiers that can be used when formatting date and times in PostgreSQL.
Template Patterns & Modifiers for Numeric Formatting in PostgreSQL
This page contains the full list of template patterns and template pattern modifiers that can be used when formatting numbers in PostgreSQL.
Get the Day Name from a Date in PostgreSQL
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.