뚝딱 PostgreSQL 레시피 (오라클과 PostgreSQL을 한 번에)

김익서

2005년농심데이타시스템즈에서개발자로커리어를시작했다.2008년부터2015년까지비투엔컨설팅에서데이터컨설턴트로재직하면서데이터베이스기술과SQL교육에대한경험을쌓았다.'SQL전문가가이드'초판의공저자로참여했으며한국데이터진흥원에서주관하는SQLD,SQLP자격검정전문위원으로2022년도까지활동했다.국민대학교,삼성전자,KB데이타시스템등에서SQL교육을수행했으며2022년부터와우패스에서SQLD전담강사로활동중이다.현재싸이버로지텍에서DA로근무중이다.

CHAPTER1SQL기본문법
1.1.SELECT
1.2.FROM
1.3.WHERE
1.4.조건연산자
1.4.1.AND조건과OR조건
1.4.2.NOT조건
1.4.3.=조건
1.4.4.범위조건
1.4.5.NULL조건
1.5.인라인뷰
1.6.정렬및그룹화
1.6.1.ORDERBY절
1.6.2.GROUPBY절
1.7.집합연산
1.7.1.합집합
1.7.2.교집합
1.7.3.차집합

CHAPTER2스칼라함수
2.1.숫자함수
2.1.1.ABS
2.1.2.SQRT
2.1.3.CBRT
2.1.4.POWER
2.1.5.CEIL
2.1.6.FLOOR
2.1.7.DIV
2.1.8.MOD
2.1.9.ROUND
2.1.10.TRUNC
2.1.11.SIGN
2.1.12.무작위수(RANDOM)
2.2.문자함수
2.2.1.UPPER
2.2.2.LOWER
2.2.3.INITCAP
2.2.4.CONCAT
2.2.5.||
2.2.6.CONCAT_WS(오라클미지원)
2.2.7.SUBSTRING(오라클미지원),SUBSTR
2.2.8.SUBSTRB(PostgreSQL미지원)
2.2.9.LEFT(오라클미지원),RIGHT(오라클미지원)
2.2.10.SPLIT_PART(오라클미지원)
2.2.11.REPLACE
2.2.12.OVERLAY(오라클미지원)
2.2.13.TRANSLATE
2.2.14.LENGTH,CHAR_LENGTH(오라클미지원),CHARACTER_LENGTH(오라클미지원)
2.2.15.OCTET_LENGTH(오라클미지원)
2.2.16.LPAD,RPAD
2.2.17.LTRIM,RTRIM
2.2.18.BTRIM(오라클미지원)
2.2.19.ASCII
2.2.20.CHR
2.2.21.POSITION(오라클미지원),STRPOS(오라클미지원)
2.2.22.REPEAT(오라클미지원)
2.2.23.GREATEST,LEAST
2.2.24.ILIKE(오라클미지원)
2.3.날짜함수
2.3.1.자주사용하는PostgreSQL날짜함수사용형태
2.3.2.TO_DATE
2.3.3.TO_TIMESTAMP
2.3.4.특정일자의시작과끝시각구하기
2.3.5.현재를기준으로어제,오늘,내일구하기
2.3.6.시분초제거하기
2.3.7.월의마지막날구하기(LAST_DAY)
2.3.8.요일구하기(EXTRACT)
2.3.9.한국,미국,영국날짜표현
2.3.10.일정기간이후시각구하기
2.3.11.시각값자르기
2.3.12.시각값에서특정부분추출하기
2.3.13.일자간차이구하기
2.3.14.입력일이후특정요일인첫번째일자구하기
2.3.15.테이블에서일자단위데이터조회예시
2.4변환함수
2.4.1.형변환방법
2.4.2.TO_CHAR(숫자를문자로변경)
2.4.3.TO_CHAR(일자를문자로변경)
2.4.4.TO_NUMBER(문자를숫자로변경)
2.4.5.TO_DATE(문자를시각으로변경)
2.4.6.NULLIF
2.4.7.NVL과NVL2
2.5ARRAY관련함수와조회방법
2.5.1.ARRAY데이터생성과기본이해하기
2.5.2.ARRAY에서특정데이터추출하기
2.5.3.ARRAY차원파악
2.5.4.ARRAY길이파악
2.5.5.ARRAY에값추가
2.5.6.ARRAY결합
2.5.7.ARRAY에서특정값의위치찾기
2.5.8.문자열을ARRAY로변환
2.5.9.ARRAY를문자열로변환
2.5.10.ARRAY를row로분리
2.5.11.row값을ARRAY로변환
2.5.12.ARRAY간비교
2.5.13.ARRAY칼럼에있는값조회(sample)

CHAPTER3타임존설정및변환
3.1.세션타임존확인및설정방법
3.2.타임존변환방법
3.2.1.WITHTIMEZONE사용
3.2.2.WITHOUTTIMEZONE
3.3.sysdate변환
3.3.1.sysdate변환방법제안
3.3.2.테스트요약
3.3.3.상세테스트결과

CHAPTER4조인
4.1.이너조인(INNERJOIN)
4.2.아우터조인(OUTERJOIN)

CHAPTER5분석함수
5.1.분석함수기본문법
5.1.1.WINDOWING절
5.2.다양한분석함수
5.2.1.ROW_NUMBER
5.2.2.RANK와DENSE_RANK
5.2.3.LEAD와LAG
5.2.4.LISTAGG,STRING_AGG,ARRAY_AGG
5.2.5.CUME_DIST
5.2.6.PERCENT_RANK
5.2.7.NTILE
5.2.8.FIRST_VALUE,LAST_VALUE
5.2.9.NTH_VALUE

CHAPTER6SQL활용
6.1.조인업데이트
6.1.1.오라클의조인업데이트
6.1.2.PostgreSQL의조인업데이트
6.2.DML을포함한WITH절
6.2.1.기본적인WITHDML구문
6.2.2.WITHDML을이용한MULTITABLEINSERT
6.2.3.WITHDML을이용한UPSERT
6.2.4.ONCONFLICT를사용한제약조건처리
6.3LATERAL
6.3.1.오라클에서LATERAL사용
6.3.2.PostgreSQL에서LATERAL사용
6.3.3.LEFTOUTERJOINLATERAL
6.4.기타SQL사용법
6.4.1.WHERErownum=1변환
6.4.2.원하는건수의임시집합만들기
6.4.3.시퀀스NEXTVAL호출
6.4.4.SELECT절에NOTEXISTS,EXIST사용(PostgreSQL)
6.4.5.FILTER구문사용(PostgreSQL)
6.4.6.ISDISTINCTFROM
6.4.7.DISTINCTON

CHAPTER7계층데이터조회
7.1.계층데이터모델
7.2.오라클의CONNECTBY구문작동원리
7.2.1.STARTWITH절
7.2.2.계층데이터연결
7.2.3.순방향전개와역방향전개
7.2.4.데이터필터링
7.2.5.계층쿼리결과정렬하기
7.2.6.기타사용가능칼럼
7.2.7.오류데이터로인한무한루프처리
7.2.8.CATEGORYFULLPATH출력
7.3.RecursiveSubqueryFactoring
7.3.1.RecursiveSubqueryFactoring기초문법
7.3.2.RecursiveSubqueryFactoring동작방식
7.3.3.RecursiveSubqueryFactoring레벨우선정렬
7.3.4.RecursiveSubqueryFactoring계층우선정렬
7.3.5.CONNECT_BY_ISLEAF,CONNECT_BY_ROOT,SYS_CONNECT_BY_PATH값만들기
7.3.6.RecursiveSubqueryFactoring무한루프(cycle)데이터처리
7.3.7.RecursiveSubqueryFactoring을이용한계층누적합계산

CHAPTER8PostgreSQLSQL작성가이드(참고용)

대략3년전으로기억한다.저자가DB전문도서출판사대표인나를찾아왔다.전직장에서우린같은팀이었고,내가팀장이었다.저자가나를찾아온이유는책을출간하고싶어서였다.최근Oracle시스템을PostgreSQL로전환하는프로젝트를수행했는데,그전까지Oracle중심으로만일해온터라많은시행착오를겪었고,그경험을담은책을출간해다른이들에게도움을주고싶다는취지였다.아직초벌원고가완성된것도아니었고,그때까지만해도과연시장수요가있을지반신반의했기에더구체적인논의를진행하지못하고흐지부지되고말았다.

시간이흘러이번엔내가저자를찾아갔다.DB컨설팅회사도함께운영하는터라DB업계동향에도밝은나는최근들어오는영업문의가대부분DBMS전환관련되었다는사실에저자가3년전에제안했던그책이번뜩생각났던것이다.

그간IT생태계는빅데이터,클라우드컴퓨팅,MSA등을중심으로지각변동이일어났고,최근엔AI열풍이거세다.그중심에는데이터가있다.기존보다훨씬더많은데이터를활용하려다보니데이터저장비용을계속낮춰야했고,갖은노력끝에기업들은‘닭잡는데소잡는칼쓰지말자’는결론을낸듯하다.

Oracle이세상에서가장뛰어난범용DBMS라는건세상이다아는사실이다.하지만,도입비용과유지보수비용이만만치않다.그에반해,그것이가진기능의1/10,아니1/100도채쓰지못하는것이현실이다.그럴바엔기능은좀부족하더라도비용이현저히낮은오픈소스DBMS를사용하자는주장이설득력을얻고있다.성능은분산을통해확보한다.분산아키텍처가복잡하긴해도사용자및데이터증가에따른확장성측면에서는유리하다.

운영비용을절감하기위해시스템특성과규모에맞는DBMS로전환하는사례가느는흐름속에PostgreSQL이있고,이책은그흐름에올라탄개발자들에게신선한바람을제공할것이다.