If you’re getting an error in PostgreSQL that reads something like “function lag(numeric, numeric) does not exist“, it could be because your second argument is the wrong data type.
The second argument to the lag() function is optional, but if it’s provided, it must be an integer.
So to fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.
If you’re getting an error that reads “ERROR: argument of ntile must be greater than zero” in PostgreSQL, it’s probably because you’re calling the ntile() function with a non-positive integer.
This error occurs when we pass a non-positive integer to the ntile() function.
To fix this issue, be sure to pass a positive integer to the function.
Continue readingMost major RDBMSs support the DISTINCT clause, which allows us to get unique – or “distinct” – rows from our SQL queries. But PostgreSQL’s implementation of this clause has an extra option that most other RDBMSs don’t include.
PostgreSQL allows us to include the ON() option when using DISTINCT. This enables us to specify exactly which columns should be evaluated by the DISTINCT modifier, while simultaneously allowing us to return columns that aren’t evaluated.
If you’re getting a PostgreSQL error that reads “SELECT DISTINCT ON expressions must match initial ORDER BY expressions” when trying to run a query, it’s probably because the initial columns provided to your ORDER BY clause are different to the ones provided to the DISTINCT ON clause.
To fix this error, make sure the initial columns provided to the ORDER BY clause are included in the DISTINCT ON clause.
If you’re getting an error in PostgreSQL that reads something like “function lead(numeric, numeric) does not exist“, it may be because your second argument is of the wrong data type.
The second argument to the lead() function is optional, but if it’s provided, it must be an integer.
To fix this issue, make sure the second argument is an integer. Alternatively, you can omit the second argument altogether if you’re happy to use the default value of 1.
If you’re getting an error that reads “function mode() does not exist” in PostgreSQL, it could be that you’re using the wrong syntax with this function.
The mode() function requires a WITHIN GROUP clause, and we can get the above error if we remove that clause.
In this case, we can fix the error by adding a valid WITHIN GROUP clause.
In PostgreSQL the last_value() function returns the value from the last row of the current window frame.
There are at least a couple of reasons you might get an error that reads “function nth_value(numeric) does not exist” in PostgreSQL.
It could be that you’re calling the nth_value() function without the correct number of arguments. It could also be that you’re passing the wrong argument type.
To fix this issue, be sure to pass the correct number of arguments, with the correct type.
Continue readingIn PostgreSQL the first_value() function returns the value from the first row in the current window frame.
We can use this function to get the value from the first row in a result set, or from the first row in the current partition, or some other window frame that’s been specified.
Continue readingPostgreSQL has an ordered-set aggregate function called mode() that allows us to get the mode from a given column.
The mode is the most frequently occurring value.
Null values are ignored, so if null occurs the most, the mode() function will return the second most common value.