How to Select From Dual in PostgreSQL

1. Overview of dual Table
2. Introduction to the dual Table
3. Importance of the dual Table in PostgreSQL
4. Create a dual Table in PostgreSQL

Today’s tutorial educates about the dual table, its importance in PostgreSQL and how we can select from dual in PostgreSQL.

Overview of dual Table

In database systems, we sometimes need to use some queries for selecting data that is not present in a table. For example, we may execute the following query to retrieve the current date and time:

select now();

This query uses the SELECT statement without the FROM keyword since it is not selecting the data from any table. This selection of a pseudo column is a valid possibility in PostgreSQL.

However, some database management systems like Oracle do not allow this syntax. Instead, they require that there must be a FROM clause in the SELECT statement.

So what do we do in Oracle when running basic statements like the one above? We use the dual table.

Introduction to the dual Table

The dual table is a dummy table in Oracle used to counter the problem described above.

It only has one row and column with the column name being dummy and a value of x stored in the row, which is of the data type VARCHAR2(1).

Let us look at the dual table in Oracle. Suppose we want to do a simple calculation in SQL. We can do it in the following way:

select 2*2;

However, running this query on Oracle will give us an error saying ORA-00923: FROM keyword not found where expected. This is because, in Oracle, we must specify the FROM clause and table in the SELECT statement.

Thus we run the following query to counter the error:

select 2*2 as result from dual;

It removes the error as dual is used as a dummy table.

In PostgreSQL, there is no restriction of necessarily including the FROM clause in the SELECT statement. Therefore, we can run the following statement without any error:

select 2*2 as result;

So why do we need to look at alternatives to the dual table in PostgreSQL? This question is answered below.

Importance of the dual Table in PostgreSQL

Even though PostgreSQL on its own does not require the dual table, issues might arise while porting a database from PostgreSQL to Oracle or the other way round.

An organization might want to migrate its database from one database management system to another due to scalability issues and modernization. But for this purpose, we must also consider both platforms’ compatibility.

One compatibility issue is the dual table, which is present in Oracle but not in PostgreSQL. Therefore, when code is migrated from one platform to another, it is guaranteed to produce errors.

For example, the following code runs perfectly on Oracle but will introduce an error in PostgreSQL:

select 2*2 as result from dual;

It is because PostgreSQL allows the omission of the FROM keyword in the SELECT statement, so the dual table does not exist there. Therefore, selecting from it produces an error.

Similarly, a simple select 2*2 query, which works perfectly fine in PostgreSQL, will be considered erroneous when run in Oracle. As explained earlier, it is a requirement in Oracle to include the FROM clause in the SELECT statement.

Therefore, to make database migration between these platforms compatible, we will have to look for some alternatives in PostgreSQL. However, the solution is simple: since we do not have a dual table in PostgreSQL, we can create one.

Create a dual Table in PostgreSQL

Yes, the solution to this compatibility issue is as simple as creating a dual table in the PostgreSQL database, just like the one in Oracle. We can do this in the following way:

CREATE TABLE public.dual ( dummy varchar);

We have included a column named dummy of the data type varchar. We will now fill it with a dummy value as well, similar to the dual table in Oracle:

insert into dual values ('X');

Let us test if this works by running the following query, which was previously giving us an error in PostgreSQL:

select 2*2 as result from dual;

It is working now! It removes the selection from the dual compatibility issue between PostgreSQL and Oracle.

Alternatively, we can create a dual view in PostgreSQL instead of a table. It is done in the following way:

CREATE VIEW public.dual AS 
SELECT 'X'::varchar AS dummy;

Here, in the definition of the dual view, we are selecting a dummy varchar variable, just like the one we have in the dual table in Oracle. Let us see if this works by again running this query:

select 2*2 as result from dual;

As expected, this works as well! Hence, we can create either a table or a view in PostgreSQL, which works just like the dual table in Oracle.

Let us look at other examples of selecting pseudo columns using a dual table in PostgreSQL to see if it works in all cases.

In the following example, we will try selecting the current date and time using the following query:

select now() from dual;

It gives the following output:

We can also select the current user of the database in the following way:

select user from dual;

It gives the following output:

So, we have discussed that the dual table is needed in some database management systems, like Oracle, which require specifying a FROM clause.

To counter compatibility issues, we learned how we could create a dummy dual table or view in PostgreSQL. We hope you were able to grasp these concepts thoroughly. Keep learning!