Most major RDBMSs support the NULLIF() operator, which returns NULL if both of its arguments are equivalent. If the arguments not equivalent, NULLIF() returns the first argument.
NULLIF() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).
Most major RDBMSs support the COALESCE() operator, which returns the first non-null value from its list of arguments.
COALESCE() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).
In PostgreSQL, the LEAST() function returns the smallest value from a list of any number of expressions.
The LEAST() function is not included in the SQL standard, but it’s a popular extension that many major RDBMSs support.
Sometimes you might need to search a database table for only those rows that contain at least one number in a given column.
Technically, numbers can be represented by words and other symbols, but here “number” means “numerical digit”.
Below are examples of how to find rows that contain at least one number in various SQL based DBMSs.
Continue readingIn PostgreSQL, we can use the to_timestamp() function to convert a Unix timestamp value to a date/time value.
The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).
Continue readingBelow are two options for returning rows that only contain non-alphanumeric characters in PostgreSQL.
Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab characters.
We can use the - operator to subtract one or more weeks from a date in PostgreSQL.
We can use the following technique in PostgreSQL to return the last day of a given month.
This could be the last day of the current month, or the last day of the month based on a date that we specify.
Continue readingThe following PostgreSQL examples return only those rows that don’t have a numeric value in a given column.
Continue readingHere are seven ways to return duplicate rows in PostgreSQL when those rows have a primary key or other unique identifier column.
This means that the duplicate rows share exactly the same values across all columns with the exception of their primary key/unique ID column.
Continue reading