(Oracle PostgreSQL변환) from dual 대응

Oracle에서는 select문의 from절을 생략할 수 없기 때문에 표를 필요로 하지 않는 작업에도 dual테이블을 사용합니다. dual테이블은 Oracle에 기본으로 작성 되어있습니다. PostgreSQL에서는 from절을 생략 할 수 있기 때문에 dual테이블은 기본으로 작성되어 있지 않습니다. 그러므로 PostgreSQL에서는 ‘from dual’절을 삭제해야 실행 할 수 있습니다. 이 글에서는 ‘from dual’절에 대한 이 두 데이터베이스의 차이점과 sql문 변경 없이 양쪽에서 사용할 수 있게 하는 방법에 대해 소개하겠습니다.

Oracle vs PostgreSQL비교

Oracle에서는’from dual’ 절을 생략하면 다음과 같이 에러가 발생합니다.

Oracle(OK)	Oracle(NG)
SQL> select upper(‘Hello World’) as item1 from dual; ITEM1 ———- HELLO WORLD	SQL> select upper(‘Hello World’) as item1; select upper(‘Hello World’) as item1 * ERROR at line 1: ORA-00923: FROM keyword not found where expected

PostgreSQL에서는 dual테이블이 없어 ‘from dual’ 절을 생략하지 않고 사용하면 다음과 같이 에러가 발생합니다.

PostgreSQL(NG)	PostgreSQL(OK)
mydb=# select upper(‘Hello World’) as item1 from dual; ERROR: relation “dual” does not exist LINE 1: select upper(‘Hello World’) as item1 from dual;	mydb=#select upper(‘Hello World’) as item1; item1 ————- HELLO WORLD (1 row)

Oracle dual 테이블 고찰

Oracle데이터베이스는 다음과 같이 sys가 소유자인 dual테이블이 존재합니다. 다음과 같이 PUBLIC에 synonym 형태로 공개 되어있어 모든 데이터베이스 사용자가 참조할 수 있습니다.

SQL> COLUMN owner FORMAT A10;
SQL> COLUMN object_name FORMAT A10;
SQL> COLUMN object_type FORMAT A10;
SQL>
SQL> select owner, object_name, object_type from dba_objects where lower(object_name) = 'dual';
OWNER      OBJECT_NAM OBJECT_TYP
---------- ---------- ----------
SYS        DUAL       TABLE
PUBLIC     DUAL       SYNONYM

SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

또한 dual테이블에는 다음과 같이 오직 한 행의 데이터만 존재함을 확인할 수 있습니다.

SQL> select dummy from dual;

D
-
X

PostgreSQL에서 ‘from dual’사용하기

다음과 같은 방법으로 PostgresSQL에서도 Oralce의 dual테이블을 모방해서 만들 수 있습니다.

mydb=# CREATE TABLE public.dual ( dummy character varying(1) );

CREATE TABLE
mydb=# INSERT INTO public.dual VALUES ('X');
INSERT 0 1
mydb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | dual | table | postgres
(1 row)

위와 같이 dual테이블을 작성한다면 Oracle과 똑같이 형태로 ‘from dual’절을 사용할 수 있습니다.

PostgreSQL(OK)	PostgreSQL(OK)
mydb=# select upper(‘Hello World’) as item1 from dual; item1 ————- HELLO WORLD (1 row)	mydb=#select upper(‘Hello World’) as item1; item1 ————- HELLO WORLD (1 row)

만약 Oracle 데이터베이스에서 PostgreSQL데이터베이스로 갈아 탈 때 SQL문을 되도록 수정하지 않도록 하려면 이 방식을 추천합니다.