For the German version of this post click here
Whenever a simple value needs to be set or queried in SQL, it is time for the pseudo table DUAL to shine.
One of the most popular use cases for this was the use of sequences until Oracle version 10.
In previous versions, sequences had to be queried by an SQL statement, meaning the following code worked:
select my_seq.nextval from dual;
The following code didn’t work:
v_myvar := my_seq.nextval;
The DUAL table is an actually existing physical table in the database that resides in the SYS schema.
The table consists of a single column with the name “DUMMY”.
The datatype of the column is VARCHAR2(1).
The single row of the column has the value ‘X‘ in the column which can be queried by the following statement:
select * from dual;
Theoretically this table can be changed.
As expected, Oracle strongly advises against doing this, since it can create a host of unexpected behavior and errors.
The basic properties could be simulated with any other 1-column-1-row-table, but there is a simple catch.
The Oracle optimizer recognizes DUAL as a keyword and creates a fitting execution plan for this.
So how is this working in Postgres?
If you run the code from Oracle on Postres, you will see the the statement from above…
select 1 from dual;
… doesn’t work!
Postgres knows of no DUAL table.
Solutions
There are two options, depending on the current state and the planned outcome:
- You create a dummy-view in Postgres named DUAL (with 1 row and 1 column)
- You alter the statement
The first option is simple and especially recommended it you migrate larger portions of code where you would otherwise have to adapt tens or hundreds of statements.
The second option is recommended for smaller migrations of generally new developments. It is the option that fits the Postgres standard. It would look like this in Postgres:
select 1
The complete FROM clause of the statement is gone!
The Postgres syntax make the use of a Dummy table obsolete if you just need to get or set a single value by SQL (including for example also SYSDATE).
This makes the whole thing much more simple. 🙂
