Contents

DUAL table

A dummy rable provided in Oracle which does not exist in PostgreSQL

DUAL is a dummy table provided by Oracle for using when evaluating expressions which don't actually need to reference a database object, e.g.

SELECT 1 FROM DUAL

PostgreSQL does not provide an equivalent to the DUAL table as it is capable of evaluating expressions without referencing a database object, e.g.

SELECT 1

Compatibility

orafce implements a DUAL table to assist with migrating from oracle, created as:

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

(source)

Implicit implementation in PostgreSQL

In 2018, Tom Lane suggested a potential use case where the planner could benefit from inserting a dummy table into the planner/rewriter which would simplify various code paths: "SELECT ... FROM DUAL" is not quite as silly as it appears. This was subsequently implemented as commit 4be058fe in PostgreSQL 12.

Categories

Migration, Planner

See also

orafce