[PostgreSQL실용]_4_데이터 타입(문자형, 숫자형, 날짜형, JSON, 그 외 그리고 CAST AS 데이터 타입 변환)

Hyejin Beck·2024년 1월 9일

데이터베이스(SQL)

목록 보기

13/40

가장 많이 접하는 데이터 타입은 보통 3가지라고 합니다.

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

테이블의 데이터타입 지정은 CREATE TABLE 을 통해서 합니다.

CREATE TABLE 테이블명 (
    컬럼명 타입, 
    컬럼명 타입,
    ... 
);

문자형

~~char(n) : 길이가 n 까지 입력 가능~~

~~n보다 작게 입력 되었을 경우, 공백으로 채워집니다.~~

사실 char()은 거의 쓰이지 않는다고 합니다.

varchar(n) : 최대길이가 n까지 입력 가능

n보다 작게 입력 되었을 경우 공백 추가하지 않습니다. (char보다 공간절약)

character varying(n) 으로도 정의할수 있다고 합니다.

~~text : 길이 제한이 없음~~

문자 저장할때는 text를 추천하는 이유

최대 길이 지정 X

추후 문자 타입 열에 대한 요구사항이 변경되더라도 테이블 수정 X

~~1GB까지 문자열을 담을수 있다고 합니다.~~

표준SQL은 아니지만, MySQL과 MicrosoftSQL Server에서 사용 가능

위 세 가지 타입은 성능상 엄청난 차이는 굳이 없다고 합니다.
유저나 사용하고있는 프로그램에 따라 각자 추천하는 문자형 데이터 타입이 있다고 합니다.

숫자형

문자형과는 달리, 사칙연산이나 다른 수학 연산 수행 가능합니다.

정수 integers : 양수와 음수를 포함한 정수
고정 소수점 fixed-point 과 부동 소수점 floating-point : 실수를 표현

정수 int

smallint : 2 바이트

양/음수로 약 3만 사이

보통 한 주, 한 달 짜리처럼 데이터값이 적게 한정되어 있을 경우

integer : 4 바이트

양/음수로 양/음수로 4만~20억 사이

bigint : 8 바이트

양/음수 21억이 넘어가는 경우

데이터 타입의 범위를 벗어난 숫자를 열에 가할 경우, 데이터베이스는 실행을 중지하고 out of range 오류를 반환합니다.

자동증가 serial

테이블에 행을 추가할 때, 자동 증가가 되는 데이터타입의 열에는 serial 타입과 indentity 타입이 있다고 합니다.
단, serial 타입은 실수로 열에 값을 삽입할경우 방지하지 않는데 indentity타입은 방지해준다고 합니다.

smallserial : 2 바이트
- 범위 : 1 ~ 약 3만
serial : 4 바이트
- 범위 : 1 ~ 약 21억 이내
bigserial : 8 바이트
- 범위 : 1 ~ 약 약 900경 (922,3372,0368,5477,5807까지)

CREATE TABLE 테이블명 (
    컬럼명 serial, 
    컬럼명 varchar(100) 
);

그런데 궁금하군요! serial 컬럼에 값을 삽입할경우! 어떻게 되는지요

-- 정상적 데이터 입력 
INSERT INTO teachers (first_name, last_name, school, hire_date, salary )
VALUES ('Hyejin','Beck','Harvard University','2024-03-01','5200')

-- 잘못된 데이터 입력 
INSERT INTO teachers 
VALUES (10,'Jane','Doe','London Collage', '2000-01-01','100')

오 그래도 나름 정상적?으로 입력이 되는군요! id를 내가 부여해주고 싶을때 사용할수 있나 봅니다.

INSERT INTO teachers (id, first_name, last_name, school, hire_date, salary)
VALUES (10,'Jane','Doe','London Collage', '2000-01-01','100')

INSERT INTO teachers (id, first_name, last_name, school, hire_date, salary)
VALUES (4,'Jane','Doe','London Collage', '2000-01-01','100')

id를 일부러 고유한 값으로 자동증가 시키기위해 serial로 했는데,
굳이 value값을 넣는다면 중복되거나 순서가 틀어질수도 있을 것 같습니다!

자동증가 identity

serial 타입은 중복된 Value값을 입력해줄 경우 방지하지 않고 그대로 출력되었는데, identity는 실수로 해당 열에 값을 삽입하지 않도록 방지해준다고 합니다!

Serial처럼 그냥 컬럼뒤에 바로 IDENTITY를 쓰는게 아닌
컬럼명 integer GENERATED ALWAYS AS IDENTITY를 써야 한다고 합니다.

CREATE TABLE teachers_test_identity (
    id integer GENERATED ALWAYS AS IDENTITY, 
    first_name varchar(50) , 
	last_name varchar(50) 
);

일단 확인

여기에 데이터값들을 추가해보겠습니다.

오~ Serial 으로 지정해주는것과는 달리 identity로 지정해주는 컬럼에 대해 값을 넣어줄 경우 ERROR로 출력해줍니다!

자동 알파벳 증가 가능?

꼭 integer(1->2->3 처럼 자동 정수 증가) 를 써야할까요?
varchar나 text로 (a->b->c 처럼 자동 알파벳 증가)도 가능할까요? 그냥 궁금해졌습니다!

GENERATED ALWAYS AS IDENTITY 앞에 integer 외에는 문법 오류가 납니다!

소숫점 숫자

fixed 고정소수점

numeric(전체 자리수, 소수점 아래 자리수)

numeric(5,2)

12.05

5.2

143.87

982.78

decimal(전체 자리수, 소수점 아래 자리수)

참고로 소수점 아래 자리수가 누락될 경우 자동으로 0 이 되어, 소수점이 없는 정수가 됩니다.
전체 자리수와 소수점 아래 자리수 둘 다 누락될 경우 데이터가 가능한 최대한의 한도를 기준으로 숫자를 저장합니다. (PostgreSQL의 경우 소숫점 위 131,072자리와 소수점 아래 16,383자리까지 저장)

float 부동소수점

real

담을 수 있는 데이터 크기 : 소수점 이하 6자리까지

4 바이트

double precision

담을 수 있는 데이터 크기 : 소수점 이하 15자리까지

8 바이트

고정소수점과 달리 부동소수점은 소숫점의 위치를 숫자에 따라 움직일 수 있다고 합니다.

더 정확한 고정소수점 fixed

정밀한 계산을 위해서는 고정소수점을 많이 이용한다고 합니다.

참고로 네이버 계산기 돌려봤을때 0.7 곱하기 1000만은 = 700만 입니다.

날짜형

timestamp :

8바이트

날짜와 시간

범위 : 4713 BC ~ 294276 AC

시간대 TIME ZONE 을 추가 하는 것이 좋습니다.

timestamp with time zone 으로 컬럼 형태 지정

'2023-01-01 01:00 KST'

'2023-01-01 01:00 +9'

'2023-01-01 01:00 Asia/Seoul'

date

4바이트

날짜 (시간X)

범위 : 4713 BC ~ 5874897 AC

time

8바이트

시간 (날짜X)

00:00:00 ~ 24:00:00

interval

16바이트

시간 차이

+/- 178,000,000년

1 day , 1 days

1 week를 7 day로 계산, 2 weeks 를 14 days로 계산,

1 month , 2 mons

timestamp와 interval

timestamp(+time zone) 타입 컬럼과 interval 타입컬럼의 테이블을 만들어보겠습니다.

현재 우리나라에서 사용되고 있는 한국 표준시는 KST 입니다. "Korea Standard Time" 즉 "대한민국 표준시"라는 뜻입니다.
KST의 타임존은 UTC/GMT +9 입니다. 즉 그리니치 천문대가 있는 곳의 시간인 세계협정시(세계표준시)에서 9시간을 더하면 한국시간이 되는 것입니다.

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

INSERT INTO date_test
VALUES 
	  ('2023-12-31 01:00 EST', '2 days'),
	  ('2023-12-31 01:00 KST', '2 days'),
	  ('2023-12-31 01:00 +9', '4 weeks'),
	  ('2023-12-31 01:00 -8', '4 weeks'),
	  ('2023-12-31 01:00 Australia/Melbourne', '1 month'),
	  ('2023-12-31 01:00 Asia/Seoul', '1 month'),
	  (now(), '6 months'), 
	  (now(), '1 century')

SELECT * FROM date_test

interval 날짜 계산

interval로 입력한 날짜값 (1 day, 2 weeks 등) 으로 날짜를 계산할 수 있습니다.

정해진 날짜 +/- interval 날짜

int + int = int
date + interval = date

interval을 통해 date 계산도 할 수 있는게 신기합니다!

JSON

JavaScript Object Notation 의 약자로서
데이터를 저장하고 교환하는데 사용하는 구조화된 데이터 형식입니다.
모든 컴퓨터 시스템에서 사용할 수 있습니다.
모든 주요 프로그래밍 언어는 JSON 형식의 데이터를 읽고 쓸 수 있습니다.

{"키 : 값", "키 : 값" .. } 의 형태의 값과 값 목록의 컬렉션으로 정보를 구성합니다.
{
  "name" : "hyejinbeck", 
  "age" : 30, 
  "location" : {
    "country" : "South Korea", 
    "city" : "Seoul", 
  }
}

json 그대로 저장
jsonb 바이너리 형식으로 변환하여 저장
- 인덱싱 지원 , 처리 속도가 빠름

그 외 타입

Boolean

True or False
참이냐 거짓이냐를 반환해줍니다.

Geometric

점, 선, 원 및 기타 2차원 개체 포함

Text Search

PostgreSQL 전문 검색 엔진용 텍스트 검색

Network Address

IP 또는 MAC 주소와 같은 네트워크 주소 타입

UUID

Universally Unique Identifier 타입
경우에 따라 테이블의 고유 키로 사용

Range

정수 혹은 타임스템프 같은 값의 범위를 지정하는 범위 타입

바이너리 데이터 저장하는 타입

XML 및 JSON 타입

구조화된 형식으로 정보 저장

CAST AS 데이터 타입 변환

정수 -> 문자 가능
문자나 텍스트 -> 숫자 불가능

::은 PostgreSQL만

cast(컬럼명 as 바꿀 타입) 을 해도 되고,
컬럼명 :: 바꿀 타입 으로 해도 된다고 합니다.

두 개 다 똑같다고 합니다.

다만, PostgreSQL 에서만 사용 가능합니다!
다른 SQL 버전에서는 비슷한 방식이 없어 포트 할 수 없습니다.

연습문제

과일과 야채를 지역 식료품점에 배달하는 회사라고 가정해보자.
매일 운전자들이 운전하는 마일리지의 10분의 1킬로미터를 추적해야 한다.
어떤 운전자도 하루에 999킬로미터 이상을 주행하지 않는다고 할 때,
테이블의 마일리지 열에 적합한 데이터 타입은 무엇이며 그 이유는?

마일리지 컬럼
= 하루에 운전하는 길이(km)의 0.1
= 하루 주행거리(km) * 0.1

아무래도 숫자 * 숫자로 이루어진 컬럼이기 때문에
1. 숫자형 타입
거기에 소숫점인 실수형태로 자세히 나오게 하려면 고정 소수점 fixed와 부동 소수점 float 타입이 있는데, 곱셈 등 정확하게 나오려면 고정 소수점 형태가 좋다.
2. numeric 타입

즉, 곱셈에 대해서 정확한 수치를 표현할 수 있는 numeric 데이터 타입이 좋습니다.

회사의 각 운전자를 나열한 테이블에서,
운전자의 성과 이름에 적합한 데이터 타입은 무엇인가요?
성과 이름이 함께 적힌 더 큰 이름의 열을 하나 가지는 것보다
성과 이름을 두 개의 열로 구분하는 것이 좋은 이유는 무엇인가요?

성과 이름은 문자형
문자형에는 char, varchar, text 세 형태가 있다.
성과 이름은 각자 가지고 있는 길이가 다르니,
~~길이가 고정된 char~~ 제외
~~표준SQL언어가 아니라서 PostgreSQL에는 사용이 불가한 text~~ 제외하면,
varchar(n) 타입으로 저장하는 것이 좋다.

성과 이름을 합해서 하나로 하게 되면 각자 성,이름의 길이가 달라 가지각색이 된다.
또한 추출하거나 검색하는데 번거로워집니다.
hyejin_beck, miranda_beckham 보다는
hyjin, beck, miranda, beckham 형태가 각각 찾기 수월합니다.

날짜 형식의 문자열을 포함하는 텍스트 열이 있다고 가정해보자.
문자열 하나는 '4//2021'로 표기되어 있다.
해당 문자열을 timestamp 데이터 타입으로 변환하려고 하면 어떻게 되나요?

CREATE TABLE teachers_test (
	id bigserial , 
	first_name varchar(25), 
	hire_date date, 
	salary numeric 
); 

INSERT INTO teachers_test (first_name,  hire_date, salary)
VALUES 
	('4//2021', '2021-04-01', '20210404')
    -- date타입에서는 4//2021이나, 04-01-2021 등으로는 값이 넣어지지 않는다.

문자열 > 날짜형은 어려울 것 같다.
그것도 제대로된 형식이 아닌 문자형이니 더더욱 안 될 것 같다.

varchar형식의 컬럼을 date형식으로 바꾸니, 코드에러

Hyejin Beck

데이터기반 스토리텔링을 통해 인사이트를 얻습니다.

이전 포스트

[PostgreSQL실용]_3_SELECT, DISTINCT, FROM, WHERE ,연산자, ORDER BY 사용

다음 포스트