Equivalence of from dual in PostgreSQL

Question

I work with Hibernate and Oracle. I used this code :

     if (null != sessionFactory) {
                Session s = currentSession.get();
    
                if ((null == s) || !s.isOpen()) {
                    s = sessionFactory.openSession();
                    currentSession.set(s);
                } else {
                    try {
                        HibernateWork hibernateWork = new HibernateWork("SELECT 1 FROM DUAL");
                        s.doWork(hibernateWork);
                    } catch (Exception e) {
                       
                        s = sessionFactory.openSession();
                        currentSession.set(s);
                    }
                }
    
                return s;
            }

I want to do the same thing with PostgreSQL, but PostgreSQL does NOT support the ‘FROM dual’ syntax. I want to know equivalence of dual in PostgreSQL.

Nick Barnes · Accepted Answer · 2015-05-18 12:49:47Z

126

You don't need a FROM clause at all. Just SELECT 1 will do it.

answered May 18, 2015 at 12:49

Nick Barnes

21.9k5 gold badges66 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Surya Over a year ago

what if I needed multiple rows for one column?

Nick Barnes Over a year ago

@Luna You can select n from generate_series(1,5) s(n)

rogerdpack · Accepted Answer · 2022-04-07 20:04:30Z

23

PostgreSQL has implicit DUAL table, just write:

SELECT NOW  ()

Output:    
2019-05-17 13:56:51.120277+02

In case that you Oracle PL/SQL code is referring DUAL table you can create own DUAL table:

CREATE TABLE mydb.public.dual (column1 bool);

edited Apr 7, 2022 at 20:04

rogerdpack

68k40 gold badges295 silver badges411 bronze badges

answered May 17, 2019 at 12:01

Amit Vujic

1,8281 gold badge27 silver badges32 bronze badges

Comments

Predrag Maric · Accepted Answer · 2015-05-18 12:50:37Z

20

Just use select 1. From documentation

Oracle uses the "fake" dual table for many selects, where in PostgreSQL we can write select just without from part at all. This table was created in postgres as a view to ease porting problems. This allows code to remain somewhat compatible with Oracle SQL without annoying the Postgres parser.

answered May 18, 2015 at 12:50

Predrag Maric

24.5k5 gold badges57 silver badges72 bronze badges

1 Comment

wideseen Over a year ago

DUAL is not a fake table, it's as real as any other table. Try inserting a row into DUAL. But have your entire database backup ready! (At least in older versions of Oracle).

Frank Heikens · Accepted Answer · 2015-05-18 18:35:47Z

6

Just create one, if you really want to:

CREATE TABLE dual();

answered May 18, 2015 at 18:35

Frank Heikens

130k26 gold badges158 silver badges153 bronze badges

Comments

Oli · Accepted Answer · 2023-12-06 23:49:35Z

Another way to do this in Postgres would be to use the VALUES command to create a single-element array and use it in the FROM clause, such as:

SELECT 'foo' FROM (VALUES(1)) i;

This may prove useful if you want or need to express a SELECT statement with a FROM clause, for clarity purposes or when nested in a function or command that may require it.

If you absolutely must call dual (for script portability reasons, or otherwise) the workaround I found was the most efficient, direct, and closest to the Oracle behavior is to use a view:

CREATE VIEW dual AS
    SELECT 'X' as DUMMY from (VALUES('X')) i;

In this case, the view approach seems more appropriate to me given the Oracle documentation stating:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column [...]

Output:

postgres=# CREATE VIEW dual AS
    SELECT 'X' as DUMMY from (VALUES('X')) i;
CREATE VIEW
postgres=# select * from dual;
 dummy
-------
 X
(1 row)

postgres=# select 1 from dual;
 ?column?
----------
        1
(1 row)

Collectives™ on Stack Overflow

Equivalence of from dual in PostgreSQL

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Linked

Related