[postgreSQL] 쿼리에 사용되는 연산자와 함수

연산자와 함수

다양한 연산을 하기 위한 기호나 문자를 연산자라고 합니다. 함수도 마찬가지로 특정 기능을 위한 구문입니다.

논리, 비교 연산자와 조건문 함수

연산자/함수	예시/형식	결과
AND	a AND b	TRUE, FALSE 또는 NULL
OR	a OR b	TRUE, FALSE 또는 NULL
NOT	NOT a	TRUE, FALSE 또는 NULL
IS TRUE	<불리언_표현식> IS TRUE	TRUE, FALSE 또는 NULL
IS FALSE	<불리언_표현식> IS FALSE	TRUE, FALSE 또는 NULL
IS NULL	<표현식> IS NULL 또는 <표현식> IS UNKNOWN	TRUE, FALSE 또는 NULL
BETWEEN	<변수> BETWEEN <시작값> AND <끝값>	-
CASE	CASE WHEN <조건문> THEN <결과문> … END	-
COALESCE()	COALESCE(매개변수1, 매개변수2, 매개변수3, …)	NULL이 아닌 최초의 매개변수 값 또는 NULL
NULLIF()	NULLIF(매개변수1, 매개변수2)	NULL 또는 매개변수1
배열 연산자와 함수
연산자/함수	예시/형식	결과
<@ 또는 @>	ARRAY[1,2,3] @> ARRAY[1,2]	TRUE, FALSE 또는 NULL
&&	ARRAY[1,2,3,4] && ARRAY[1,5,6]	TRUE, FALSE 또는 NULL
||	ARRAY[1.2.3] || ARRAY[1,3]	배열
array_append()	array_append(배열, 원소)	배열
array_prepend()	array_prepend(원소, 배열)	배열
array_remove()	array_remove(배열, 원소)	배열
array_replace()	array_replace(배열, 원소1, 원소2)	배열
array_cat()	array_cat(배열1, 배열2)	배열
JSON 연산자와 함수
연산자/함수	예시/형식	결과
->	‘{“a”:”a1”,”b”:”b1”,”c”:”c1”}’::json->'a'	JSON
	'[{“a”:”a1”},{“b”:”b1”},{“c”:”c1}]::json->1	JSON
->>	‘{“a”:{“a”:”c”},”b””:{“b”:”d”}}’::json->>'a'	TEXT
#>	‘{“a”:{“b”:{c”:”d”}}}’::json #> ‘{“a,”b”,”c”}’	JSON
#>>	‘{“a”:[{““b”:”d”},{“d”””:”f”}}}’::json #>> ‘{“a”,0,b”}’	TEXT
<@ 또는 @>	‘{“a”:0,”b”:1}’::jsonb @> '{"b":1}'::jsonb	TRUE, FALSE 또는 NULL
?	‘{“a””:0,”b”:1}’::jsonb ? 'a'	TRUE, FALSE 또는 NULL
?|	‘{“a“:0, “b“:1, “c“:2}'::jsonb ?| array['b','c']	TRUE, FALSE 또는 NULL
?&	'{“a“:0,”b”:1,”c”:2}'::jsonb ?& array['b','c']	TRUE, FALSE 또는 NULL
||	‘{“a”:0, “b”:1}'::jsonb || '{“c”:2}”::jsonb	JSONB
-	‘{“a”:”0”, “b”:1”}’::jsonb-'b'	JSONB
	‘{“a”:”0”, “b”:”1”, “c”:”2”}’::jsonb-ARRAY['b','c']	JSONB
	‘[“a”,”b”,”c”]’::jsonb--1	JSONB
json_build_object()	json_build_object(“<키1>”,”<밸류1>”,”<키2>”,”<밸류2>”,…)	JSON
json_build_array()	json_build_array(“<원소1>”,”<원소2>”,”<원소3>”,…)	JSON
json_array_length()	json_array_length(<JSON>)	JSON
json_each()	json_each(<JSON 오브젝트>)	JSON
json_array_elements()	json_array_elements(<JSON 배열>)	JSON
날짜 및 시간 연산자와 함수
연산자/함수	예시/형식	결과
CURRENT_DATE	SELECT CURRENT_DATE;	현재 날짜
CURRENT_TIME	SELECT CURRENT_TIME;	현재 시간
CURRENT_TIMESTAMP	SELECT CURRENT_TIMESTAMP;	현재 날짜 및 시간
LOCALTIME	SELECT LOCALTIME;	현재 시간
LOCALTIMESTAMP	SELECT LOCALTIMESTAMP;	현재 날짜 및 시간
now()	SELECT now();	TIMESTAMP
timeofday()	SELECT timeofday();	TIMESTAMP
age()	age(timestamp ‘2020-01-12’)	INTERVAL
EXTRACT()	EXTRACT(MONTH FROM TIMESTAMP ‘2020-09-20’)	double
date_part()	date_part('quater', now());	double
date_trunc	date_trunc('month', <TIMESTAMP>)	TIMESTAMP
서브쿼리 연산자
연산자/함수	예시/형식	결과
EXISTS	EXISTS (서브쿼리)	TRUE, FALSE 또는 NULL
IN	<표현> IN (서브쿼리)	TRUE, FALSE 또는 NULL
NOT IN	<표현> NOT IN (서브쿼리)	TRUE, FALSE 또는 NULL
ANY/SOME	<표현> <비교 연산자> ANY (서브쿼리)	TRUE, FALSE 또는 NULL
ALL	<표현> <비교 연산자> ALL (서브쿼리)	TRUE, FALSE 또는 NULL
패턴 매칭 연산자
연산자/함수	예시/형식	결과
LIKE	컬럼명 LIKE ‘패턴’	패턴에 맞는 로우 값, TRUE, FALSE 또는 NULL
SIMILAR TO	컬럼명 SIMILAR TO ‘패턴’	패턴에 맞는 로우값, TRUE, FALSE 또는 NULL
SQL 문자열 함수
연산자/함수	예시/형식	결과
length()	length(<문자열>)	TEXT
substring()	substring(<문자열> from <정수형> for <정수형>)	TEXT
left()	left(<문자열>,<정수형>)	TEXT
concat()	concat(<문자열><문자열>, … 또는 <배열>)	INTEGER
position	position(<문자열> in <문자열>)	TEXT
replace	replace(<문자열>, <문자열>, <문자열>)	TEXT

논리, 비교 연산자와 조건문 함수

논리 연산자와 비교 연산자는 조건이 참인지 거짓인지 판별하는 연산자로, 연산 결과를 불리언 데이터 타입으로 나타냅니다.

논리 연산자는 조건을 추가적으로 늘릴 때 사용하고, 비교 연산자는 2개의 어떤 값 또는 조건을 비교할 때 사용합니다.

논리 연산자

논리 연산자는 AND, OR, NOT입니다. 논리 연산자에서 조건과 결과의 불리언 데이터 타입이 탐과 거짓으로만 이루어졌다고 생각하면 안됩니다.

ABA AND BA OR BNOT A

참	참	참	참	거짓
참	거짓	거짓	참
참	NULL	NULL	참
거짓	거짓	거짓	거짓	참
거짓	NULL	거짓	NULL
NULL	NULL	NULL	NULL	NULL

비교, 범위 연산자

비교 연산자와 범위 연산자는 서로 연관이 있고 대체가 가능합니다.

비교 연산자

비교 연산자는 조건 A와 B를 비교할 때 사용합니다.

조건문 함수를 더 폭넓게 사용하기 위해, 어떤 표현이 참인지 거짓인지를 알아야 할 때가 있습니다. 이때 사용하는 비교 연산자가 비교 술어입니다.

IS TRUE와 IS NOT TRUE는 불리언 조건식이 참인지 알아보는 연산자 입니다. IS TRUE는 특정 불리언 조건식이 참이면 결과로 TRUE 값을 거짓이면 결과로 FALSE 값을 갖습니다. IS NOT TRUE은 참이 아닐경우 결과로 TRUE 값을 참일 경우 결과로 FALSE 값을 갖습니다. 이때 불리언 표현식이 NULL인 경우 참이 아니므로 결과로 TRUE 값을 갖습니다.

IS FALSE는 IS NOT TRUE와 마찬가지로 불리언 조건식이 거짓이면 결과로 TRUE 값을 참이면 결과로 FALSE 값을 갖습니다. 불리언 표현식이 만약 NULL이라면 결과는 FALSE 값을 갖습니다.

IS NULL은 불리언 표현식이 NULL이면 결과로 TRUE 값을 갖습니다. 만약 NULL이 아닌 참 또는 거짓 값을 갖는다면 결과는 FALSE 값을 출력합니다.

연산자설명결과

<불리언 표현식> IS TRUE	<불리언 표현식>이 참이다.	참 또는 거짓
<불리언 표현식> IS NOT TRUE	<불리언 표현식>이 참이 아니다.	참 또는 거짓
<불리언 표현식> IS FALSE	<불리언 표현식>이 거짓이다.	참 또는 거짓
<불리언 표현식> IS NOT FALSE	<불리언 표현식>이 거짓이 아니다.	참 또는 거짓
<불리언 표현식> IS NULL	<불리언 표현식>이 NULL이다.	참 또는 거짓
<불리언 표현식> IS NOT NULL	<불리언 표현식>이 NULL이 아니다.	참 또는 거짓

비교 연산자는 참, 거짓, NULL 세 가지 항을 비교하는 삼항 논리 기반입니다.

범위 연산자

비교 연산자를 범위 연산자로 대체할 수 있습니다. 예를 들어 비교 연산자로 A는 1보다 크고 10보다 작다를 나타낼 수 있는데, 또 다르게 생각해보면 범위 연산자로 A는 1과 10 사이의 범위에 있다고 할 수 있습니다. 이때 중요하게 생각해봐야 할 부분은 바로 경계 값입니다. 1과 10을 포함하는지, 포함하지 않는지가 명확하지 않습니다.

비교 연산자로 1보다 크거나 같고 9보다 작거나 같은 값을 얻도록 필터 기능을 활용하려고 한다면 WHERE 절을 이용하여 다음과 같이 쓸 수 있습니다.

SELECT * FROM example_table WHERE 1 <= num_col AND num_col <= 9;

BETWEEN 연산자로 조금 더 명확하고 쉽게 범위를 나타낼 수 있습니다.

SELECT * FROM example_table WHERE BETWEEN 1 AND 9;

반대로 1 미만 9를 초과하는 범위의 값을 구하고 싶다면, 단순히 BETWEEN 앞에 NOT만을 붙여 NOT BETWEEN 연산자를 활용하면 됩니다.

SELECT * FROM example_table WHERE NOT BETWEEN 1 AND 9;

이처럼 비교 연산자는 범위 연산자로 대체 가능하며, 범위 연산자를 활용하여 다양한 곳에 활용할 수 있습니다.

조건문 함수

CASE 함수

CASE 함수는 SQL에서 사용하는 가장 기본적인 조건문 함수로, 이 함수를 사용하여 컬럼에 특정한 조건을 부여할 수 있습니다.

CASE
  WHEN <조건문1> THEN <결과문1>
  WHEN <조건문2> THEN <결과문2>
  ELSE <결과문3>
END

COALESCE 함수

COALESCE() 함수는 주로 데이터를 조회할 경우, NULL 값을 다른 기본 값으로 대체할 때 자주 사용됩니다.

COALESCE(<매개변수1>, <매개변수2>, ...)

매개변수를 순서대로 평가하여 NULL이 아닌 첫 번째 매개변수를 반환합니다.

NULLIF 함수

NULL을 이용하는 가장 기본적인 조건문 함수입니다. 0을 NULL 값으로 바꾸고 싶을 때는 NULLIF()를 사용합니다.

NULLIF(<매개변수1>, <매개변수2>)

<매개변수1>과 <매개변수2>가 같은 경우 NULLIF() 함수는 NULL을 반환하고 서로 다를 경우 매개변수1을 반환합니다.

배열 연산자와 함수

배열 연산자

기본적으로 배열도 마찬가지로 비교 연산자를 사용할 수 있습니다.

SELECT ARRAY[5.1, 1.6, 3]::INTEGER[] ARRAY[5, 2, 3] AS result;
 result
--------
 false
(1개 행)
 
SELECT ARRAY[5, 3, 3] > ARRAY[5, 2, 4] AS result;
 
 result
--------
 false
(1개 행)

하지만 이전과 구별되는 차이는 순서대로 원소끼리 비교하게 된다는 점입니다.

배열 연산자에서 포함관계를 확인하는 방법으로 <@, @> 연산자를 사용합니다. 또한, 원소 단위로 겹침 유무를 확인하는 && 연산자가 있습니다. 만약 두 배열을 비교하여 하나라도 겹치는 원소가 있다면 결과는 참을 반환합니다.

연산자설명예시결과

<@ 또는 @>	포함관계	ARRAY[1,2,3] @> ARRAY[1,3]	참
&&	겹침 유/무	ARRAY[1,2,3,4] && ARRAY[1,5,6]	참

배열끼리 병합하고 싶을 때 또는 원소를 추가하고 싶을 때 || 연산자를 사용합니다.

연잔자설명예시결과

||	배열끼리 병합	ARRAY[1,2,3] || ARRAY[1,3]	{1,2,3,1,3}
	2차원 배열로 병합	ARRAY[[1,2,3],[4,5,6] || ARRAY[7,8,9]	{{1,2,3},{4,5,6},{7,8,9}}
	원소 배열 병합	1 || ARRAY[2,3,4]	{1,2,3,4}

만약 배열 안에 배열을 넣고 싶다면 2차원 배열을 사용하면 됩니다. ({{}}, ARRAY[[]])

배열 함수

배열 속에 원소를 추가하는 방법으로 || 연산자를 이용함으로써 원소를 배열에 병합시켜 원소를 추가하였지만, array_append() 함수를 이용하여 추가시킬 수 있습니다.

array_peend(<배열>, <원소>)

첫 번째 매개변수로 원소를 추가할 <배열>을 놓고 두 번째 매개변수로 추가할 <원소>를 넣습니다.

보통은 새로 추가할 원소를 맨 뒤로 넣어주었지만, 때로는 배열 맨 앞에 추가하고 싶을 때가 있습니다. 이 때 사용하는 함수가 바로 array_prepend() 함수입니다.

array_prepend(<원소>, <배열>)

배열 속 원소를 삭제하는 방법으로 array_remove() 함수를 사용합니다.

array_remove(<배열>, <원소>)

배열 속의 원소를 다른 원소로 바꿀 때 array_replace() 함수를 사용합니다.

array_replace(<배열>, <원소1>, <원소2>)

첫 번째 매개변수는 기존 <배열>을 입력받고, 두 번째 매개변수로 바꿀 기존 배열의 <원소1>을 입력받습니다. 세 번째 매개변수로 두 번째 매개변수와 바꿀 <원소2>을 넣어주면 됩니다.

배열과 배열을 합치는 연산자로 || 연산자를 이용할 수 있지만, array_cat() 함수를 이용하여 병합할 수 있습니다. 매개변수로는 <배열1>과 <배열2> 2개의 매개변수를 입력받아 두 배열을 병합합니다.

함수설명예시결과

array_append()	배열의 맨 뒤에 원소를 추가	array_append(ARRAY[1,2], 3)	배열
array_prepend()	배열의 맨 앞에 원소를 추가	array_prepend(1, ARRAY[2,3])	배열
array_remove()	배열의 특정 원소를 삭제	array_remove(ARRAY[1,2], 1)	배열
array_replace()	배열의 특정 원소를 다른 원소와 대체	array_replace(ARRAY[1,3], 3, 2)	배열
array_cat()	두 배열을 병합	array_cat(ARRAY[1,2], ARRAY[3,4])	배열

JSON 연산자와 함수

JSON에는 JSON 데이터 타입과 JSONB 데이터 타입이 있습니다. JSON, JSONB 모두 쓸 수 있는 연산자가 있는가 하면, JSONB에만 쓸 수 있는 연산자와 함수가 있습니다.

JSON 연산자

JSON, JSONB 공통 연산자

JSON에 저장된 키 값으로 밸류 값을 가져오고 싶을 때는 → 연산자를 활용하면 됩니다.

'{"키 값1": "밸류 값1", "키 값2": "밸류 값2"}' -> "키 값1"

다음과 같이 → 연산자로 JSON 배열 속에 있는 JSON 원소를 인텍스를 활용하여 불러올 수 있습니다.

[{"키 값1": "밸류 값1"}, {"키 값2": "밸류 값2"}] -> 인덱스 번호

JSON을 TEXT로 불러오려면 ->> 연산자를 사용합니다.

SELECT '{"p": {"1":"postgres"}, "s":{"1":"sql"}}'::json ->> 'p' AS result;

만약 복잡한 다층 구조로 이루어져 있다면 #> 연산자를 사용합니다. 이 연산자는 특정한 경로를 지정해 데이터 값을 불러올 수 있습니다.

연산자설명결과

->	JSON 오브젝트에서 키 값으로 밸류 값을 불러오기	JSON
->	JSON 배열에서 인덱스로 JSON 오브젝트를 불러오기	JSON
->>	JSON 오브젝트, JSON 배열 속 데이터 텍스트로 불러오기	TEXT
#>	특정한 경로의 값을 가져옴	JSON
#>>	특정한 경로의 값을 TEXT 데이터 타입으로 가져옴	TEXT

아쉽게도 JSONB가 아닌 JSON 데티터 타입에서 앞서 사용한 기본적인 비교 연산자는 사용할 수 없습니다. 기본적인 비교 연산자와 추가적인 JSON 연산자는 JSONB 데이터 타입에서만 가능합니다.

추가적인 JSONB 연산자

배열과 마찬가지로 JSONB에서도 포함관계 연산자 @>를 사용할 수 있습니다.

결과 또한 배열처럼 불리언 데이터 타입으로 나타납니다. 또한, ? 연산자를 사용하여 가장 바깥 단의 JSONB에 해당하는 문자열의 “키 값”이 존재하는지 물어볼 수 있습니다.
? 연산자와 더불어 가장 바깥 단의 JSONB에 배열 속 원소가 “키 값”으로 1개 이상 존재하는지 물어볼 때는 ?| 연산자를 사용합니다.
?| 연산자와는 다르게 가장 바깥 단의 JSONB에 배열 속의 원소가 “키 값”으로 모두 존재하는 지 물어볼 때는 ?& 연산자를 사용합니다.

배열과 마찬가지로 || 연산자를 사용하여 JSONB와 JSONB를 병합할 수 있습니다.

하나 또는 복수의 원소를 삭제하고 싶을 때는 - 연산자를 사용하면 됩니다.
복수의 원소를 삭제하는 방법은 - 연산자의 오른쪽 항에 text[]의 원소로 삭제하고 싶은 복수의 원소를 넣어주면 됩니다.
JSONB 배열에 원소를 삭제하는 방법도 - 연산자를 사용하지만, 오른쪽 항에는 JSONB 배열의 인덱스 번호가 들어갑니다.

연산자설명결과

<@ 또는 @>	JSONB끼리 포함관계를 판별합니다.	참, 거짓 또는 NULL
?	JSONB에 해당 문자열을 키 값으로 존재하는지 판별	참, 거짓 또는 NULL
?|	JSONB에 배열속 원소가 기 값으로 하나 이상 존재하는지 판별	참, 거짓 또는 NULL
?&	JSONB에 배열속 원소가 기 값으로 모두 존재하는지 판별	참, 거짓 또는 NULL
||	2개의 JSONB를 병합	JSONB
-	JSONB 오브젝트의 하나 이상의 원소를 삭제	JSONB
	JSONB 배열의 해당 인덱스 번호의 원소를 삭제	JSONB

JSON 생성과 처리함수

JSON 생성함수

JSON 오브젝트를 만드는 생성함수는 json_build_object()입니다.

json_build_object("<키1>", "<밸류1>", "<키2>", "<밸류2", ...)

만약 JSONB 데이터 타입으로 생성하고 싶다면 다음과 같이 함수 이름의 json을 jsonb로 바꾸면 됩니다.

jsonb_build_object("<키1>", "<밸류1>", "<키2>", "<밸류2", ...)

JSON 배열을 생성하는 법은 JSON 오브젝트와 비슷합니다. 함수 이름의 object를 array로 바꾼 json_build_array() 함수를 이용하면 됩니다. JSON 오브젝트와 마찬가지로 JSONB 형식으로 JSON 배열을 생성하고 싶다면 jsonb_build_array() 함수를 이용하면 됩니다.

json_build_array("<원소1>", "<원소2>", "<원소3>", "<원소4>", ...)

JSON 처리함수

JSON 배열의 원소의 개수를 세려면 json_array_length() 함수를 사용합니다. 매개변수로는 JSON이 들어가고 결과는 정수 형으로 출력됩니다.

SELECT json_array_length('["a", 1, "b", 2, "c"]'::json) AS length;

jsonb 형식으로 저장되어 있다면 함수 이름의 json을 jsonb로 바꾼 jsonb_array_length() 함수를 써 주면 됩니다.

목록을 컬럼으로 정리해서 보고 싶다면 json_each() 함수를 사용합니다. 키 값을 텍스트 데이터 타입의 컬럼으로 밸류 값을 JSON 데이터 타입의 컬럼으로 정리해줍니다. jsonb 데이터 타입으로 저장했다면, jsonb_each() 함수를 사용합니다.

SELECT * FROM
json_each('{"sution":"i like postgresql", "Siyoun": "i like postgresql too"}');

밸류 값도 텍스트로 출력받길 원한다면 json_each_text() 사용하면 되지만 일반적으로 “키 값”에는 데이터를 넣지 않습니다.

목록에서 조회하려면 json_array_elements() 함수를 이용하면 JSON 배열 속 원소를 컬럼으로 불러올 수 있습니다.

SELECT * FROM json_array_elements('[1, "a", {"b":"c"}, ["d",2,3]]');

컬럼의 데이터 타입은 JSON 데이터 타입입니다. TEXT 데이터 타입으로 조쇠하고 싶다면, json_array_elements_text() 함수를 사용하면 됩니다.

날짜 및 시간 연산자와 함수

날짜 및 시간 연산자

날짜에 정수를 더할 수 있고, 이때 정수를 날짜 및 시간 간격으로 간주합니다.

SELECT date '2020-07-15' + integer '7' AS result;

날짜에 시간을 더하면 시간에 날짜가 합하여진 TIMESTAMP가 됩니다.

SELECT date '2020-07-15' + time '13:00' AS result;

뺄셈도 마찬가지로 덧셈과 유사합니다. 날짜와 날짜를 빼면 그 사이 간격을 정수형으로 출력합니다. 날짜와 정수를 빼면 날짜가 나오고, 시간과 시간을 빼면 날짜 간격 및 시간 간격을 나타내는 INTERVAL이라는 데이터 타입의 결과가 출력됩니다.

시간 간격을 나타내는 INTERVAL 데이터 타입에 정수를 곱하면 시간이 출력됩니다.

SELECT 60 * interval '1 second' AS result;

어떤 시간에 정수를 곱할 수도 있습니다.

SELECT 2 * time '2:00' AS result;

날짜에 정수를 곱할 수는 없습니다.

SELECT 2 * date '2020-07-15' AS result;

ERROR: operator does not exist: integer * date

나눗셈을 할 때는 NULLIF를 사용하지 않고 분모가 어떠한 수식에 의해 0이 되지 않게 주의해야 합니다.

SELECT interval '1 hour' / FLOAT '1.2' AS result;

날짜 및 시간 기본 함수

현재 날짜와 시간

current 함수는 현재 날짜 및 시간을 반환하는 함수입니다.

함수설명예시

CURRENT_DATE	현재 날짜 정보를 반환합니다.	SELECT CURRENT_DATE;
CURRENT_TIME	현재 시간+시간대 정보를 반환합니다.	SELECT CURRENT_TIME(2);
CURRENT_TIMESTAMP	현재 날짜 및 시간+시간대 정보를 반환합니다.	SELECT CURRENT_TIMESTAMP(2)

CURRENT_TIME, CURRENT_TIMESTAMP 함수는 시간대 정보를 포함합니다. CURRENT_TIMESTAMPTZ는 정의되지 않은 함수이니 헷갈리지 않도록 주의합니다.

시간대 정보 없이 현재 시간 값을 출력하고 싶다면 LOCALTIME과 LOCALTIMESTAMP 함수를 사용합니다.

함수설명예시

LOCALTIME	현재 시간 정보를 반환합니다.	SELECT LOCALTIME(2);
LOCALTIMESTAMP	현재 날짜 및 시간 정보를 반환합니다.	SELECT LOCALTIMESTAMP;

CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME과 LOCALTIMESTAMP 뒤에 괄호를 붙여 초단위의 소수 값을 제한할 수 있습니다. 만약 제한하지 않고 그냥 쓰게 된다면 초 단위의 소수점 다음 최대 6자리 값을 반환합니다.

다음은 표준 SQL이 아닌 함수 중, 현재 시간을 불러오는 함수로 자주 쓰이는 now()와 timeofday() 함수가 있습니다. now() 함수는 현재 트랜잭션이 시작할 때의 시간을 반환하지만, tiemofday() 함수는 현재 작업이 시작할 때의 시간을 반환합니다.

날짜 및 시간 응용 함수

EXTRACT, date_part() 함수

EXTRACT(<필드 값> FROM <날짜 및 시간 정보>)

EXTRACT() 함수는 주어진 <날짜 및 시간 정보>에서 원하는 <필드 값>을 검색한다.

필드 값설명

CENTURY	세기
QUARTER	분기(1년을 4분기로 나눔)
YEAR	연도
MONTH	월
DAY	일
HOUR	시간
MINUTE	분
SECOND	초
ISODOW	요일 숫자(월요일(1) ~ 일요일(7))
DOW	요일 숫자 (일요일(0) ~ 토요일(6))
TIMEZONE	시간대

EXTRACT()와 유사한 함수로 date_part() 함수가 있습니다. 이 함수는 마찬가지로 2개의 매개변수를 받는데 <필드 값>과 <날짜 및 시간 정보>입니다. 필드 값은 문자열로 받습니다.

SELECT date_part('quarter', now());

date_trunc 함수

테이블에서 원하는 정보만 남기고 나머지 모든 정보를 0으로 없애고 싶다면 date_trunc() 함수를 사용합니다.

date_trunc() 함수는 2개의 매개변수를 인자로 받는데, 첫 번째 매개변수로 <필드 갓ㅂ>을 받고 두 번째 매개변수로 <날짜 및 시간 정보>를 받습니다.

SELECT id, date_trunc('month', times) FROM 'timebox';

<필드 값>은 ISODOW, DOW, TIMEZONE을 제외한 모든 <필드 값>을 사용할 수 있습니다.

자주 쓰이는 연산자와 함수

서브쿼리 연산자

연산자설명결과

EXISTS (서브쿼리)	서브쿼리의 로우가 존재하면 참이다	참 또는 거짓, Null
<표현> IN (서브쿼리)	서브쿼리의 로우 값 중 하나라도 표현식과 같다면 참이다.	참 또는 거짓, Null
<표현> NOT IN (서브쿼리)	서브쿼리의 로우 값 중 하나라도 표현식과 다르다면 참이다.	참 또는 거짓, Null
<표현> <비교 연산자> ANY (서브쿼리)	서브쿼리의 로우 값 중 하나라도 표현식과 같다면 참이다.	참 또는 거짓, Null
<표현> <비교 연산자> ALL (서브쿼리)	서브쿼리의 로우 값 모두가 표현식과 같다면 참이다.	참 또는 거짓, Null

EXISTS 연산자

EXISTS (서브쿼리)

EXISTS 연산자는 서브쿼리가 하나 이상 로우를 반환하면 true 결과를 내고, 아무것도 반환하지 않으면 false 결과를 냅니다. 또한, 적어도 하나 이상의 로우를 반환하는지만 확인하기 때문에 서브쿼리의 출력 내용에 대해서는 중요하게 생각하지 않는 경우가 많습니다. 만약 로우 데이터를 변경시키는 함수가 포함된 서브쿼리라면 EXISTS 연산자를 사용할 수 없습니다. 즉, 데이터 반환이 명확한 순간에는 사용할 수 있습니다.

IN 연산자와 NOT IN 연산자

<표현> IN (서브쿼리)

IN 연산자를 사용하기 위해서는 반드시 서브쿼리에서 하나의 컬럼 또는 특정한 값을 반환해야 합니다. 서브쿼리에서 같은 로우가 하나라도 있다면 true 결과를 내고 없으면 false 결과를 냅니다. 서브쿼리가 로우를 반환하지 ㅇ낳는 경우에는 false 결과를 냅니다.

<표현> NOT IN (서브쿼리)

NOT IN 연산자는 IN 연산을 반대로 합니다. 서브쿼리에서는 반드시 하나의 컬럼 또는 특정한 값을 반환해야 합니다. 서로 같지 않은 서브쿼리 로우가 하나라도 있으면 treu 결과를 내고 동일한 로우가 있으면 false 결과를 냅니다.

ANY와 SOME 연산자

<표현> <비교 연산자> ANY (서브쿼리)
<표현> <비교 연산자> SOME (서브쿼리)

두 연산자는 서로 같은 의미이기 때문에 ANY와 SOME은 둘중 어떤 것을 사용해도 상관없습니다. ANY와 SOME 연산자를 사용하기 위해서는 서브쿼리에서 반드시 하나의 컬럼 또는 특정한 값을 반환해야 합니다. IN 연산자와 동일한 역할을 하지만 추가로 비교 연산자를 함께 사용합니다. 따라서 서브쿼리의 값 중 하나라도 같은 값이 있다면 true 결과를 내고, 없다면 fasle 결과를 냅니다.

ALL 연산자

<표현> <비교 연산자> ALL (서브쿼리)

ALL 연산자를 사용하기 위해서 서브쿼리는 반드시 하나의 컬럼 또는 특정한 값을 반환해야 합니다. <표현>과 <서브쿼리>를 비교했을 때 모든 값이 동일하다면 ALL은 true 결과를 냅니다. 또한 다음과 같이 입력하면 NOT IN과 동일한 역할을 합니다.

<표현> <>ALL (서브쿼리)

패턴매칭 연산자

LIKE 연산자

LIKE 연산자는 조회해야 하는 정보의 부분만을 알고 있을 때 사용할 수 있는 연산자입니다. 특정한 패턴을 가지고 있는 값들만들 조회할 수 있게 해줍니다.

조회할 컬럼명 LIKE '패턴';

위와 같이 입력하면 조회할 컬럼명에서 설정한 패턴을 가지고 있는 값들을 추려낼 수 있습니다. 또 다음과 같은 형태로도 사용할 수 있습니다.

특정 값 LIKE '패턴';

위와 같이 조회하면 특정 값과 패턴이 일치하면 참, 불일치하면 거짓으로 결과값이 나오게 됩니다. postgreSQL은 와일드카드 문자라는 것을 지원하여 패턴을 만들 때 사용할 수 있게 해줍니다. postgreSQL이 지원하는 와일드 카드 문자는 ‘%', ‘_’ 두가지 입니다. ‘%’는 문자열을 대체할 수 있고, '_’는 하나의 문자만을 대체할 수 있습니다. 이때, 문자열의 길이는 상관이 없습니다. 패턴은 와일드카드의 위치에 따라서도 다르게 만들 수 있습니다.

postgreSQL은 ILIKE 연산자도 지원합니다. ILIKE 연산자는 LIKE 연산자에서 대소문자를 구분하지 않는 것입니다. 또한 LIKE를 쓰지 않더라도 ‘~'과 ‘*’, '!’ 등의 연산자를 사용하여 LIKE, ILIKE, NOT LIKE, NOT ILIKE 등을 표현할 수 있습니다.

연산자동일 연산자

LIKE	~~
NOT LIKE	!~~
ILIKE	~~*
NOT ILIKE	!~~*

SIMILAR TO 연산자

SIMILAR TO 연산자는 표준 SQL 정규 표현 정의를 사용하여 패턴을 해석한다는 점에서 LIKE 연산자와 차이가 있을 뿐, 나머지는 유사합니다. SIMILAR TO 연산자는 LIKE 연산자와 달리 POSIX 정규식으로부터 추가적인 메타문자를 가져와 쓸 수 있습니다. 메타문자는 정규식에서 쓰는 기호입니다. 대표적으로 ‘|' 메타문자를 사용할 수 있는데, '|’는 둘 중 하나의 의미를 가진 메타문자입니다.

WHERE student_name SIMILAR TO '(Kim|LEE)%';

이 외에도 ‘*', ‘+’, '?’ 등의 다양한 메타문자를 사용할 수 있습니다.

POSIX 정규식

POSIX 정규식은 특정한 패턴을 가진 문자열의 집합을 표현하는 데 쓰입니다. 즉, POSIX 정규식은 패턴 매칭에 쓰이고, LIKE, SIMILAR TO보다 더 다양하고 강력한 특성을 지닙니다. 정규식에서 쓰는 메타문자는 앞서 말한 LIKE, SIMILAR TO에서 쓰는 와일드카드 문자와 차이가 잇습니다. 한 글자만을 나타내는 ‘_’가 메타문자에서는 '.’으로 표현됩니다. 정규식은 하나의 통일된 표준이 없고 종류가 다양합니다. POSIX BRE, POSIX ERE, VIM 등 다양한 정규식 버전이 있으니, 해당 버전의 자세한 문서를 참고하는 것이 좋습니다.

SQL 문자열 연산자와 함수

병합 연산자

“문자열 데이터 타입”에서도 || 연산자를 사용 가능합니다.

SELECT 'postgre' || 'sql' AS result, 'price: 'price: '||300||'won' AS result2;

문자열 데이터 타입과 문자열이 아닌 데이터 타입을 병합할 수도 있습니다. 다만 출력된 결과는 문자열 데이터 타입입니다.

문자열 함수

문자열 수를 셀 수 있는 length() 함수가 있습니다.

SELECT length('postgresql') AS length;

데이터를 전달받아 각각 다른 데이터를 따로 분리하여 다른 컬럼으로 저장하고 싶다면 분리할 때 subtring() 함수를 사용합니다.

SELECT
substring('sujin_01012345678' from 1 for 5),
substring('sujin_01012345678' from 7 for 16);

정수형을 써서 위치를 나타냈지만, 패턴 매칭에서 배웠던 정규식이나 패턴을 사용해도 됩니다.

문자열 앞의 몇 글자만 보여주고싶다면 left() 함수를 사용합니다.

SELECT left('죽는 날까지 하늘을 우러러 한점 부끄럼이 없기를 잎새에 이는 바람에도 나는 괴로워했다', 15);

|| 연산자를 사용해서 문자열을 결합할 수 있지만 concat() 함수도 있습니다. 보통 이 함수는 다수의 문자열을 병합해야할 때 사용합니다. 만약 concat() 함수의 인자로 null 값이 있을 경우 무시하고 지나갑니다.

SELECT concat('my sql', 'language', 'is', null, 'postgresql')

문자열 내에서 특정 문자열의 위치를 찾을 때는 position() 함수를 사용하면 됩니다.

SELECT position('postgre' in 'my sql language is postgresql');

이렇게 위치를 찾아낸 후 문자열의 특정 부분을 변경할 때는 replace() 함수를 사용할 수 있습니다. 이 함수의 매개변수에는 문자열-기존 문자열의 바꿀부분-바꿀 문자열 순서로 오면 됩니다.

SELECT('my sql language is postgresql', 'postgre', 'not My')