How to use VALUES as a Stand Alone SQL Statement in PostgreSQL

Many of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.

But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.

When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.

Continue reading

Oracle Adds Support for IF EXISTS and IF NOT EXISTS Syntax Modifiers

Many RDBMSs implement IF EXISTS and IF NOT EXISTS syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE and DROP TABLE statements, to name just a couple.

These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).

Continue reading

Fix Error “function lag(numeric, numeric) does not exist” in PostgreSQL

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.

Continue reading

Fix Error “argument of ntile must be greater than zero” in PostgreSQL

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 reading

Understanding the DISTINCT ON Option in PostgreSQL

Most 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.

Continue reading

Fix “SELECT DISTINCT ON expressions must match initial ORDER BY expressions” in PostgreSQL

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.

Continue reading

Understanding the OFFSET Clause in Oracle

In Oracle Database, we can use the OFFSET clause to make a SELECT statement skip a certain number of rows in its result set.

The OFFSET clause is defined as part of the FETCH row limiting clause, which allows us to limit the number of rows returned by a query.

We can therefore use both OFFSET and FETCH to limit the output to just the specified number or percentage of rows, at a specified offset.

Continue reading