PostgreSQL Query

형식크기범위별칭

smallint	2 바이트	-32768에서 +32767	int2
integer	4 바이트	-2147483648에서 +2147483647	int, int4
bigint	8 바이트	-9223372036854775808에서 +9223372036854775807	int8

 

===null 대체===  Oracle      ==> nvl()  SQL Server  ==> isnull()  PostgreSQL  ==> COALESCE() SELECT COALESCE(etc,'널입니다~')  FROM test as tp  => 널입니다~

 

Oracle LPAD와 동일 기능 원하는 문자열로 설정된 byte만큼 왼쪽으로 채움 SELECT LPAD(COALESCE(etc,'1'),10,'0') FROM test as tp  => 0000000001 Oracle RPAD와 동일 기능 원하는 문자열로 설정된 byte만큼 오른쪽으로 채움

 

유니크한 ID 만들기 SELECT LPAD(  CAST(CAST(COALESCE(MAX(etc),'0') AS NUMERIC)+1 AS CHARACTER VARYING),10,'0') FROM test_point as tp

 

문자열을 구분자로 자를 경우 ===> split_part ===> split_part(date::TEXT, '-', 1)::SMALLINT  문자열을 합칠 경우 ===> concat ===> concat(first_name,'-',last_name)

---

1. 코드 분석

<insert id="setInsertCu">
INSERT INTO tn_cust
	(cu_id, tr_id, cu_nm)
VALUES(
	(SELECT 'CKD'||lpad((split_part(COALESCE(max(cu_id),'CKD000000'), 'CKD',2)::int+1)::text,6,'0') FROM tn_cu)
	, #{trdr_id}
	, NOW()
)
</insert>
_________________________________________
COALESCE(max(cu_id),'CKD000000') 
  =>  null 인 경우 대체
  
split_part( '55CKD000' , 'CKD',2)::int+1 
  => 문자열을 CKD 문자 기준으로 자르고 + 1 (위치 1부터 시작)
  => index1 : 55 , index2 : 0(int)
  
split_part( 'CKD000' , 'CKD',2)::int+1 
  => 문자열을 CKD 문자 기준으로 자르고 + 1 (위치 1부터 시작)
  => index1 : 공백 , index2 : 0(int)
  
lpad('1',6,'0') => 0을 문자열로 설정된 6byte만큼 왼쪽으로 채움 000001(0을5개왼쪽으로)

'CKD'|| => 문자열 'CKD'를 붙임

 

---

2. 코드분석

재귀적 쿼리인데 이는 보통 테이블 데이터가 계층형일때 많이 사용된다.

WITH RECURSIVE 구문을 통해 쿼리가 반복되며,

반복된 결과를 parent query 영역에서 FROM 절로 가져와 사용하는 구조로

UNION 다음에 사용되는 recursive query

즉, 재귀쿼리문에서는 보통 where 조건을 통해 반복이 멈추도록 제한을 둔다

WITH RECURSIVE recursive_name [(column1, ...)] AS (
   -- initial query (처음 호출하는 쿼리)
   -- non-recursive query
   SELECT [(column1, ...)]
   
   UNION [ALL]
   
   -- recursive query (반복 쿼리)
   SELECT [(column1, ...)] FROM recursive_name [WHERE]
)
-- parent query
SELECT * FROM recursive_name

 

예시1

WITH RECURSIVE mine_record AS ( 
  SELECT 1 AS num UNION ALL SELECT num + 1 FROM mine_record WHERE num < 10 
) SELECT * FROM mine_record

예시2

WITH RECURSIVE dept_record(
   dept_sn, parent_sn, dept_name, dept_ctn, level, path, cycle
) AS( 

   -- initial query (처음 호출하는 쿼리)
   -- non-recursive query
   SELECT d.dept_sn, d.parent_sn, d.dept_name, d.dept_ctn, 0, ARRAY[d.dept_sn], false 
   FROM dept d WHERE d.parent_sn IS NULL 
      
   UNION ALL 
      
   -- recursive query (반복 쿼리)
   SELECT d.dept_sn, d.parent_sn, d.dept_name, d.dept_ctn, 
   level + 1, path || d.dept_sn, d.dept_sn = ANY(path) 
   FROM dept d, dept_record dr 
   WHERE d.parent_sn = dr.dept_sn AND NOT CYCLE 
   
) SELECT 
   dept_sn, parent_sn, dept_name, dept_ctn, level, path 
  FROM dept_record 
  ORDER BY path -- parent query

쿼리가 반복되는 부분에 dept_sn을 배열로 담은 path에 각각의 dept_sn을 추가해 계층형 쿼리가 완성됐다.

 

위 예제에서 사용되는 cycle은 성능상 추가된것인데,

path에 이미 추가된 데이터에 대해서는 더이상 검색을 수행하지 않게 중복 처리를 제한것이다. 

 

예시3 : 아래 코드를 분석해 보자

<select id="getTreeList" parameterType="java.util.Map" resultType="hashMap">
    /* "commonDAO.getTreeList */
    WITH RECURSIVE TREE_OGDP(OGDP_ID, OGDP_NM, UP_OGDP_ID, DEPTH, PATH, CYCLE) AS (
        SELECT 
            A.OGDP_ID,
            A.OGDP_NM,   
            A.UP_OGDP_ID,
            1,
            ARRAY[A.OGDP_ID::text],
            FALSE
        FROM TN_OGDP A
        WHERE UP_OGDP_ID = '#'
        UNION ALL
        SELECT 
            A.OGDP_ID,
            A.OGDP_NM,   
            A.UP_OGDP_ID,
            B.DEPTH + 1,
            ARRAY_APPEND(B.PATH, A.OGDP_ID::text),
            A.OGDP_ID = ANY(B.PATH)
        FROM TN_OGDP A, TREE_OGDP B
        WHERE A.UP_OGDP_ID = B.OGDP_ID
        AND NOT CYCLE
    )
    SELECT 
        A.OGDP_ID AS ID, A.OGDP_NM AS TEXT, A.UP_OGDP_ID AS PARENT,
        B.USER_ID, B.USER_NM, A.OGDP_ID, A.OGDP_NM, A.UP_OGDP_ID
    FROM TREE_OGDP A, TN_USER B
    WHERE A.OGDP_ID = B.OGDP_ID
    ORDER BY PATH
</select>

---

3. 코드분석 array_to_string

<select id="getCustIdArray" resultType="string">
    /* custDAO.getCustIdArray */ 
    SELECT
     array_to_string( 
         ARRAY(
            select cust_id from tn_cust
          )
      , ', ') AS all_cust_id
</select>