[SQL] 자주 쓰는 PostgreSql 함수 정리

˙  기본문법

-- 데이터베이스 확인 --
SELECT * FROM pg_catalog.pg_namespace;


-- 스키마 확인 --
SELECT * FROM pg_catalog.pg_tables;


-- 테이블명 변경 --
ALTER TABLE "test2222" rename to test;


-- 컬럼명 변경 --
ALTER TABLE test rename column asd1 to asd;

 

1. Cross Join

SELECT * FROM test CROSS JOIN test2

 

 

2. Using

--- 두 테이블의 컬럼명이 같을때 사용 ---
SELECT * FROM test1 INNER JOIN test2 ON test1.name = test2.name;
SELECT * FROM test1 INNER JOIN test2 USING (name);

 

3. Cast

-- String 형식을 int 형식으로 변환 --
SELECT * FROM test1 WHERE search_type = CAST(#{searchType} AS INTEGER)

 

4. TO_CHAR

-- timestamp 형식을 String 형식 포맷으로 변환 --
SELECT to_char(reg_dt, 'YYYY-MM-DD HH24:MI:SS') as reg_dt FROM test1

 

5. JSON

-- String 형식을 json 형식으로 변환 해서 update --
UPDATE test1 SET json = JSON(#{json})

-- JSON 형태는 빈값이 들어가면 안되서 NULL 로 넣어야함 --
UPDATE test1 SET json = NULL

 

6. RETURNING

-- 시퀀스 seq를 insert 후 return 값으로 받음 --
INSERT INTO test1(subject) VALUES(#{subject}) RETURNING seq

 

7. ROW_TO_JSON

-- 테이블 정보를 json 형식으로 받아올때 사용 --
SELECT row_to_json(t) FROM (
	SELECT seq, subject FROM test1 
) t

 

8. ARRAY_AGG, ARRAY_TO_STRING

-- nm 컬럼을 배열로 만들어 , 로 구분한 String 형식으로 변환 --
SELECT array_to_string(array_agg(nm),',') FROM test1

 

9. JSONB

-- jsonb 타입 검색으로 조건에 해당하는 json 형식을 가져올떄 사용 --
SELECT json FROM test1 WHERE json @> jsonb(concat('[{"id": "',seq,'","type": "I"}]'))

 

10. LAG, LEAD

-- 이전글, 다음글 번호 가져올때 사용 --
<sql id="orderSql">
	ORDER BY seq DESC
</sql>

SELECT 
	  LAG(seq,1) over(<include refid="orderSql"/>) as prevSeq
	, LEAD(seq,1) over(<include refid="orderSql"/>) as nextSeq
FROM test1