[PostgreSQL] Temporary 테이블 만들기

 복잡한 SQL 쿼리를 한 문장에 담아서 작성하다보면 Nested Select 쿼리를 쓰게 되고 그 결과 값들이 다시 Join 하는 케이스가 많이 발생합니다. 그런데 문제는 Nested Select 쿼리의 cost가 높아서 얼마 안되는 레코드 수에도 쿼리 실행 속도가 말도안되게 느려지는 문제가 발생합니다.

 그래서 이런 nested select 쿼리를 피하기 위해 temporary table을 생성 후 중간 쿼리 결과를 이곳에 담아 놓고 이 임시 테이블들 간에 join을 해서 최종 결과를 뽑으면 훨씬 빠른 속도로 원하는 데이터를 추출 할 수 있습니다.

 

PostgreSQL에서는 아래와 같은 방법으로 Temporary 테이블을 생성 할 수 있습니다.

	create temp table temp_test
	(
	  "Id" int,
	  "Name" text,
	);

PostgreSQL에서는 MSSQL과는 달리 임시 테이블에 아래와 같은 옵션을 붙일 수 있습니다.

-- Default로 preserve rows 옵션이 적용되므로 생성시 명시하지 않아도 됩니다.
create temp table temp_test
(
  "Id" int,
  "Name" text,
)
on commit preserve rows;

-- transaction이 commit 될 때 임시 테이블의 모든 행이 삭제되지만 테이블은 삭제되지 않습니다.
create temp table temp_test
(
  "Id" int,
  "Name" text,
)
on commit delete rows;

-- transaction이 commit 될 때 table을 삭제 시킵니다.
create temp table temp_test
(
  "Id" int,
  "Name" text,
)
on commit drop;

 

공식 싸이트 문서: https://www.postgresql.org/docs/current/sql-createtable.html

ON COMMIT The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are: PRESERVE ROWS No special action is taken at the ends of transactions. This is the default behavior. DELETE ROWS All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit. When used on a partitioned table, this is not cascaded to its partitions. DROPThe temporary table will be dropped at the end of the current transaction block. When used on a partitioned table, this action drops its partitions and when used on tables with inheritance children, it drops the dependent children.