Below is a full list of date/time functions available in PostgreSQL.
Continue readingAuthor: Ian
PostgreSQL Math Functions
Below is a full list of mathematical functions available in PostgreSQL.
These are arranged into the following four categories:
- Mathematical Functions
- Random Functions
- Trigonometric Functions
- Hyperbolic Functions
“Cannot drop the trigger” Error when Trying to Drop a Logon Trigger? Try This.
Are you’re trying to drop a logon trigger in SQL Server, but you’re getting an error like the following?
“Cannot drop the trigger ‘trigger_name’, because it does not exist or you do not have permission.”
It could be because you’re missing the ON ALL SERVER argument.
SQL Server IF vs IIF(): What’s the Difference?
Students learning a new programming language will often start their first lesson with an IF statement, where their statement will return a value only if the expression is true.
They might then progress to an IF... ELSE statement, where they can determine another value to return if the expression is false. So therefore, return one value if the expression is true, and another value if it’s false.
SQL Server certainly includes the IF... ELSE statement in its T-SQL toolbox.
SQL Server also includes the IIF() function, which does a similar thing, but with a more concise syntax.
But there are some subtle differences.
Continue readingHow to Test for Overlapping Dates in PostgreSQL
In PostgreSQL, you can use the OVERLAPS operator to test for overlapping time periods.
The function returns true when two time periods (defined by their endpoints) overlap, and false when they do not overlap.
Continue readingHow to Format Numbers as Currency in PostgreSQL
To format a number as currency in Postgres, you can either convert it to the money data type, or use to_char() to convert it to text that includes the appropriate currency symbol.
This obviously assumes that the number isn’t already stored using the money type.
Below are examples of each of these options.
Continue readingHow to Pause the Execution of a Statement in PostgreSQL
PostgreSQL includes three functions that allow you to delay the execution of the server process. the execution of a statement.
In other words, you can run a statement and have it pause half way through, before continuing on its merry way.
The three functions are:
These are all very similar, but they work in slightly different ways.
Below are examples of each one.
Continue readingHow IIF() Works in SQL Server
In SQL Server, the IIF() function (not to be confused with the IF statement) is a conditional function that returns the second or third argument based on the evaluation of the first argument.
It’s a shorthand way for writing a CASE expression. It’s logically equivalent to CASE WHEN X THEN Y ELSE Z END assuming IIF(X, Y, Z).
IIF() is an abbreviation for Immediate IF.
How the IF Statement Works in SQL Server
Most (probably all) programming languages include some form of the IF statement that allows programmers to write conditional code. That is, code that will execute only if a certain condition is true.
It’s a very simple concept. Basically it goes like this:
“If this, do that.”
Most languages simply call it IF, but some have their own twist on the name (for example, in ColdFusion/CFML, it’s called CFIF).
In any case, they essentially do the same thing.
In SQL Server (or more precisely, its programming language T-SQL) it’s called IF.
Format the Month in Roman Numerals in PostgreSQL
In PostgreSQL, you can use the to_char() function to return dates in various formats.
One of the things you can do with this function is return the month portion of a date in roman numerals.
Continue reading