Oracle迁移至PostgreSQL时“relation "dual" does not exist”错误的解决方法

Hey there, let’s work through this error you’re facing. The issue stems from two key differences between Oracle and PostgreSQL that your query is hitting:

• PostgreSQL has no need for the DUAL table: Unlike Oracle, which requires FROM DUAL for single-row queries that don’t reference an actual table, PostgreSQL lets you run standalone SELECT statements without specifying a table at all.
• Random number functions differ: Oracle’s DBMS_RANDOM.VALUE maps directly to PostgreSQL’s random() function—both return a value between 0 and 1 by default, so the math logic translates cleanly.

Modified Working Query #

Swap out your original statement for this PostgreSQL-compatible version:

EXECUTE 'SELECT random() * POWER(10,6)';

Optional: Keep Using DUAL (Workaround) #

If you’re migrating a large codebase and don’t want to rewrite every FROM DUAL reference, you can create a dummy DUAL table in PostgreSQL to mimic Oracle’s behavior:

CREATE TABLE dual (dummy varchar(1));
INSERT INTO dual VALUES ('X');

Then your query only needs the function swap:

EXECUTE 'SELECT random() * POWER(10,6) FROM DUAL';

Note that this is just a workaround—PostgreSQL’s native approach of omitting the table is more idiomatic and cleaner in the long run.

内容的提问来源于stack exchange，提问作者snehapai