🐘 PostgreSQL 기본 04 - 데이터 타입 이해

📙 『실용 SQL』을 읽고 정리한 글입니다.

개요

• 앞으로 많이 보게 될 데이터 타입은 다음과 같다.

1. 문자: 글자와 기호
2. 숫자: 정수와 소수를 포함한 모든 수
3. 날짜와 시간: 시간 정보

문자형 데이터 타입 이해하기

• 문자형 데이터 타입은 문자, 숫자, 기호의 조합에 사용하기 적합한 일반적인 타입이다.
• 문자형 데이터 타입은 다음과 같다.

1. char(n)
   • 입력한 n에 따라 길이가 고정된 열이 정의된다.
   • 가령 char(20)으로 설정된다면 글자를 아무리 많이 삽입하더라도 각 행에 20자만 보관된다.
   • 만약 20자보다 적게 입력하면 PostgreSQL이 나머지 공간을 공백으로 채운다.
   • character(n)으로 정의할 수도 있으며, 요즘엔 거의 쓰이지 않는다.
   • 표준 SQL에 포함된다.
2. varchar(n)
   • 최대 길이가 n으로 정의되는 가변 길이 데이터 타입이다.
   • 최대치보다 적은 수의 글자를 입력하더라도 PostgreSQL은 공백을 추가하지 않으므로 데이터 공간을 절약할 수 있다.
   • character varying(n)으로 정의할 수도 있다.
   • 표준 SQL에 포함된다.
3. text
   • 길이 제한이 없는 가변 길이 데이터 타입이다.
   • 가장 길게 담을 수 있는 데이터 크기는 1GB이다.
   • 표준 SQL에 포함되지 않는다.

문자 타입 열에 저장된 숫자에는 수학 연산을 수행할 수 없다. 문자 타입 열에는 우편번호 같은 일련번호를 저장할 때만 숫자를 사용하는 것이 좋다.

CREATE TABLE char_data_types (
	char_column char(10),
	varchar_column varchar(10),
	text_column text
);
  
INSERT INTO char_data_types
VALUES
	('abc', 'abc', 'abc'),
	('defghi', 'defghi', 'defghi');

COPY char_data_types TO 'C:\YourDirectory\typetest.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|');

• 문자 데이터 타입이 각각 어떻게 저장되는지 확인하기 위한 예제 코드이다.
• COPY table_name FROM은 가져오기 기능을 수행하며, TO path는 내보내기 기능을 수행한다.
• WITH 키워드 옵션을 사용해 파일 안 데이터의 각 열을 파이프 문자로 구분한 형식으로 변경한 것이다.

char_column|varchar_column|text_column
abc       |abc|abc
defghi    |defghi|defghi

• 위 파일을 보면 char형의 경우 공백을 채워 매번 10자를 출력하는 것을 알 수 있다.
• 지금은 각 열에서 사용되지 않는 공간들이 무시해도 될 정도로 작게 느껴지지만, 프로젝트가 커져 수십 개의 테이블에 있는 수백만 개의 행을 다루게 될 때는 더 효율적인 공간 관리가 필요해질 것이다.
• 문자를 저장할 때는 text 타입을 사용하는 것을 권장한다.
• 최대 길이를 지정하지 않아도 되기 때문에 추후 문자 타입 열에 대한 요구 사항이 변경되더라도 테이블을 수정할 필요가 없다.

숫자형 데이터 타입 이해하기

• 연산을 하거나 번호 순으로 나열해야 한다면 문자가 아닌 숫자 타입을 활용해야 한다.
• 숫자 데이터 타입은 다음과 같다.

1. 정수: 양수와 음수, 0
2. 고정 소수점과 부동 소수점: 실수를 표현하는 두 가지 형태

정수

• 표준 SQL은 smallint, integer, bigint라는 세 개의 정수를 제공한다.
• 세 타입의 차이점은 넣을 수 있는 숫자의 최대 크기다.

데이터 타입	저장 크기	최소값	최대값
smallint	2바이트	-32,768	32,767
integer	4바이트	-2,147,483,648	2,147,483,647
bigint	8바이트	-9,223,372,036,854,775,808	9,223,372,036,854,775,807

• 데이터 용량을 고려하여 적절한 데이터 타입을 선택해야 하지만, 데이터 타입을 벗어난 숫자를 열에 입력할 경우, 데이터베이스는 실행을 중지하고 out of range 오류를 반환한다.
• 시리얼 타입과 같은 자동 증가 정수 열을 만들면 테이블에 각 행에 기본 키라는 고유한 ID 번호를 생성할 수 있다.
• PostgreSQL에서 자동 증가 정수 열을 만드는 방법은 두 가지가 있다.
• 하나는 시리얼 데이터 타입으로, 이는 ANSI SQL 표준인 자동 증가 정수를 PostgreSQL에서 고유 구현한 데이터 타입이다.
• 다른 하나는 ANSI SQL 표준인 IDENTIIY 키워드이다.

시리얼 타입을 사용한 자동 증가

CREATE TABLE people (
	id serial,
	person_name varchar(100)
);

데이터 타입	저장 크기	최소값	최대값
smallserial	2바이트	1	32,767
serial	4바이트	1	2,147,483,647
bigserial	8바이트	1	9,223,372,036,854,775,807

IDENTITY 키워드를 사용한 자동 증가

CREATE TABLE people (
	id integer GENERATED ALWAYS AS IDENTITY,
	person_name varchar(100)
);

• IDENTITY 키워드를 사용할 경우 코드는 더 길지만 시리얼보다 선호된다.
• 이는 IDENTITY 타입이 다른 데이터베이스 시스템과 호환되며, 사용자가 실수로 해당 열에 값을 삽입하지 못하도록 방지하기 때문이다.
• 시리얼 타입은 데이터 삽입을 방지하지 않는다.
• IDENTITY 키워드를 사용하는 다음 두 가지 방법이 있다.

1. GENERATED ALWAYS AS IDENTITY
   • 항상 자동 증가 정수 삽입, 사용자 임의 삽입 불가
2. GENERATED DEFAULT AS IDENTITY
   • 사용자가 임의로 삽입하지 않는 경우 자동 증가 정수 삽입
   • 이 옵션을 사용하면 값이 중복될 수 있으므로 키 열로 사용하면 문제가 될 수 있음

행이 추가될 때마다 자동으로 증가하는 시리얼 타입이 적용된 열이더라도 어떤 경우에는 그 값에 틈이 생길 수 있다. 가령 하나의 행이 삭제될 경우, 그 행에 할당됐던 값은 다시 대체되지 않는다. 행 삽입이 취소되더라도 해당 열의 시퀀스는 계속해서 이어진다.

소수

• SQL 데이터베이스에서 소수는 고정 소수점과 부동 소수점 데이터 타입으로 처리할 수 있다.

고정 소수점

• 임의 정밀도 타입으로도 불린다.
• numeric(precision, scale) 형태로 선언한다.
• 인수인 precision은 입력될 숫자의 전체 자릿수를, scale은 소수점 아래 자릿수를 적는다.
• decimal(precision, scale)으로도 고정 소수점 형식으로 지정할 수 있다.
• 두 방식 모두 ANSI SQL 표준에 포함되어 있다.
• precision 인수 값을 생략할 경우 데이터베이스의 최대 한도를 기준으로 숫자를 저장한다.
• scale 인수 값을 생략할 경우 0 값을 가지며, 이는 정수를 의미한다.

부동 소수점

• 부동 소수점 타입으로는 real, double precision이 있다.
• 이 둘의 차이점은 담을 수 있는 데이터 크기이다.
• real 타입은 소수점 이하 6자리까지 정밀도를 허용하며, double precision은 소수점 아래 15자리까지 정밀도를 허용한다.
• 이 부동 소수점 타입은 가변 정밀도 타입이라고도 한다.
• 데이터베이스는 입력된 값을 유효 숫자와 10의 지수를 나타내는 숫자(소수점의 위치)로 나누어 보관한다.
• 그러므로 열에 적힌 소수점의 위치는 숫자에 따라 움직일 수 있게 된다.

구분	데이터 타입	크기	저장 방식	범위
고정 소수점	numeric(precision, scale), decimal(precision, scale)	가변 길이	내부적으로 가변 길이 2진수로 저장, 정확한 10진수 표현 가능	-10^131072 ~ +10^131072 (소수 자릿수 최대 16383 자리)
부동 소수점	real	4바이트	IEEE 754 단정도(32비트) 부동 소수점	약 ±1.18E-38 ~ ±3.40E38, 소수점 약 6자리 정밀도
부동 소수점	double precision	8바이트	IEEE 754 배정도(64비트) 부동 소수점	약 ±2.23E-308 ~ ±1.80E308, 소수점 약 15자리 정밀도
부동 소수점	float(p)	4~8바이트	p ≤ 24 → real, p > 24 → double precision	p 값에 따라 real 또는 double precision의 범위와 정밀도 적용

CREATE TABLE number_data_types (
	numeric_column numeric(20,5),
	real_column real,
	double_column double precision
);

INSERT INTO number_data_types
VALUES
	(.7, .7, .7),
	(2.13579, 2.13579, 2.13579),
	(2.1357987654, 2.1357987654, 2.1357987654);

SELECT * FROM number_data_types;

• numeric, real, double precision 세 개의 데이터 타입이 어떻게 다른지 살펴 보기 위한 예제이다.

numeric_column	real_column	double_column
0.7	0.7	0.7
2.13579	2.13579	2.13579
2.1358	2.1357987	2.1357987654

• numeric 타입은 다섯 자리보다 많이 입력할 경우 반올림 처리한다.
• real 타입은 클라이언트 별로 소수점 아래 6 ~ 7자리까지 정확한 결과를 출력한다.
• double precision 타입은 소수점 아래 15자리까지 정확한 결과를 출력한다.
• 컴퓨터가 부동 소수점을 보관하는 방식 때문에 의도치 않은 수학적 오류로 이어질 수도 있다.

SELECT
numeric_column * 10000000 AS fixed,
real_column * 10000000 AS floating
FROM number_data_types
WHERE numeric_column = .7;

fixed	floating
7,000,000	6,999,999.88079071

• 앞서 만든 테이블에서, 천 만을 곱한 뒤 0.7 값이 들어있던 첫 번째 줄만 필터링했는데, 쿼리의 리턴 값이 다르다.
• 부동 소수점이 이러한 오류를 뱉는 이유는 컴퓨터가 유한한 숫자들 안에 대량의 정보를 집어 넣으려고 하기 때문이다.
• 숫자 데이터 타입에 필요한 저장 공간은 소수점 앞뒤 자릿수에 따라 달라진다.
• 때에 따라 numeric 타입이 부동 소수점 타입보다 더 많은 공간을 차지할 수도 있다.
• 만약 몇 십만 개의 행을 다루는 작업을 하고 있다면, 비교적 부정확한 부동 소수점 계산 없이 처리할 수 있는지 고려해보는 것이 좋다.

필요한 숫자 데이터 타입을 선택하는 법

• 가능한 정수 타입을 선택하자.
• 소수점 데이터를 다루는데 계산이 정확해야 한다면 numeric, decimal 타입으로 사용하자.
• 부동 소수점 타입은 공간을 절약할 수 있지만 계산의 정확도가 떨어지므로 계산의 정확도가 중요하지 않을 때만 사용해야 한다.
• 충분히 큰 숫자 타입을 선택하자.
• 가령 numeric, decimal을 사용할 땐 precision을 충분히 크게 하여 소수점 위나 아래로 자릿수가 다 담길 수 있도록 확보하는 것이 중요하다.
• 정수를 다룰 때는 bigint를 사용하되, 확신이 든다면 integer나 smallint를 활용해도 좋다.

날짜와 시간 타입 이해하기

데이터 타입	크기	설명	범위
date	4바이트	날짜(연-월-일)만 저장	기원전 4713-01-01 bc ~ 5874897-12-31 ad
time(precision)	8바이트	하루 중 시간(시:분:초.소수초)만 저장, 타임존 정보 없음	00:00:00 ~ 24:00:00 (소수 초 정밀도 최대 6자리)
time(precision) with time zone	12바이트	하루 중 시간 + 타임존 저장	00:00:00+1459 ~ 24:00:00-1459
timestamp(precision)	8바이트	날짜 + 시간 저장, 타임존 정보 없음	4713 bc ~ 294276 ad (소수 초 정밀도 최대 6자리)
timestamp(precision) with time zone	8바이트	날짜 + 시간 + utc 기준 타임존 보정 정보 저장	4713 bc ~ 294276 ad (소수 초 정밀도 최대 6자리)
interval [fields] (precision)	16바이트	시간 간격(년, 월, 일, 시, 분, 초) 저장	약 ±178000000년 (소수 초 정밀도 최대 6자리)

• 언제 어떤 일이 발생했는지에 대한 문제는 일반적으로 누가, 무엇을, 얼마나 많은 사람이 참여했는지 만큼이나 가치 있는 문제기 때문에 데이터를 사용한 스토리텔링에 필수적이다.

timestamp

• 추적할 수 있는 다양한 상황에서 유용한 날짜와 시간을 기록한다.
• 여객기의 출발, 타임라인에 다른 사건 정리 등 이벤트 시간 기록에 해당 이벤트가 발생한 시간대가 포함되도록 키워드를 추가하는 것이 좋다.
• 그렇지 않으면 전 세계 여러 곳에서 기록된 시간을 비교할 수 없다.
• timestamp with time zone 타입은 표준 SQL의 일부로 PostgreSQL에서는 timestampz로도 그와 동일한 데이터 타입을 지정할 수 있다.

date

• 날짜만 기록하며, SQL 표준의 일부이다.

time

• 시간만 기록하며, SQL 표준의 일부이다.
• with time zone을 추가할 수 있지만, 날짜가 없다면 무의미하다.

interval

• 수량 단위 형식으로 표현된 시간 단위를 나타내는 값을 보유한다.
• 기간의 시작 또는 끝은 기록하지 않고, 길이만 기록한다.
• 일반적으로 interval 데이터 타입은 다른 날짜나 시간 열의 계산 또는 필터링에 사용된다.
• SQL 표준의 일부지만 PostgreSQL 전용 구문은 더 많은 기능을 제공한다.

CREATE TABLE date_time_types (
	timestamp_column timestamp with time zone,
	interval_column interval
);

INSERT INTO date_time_types
VALUES
	('2022-12-31 01:00 EST','2 days'),
	('2022-12-31 01:00 -8','1 month'),
	('2022-12-31 01:00 Australia/Melbourne','1 century'),
	(now(),'1 week');

SELECT * FROM date_time_types;

timestamp_column	interval_column
2022-12-31 15:00:00.000 +0900	2 days
2022-12-31 18:00:00.000 +0900	1 mon
2022-12-30 23:00:00.000 +0900	100 years
2025-08-14 14:14:02.601 +0900	7 days

• 두 타입에 대한 테이블을 만들고 행 4개를 삽입했다.
• 날짜와 시간 정보를 삽입할 때 ISO 형식 YYYY-MM-DD HH:MM:SS에 맞는 값을 입력하였다.
• SQL은 MM/DD/YYYY 등 다양한 날짜 형식을 지원하지만 세계적인 호환성을 위해 ISO를 권장한다.
• 첫 번째 행에서는 미국 동부 표준시의 약어인 EST를 사용한다.
• 두 번째 행에서는 시간대를 -8 값으로 설정하여 UTC 기준 8시간 늦은 시간대로 지정한다.
• 세 번째 행에서는 Australia/Melbourne으로 영역과 위치의 이름을 사용하여 시간대를 지정한다.
• 네 번째 행에서는 날짜, 시간 및 시간대를 지정하는 대신 하드웨어에서 현재 트랜잭션 시간을 캡쳐하는 now() 함수를 사용한다.
• 처음 세 행의 timestamp_column 열에 동일한 날짜와 시간을 제공했음에도 타임존이 다르면 저장되는 UTC와 출력값은 모두 달라진다.
• 마지막으로 PostgreSQL은 간격 표시에 대한 기본 설정으로 1 century를 100 years로, 1 week을 7 days로 변경한다.

interval 데이터 타입을 통해 날짜 계산하기

• interval 데이터 타입은 날짜 및 시간 데이터와 관련된 간단한 계산에 유용하다.
• 예를 들어, 고객이 계약을 체결한 날짜를 포함하는 열이 있다고 가정하자.
• 여기서 interval 데이터 타입의 데이터로 각 계약 날짜에 90일을 추가하여 클라이언트 후속 조치 시기를 정할 수 있다.

SELECT
	timestamp_column,
	interval_column,
	timestamp_column - interval_column AS new_date
FROM date_time_types;

timestamp_column	interval_column	new_date
2022-12-31 15:00:00.000 +0900	2 days	2022-12-29 15:00:00.000 +0900
2022-12-31 18:00:00.000 +0900	1 mon	2022-11-30 18:00:00.000 +0900
2022-12-30 23:00:00.000 +0900	100 years	1922-12-30 23:00:00.000 +0900
2025-08-14 14:14:02.601 +0900	7 days	2025-08-07 14:14:02.601 +0900

• 여기서 timestamp_column - interval_column 결과인 new_date와 같이 계산된 열을 표현식이라고 하며, 이 기법을 자주 사용한다.
• new_date 열은 기본적으로 timestamp with time zone 타입으로 포맷되어 시간 값을 표시하는데, interval 값에서 날짜가 사용되면 날짜까지 표시한다.

JSON과 JSONB 이해하기

{
	"business_name": "Old Ebbitt Grill",
	"business_type": "Restaurant",
	"employees": 300,
	"address": {
		"street": "675 15th St NW",
		"city": "Washington",
		"state": "DC",
		"zip_code": "20005"
	}
}

• PostgreSQL에는 JSON용 데이터 타입이 두 가지 있다.

1. json: JSON 텍스트를 그대로 저장
2. jsonb: JSON 텍스트를 바이너리 형식으로 변환하여 저장

• PostgreSQL은 현재 SQL 표준에 있는 여러 기능을 구현하지만 자체적으로 JSON 관련 기능과 연산자를 제공한다.
• json과 다르게 jsonb는 인덱싱을 지원하므로 처리 속도가 빠르다.

그 외 타입들 사용하기

• PostgreSQL에는 문자, 숫자, 날짜 외에도 다양한 타입들이 있다.

1. boolean 타입: true, false
2. geometric 타입: 점, 선, 원 및 기타 2차원 개체를 포함
3. text search 타입: PostgreSQL 전문 검색 엔진용 텍스트 검색 타입
4. network address 타입: IP 또는 MAC 주소
5. UUID 타입
6. range 타입: 정수 또는 타임스탬프 같은 범위를 지정
7. 바이너리 데이터를 저장하는 타입
8. 구조화된 형식으로 정보를 저장하는 XML 및 JSON 타입

CAST를 통해 데이터 타입 변환하기

SELECT timestamp_column, CAST(timestamp_column AS varchar(10))
FROM date_time_types;

SELECT numeric_column,
	   CAST(numeric_column AS integer),
	   CAST(numeric_column AS text)
FROM number_data_types;

-- 오류 발생
SELECT CAST(char_column AS integer) FROM char_data_types;

• 종종 어떤 값의 데이터를 기존에 저장된 것과 다른 타입으로 변환해야 하는 경우가 있다.
• 예를 들어 숫자와 문자를 결합하기 위해 숫자를 문자 타입으로 반환받고 싶을 수 있다.
• 이처럼 지정된 데이터 타입이 있는 값을 다른 타입으로 변환하는 일은 CAST() 함수를 사용하여 수행할 수 있다.
• CAST() 함수는 대상 데이터 타입이 원래 값을 수용할 수 있을 때만 성립한다.
• 가령 문자나 텍스트 타입을 숫자 타입으로 변환할 수는 없다.
• 첫 번째 예제는 timestamp_column 값을 varchar 타입으로 변환해 앞에서부터 10자만 유지한다.
• 시간 부분을 제외하고 날짜만 확인하고 싶을 때 유용할 것이다.
• 두 번째 예제는 numeric_column를 다른 형태로 세 번 반환한다.
• integer로 변환할 때 PostgreSQL은 값을 정수로 반올림한다.
• 세 번째 예제의 경우 잘못된 타입 변환을 수행하려고 시도하여 오류가 발생한다.

SELECT timestamp_column, CAST(timestamp_column AS varchar(10))
FROM date_time_types;

SELECT timestamp_column::varchar(10)
FROM date_time_types;

• PostgreSQL은 CAST()보다 공간을 덜 차지하고 더욱 명확한 단축 표기법을 제공한다.
• 이중 콜론 ::을 사용하면 된다.
• 편한 방식을 사용하되, 한 가지 주의할 점은 이중 콜론 방식은 PostgreSQL 전용이므로 다른 SQL 버전으로는 포트가 불가능하다.