DBConcepts Logo Original

Select * from DUAL in Postgres English

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.


There are two options, depending on the current state and the planned outcome:

  1. You create a dummy-view in Postgres named DUAL (with 1 row and 1 column)
  2. 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. 🙂


Weitere Beiträge

AOUG – Anwenderkonferenz 2024

Mit dem Motto „Driving Transformation“ findet dieses Jahr im Juni 2024 im Tech Gate (Donau-City-Straße 9, 1220 Wien) die Anwenderkonferenz der Austrian Oracle User Group


Newsletter abonnieren

Wir freuen uns, dass wir Ihr Interesse für den Newsletter geweckt haben! Mit dem Versand dieser Zustimmung erhalten Sie regelmäßig alle aktuellen Informationen!

Vielen Dank für Ihr Interesse an unserem Unternehmen. Derzeit suchen wir niemanden für diese Stelle. Aber wir sind immer an talentierten Menschen interessiert und freuen uns von Ihnen zu hören! Schicken Sie uns einfach Ihren Lebenslauf und eine kurze Nachricht und schreiben Sie an welcher Stelle Sie interessiert sind: Wir freuen usn von Ihnen zu hören!