[PostgreSQL] 데이터타입에 알맞은 테이블 만들기

데이터의 성격에 알맞은 데이터 타입을 선택함으로써 PostgreSQL이 더 빠르고, 정확하게 우리가 원하는 역할을 할 수 있게 되기 때문에 데이터 타입을 아는 것은 중요합니다. 프로그래밍 언어는 대부분 비슷한 데이터 타입을 갖고 있습니다. PostgreSQL도 마찬가지로 비슷한 데이터 타입을 가지고 있습니다.

체계적인 데이터베이스 구축을 위해 데이터베이스의 ‘무결성’에 대해 알아야 합니다.

데이터 타입이란?

데이터 타입

데이터 타입을 선언함으로써 테이블의 각 컬럼 속에 있는 데이터의 성질을 정의할 수 있습니다. 변수명 앞에 데이터 타입을 선언하면서 변수의 성질을 정의하는 것과 유사합니다.

숫자형(Numeric)

숫자형은 테이블의 컬럼속 데이터들을 숫자의 형태로 저장하는 것입니다. 숫자형에는 여러 가지 데이터 타입이 존재하는데 INTEGER, SMALLINT, BIGINT, NUMERIC 등이 있습니다. 이러한 데이터 타입을 바탕으로 사용할 때, 대문자로 주로 표기합니다. 일반적으로 변수명은 PostgreSQL에서 소문자로 관리하기 때문에 암묵적으로 소문자를 사용합니다. 이에 자료형과 뒤에 배울 제약조건과 같은 속성들은 변수명과 구분되고 명확하게 구분하기 위해 일반적으로 대문자를 씁니다.

데이터를 저장할 때 소수점 자리를 제외한 정수만을 입력하기를 원하면 INTEGER 데이터 타입을 사용합니다. 이때 INTEGER 대신 축약한 INT로 작성해도 됩니다. 또 INTEGER는 나타낼 수 있는 수의 범위와 저장공간 사이의 밸런스가 적절합니다. INTEGER는 -2147483648 ~ 2147483647 범위 내의 숫자 정보를 저장할 수 있으며, 이 범위를 넘는 숫자 정보는 저장할 수 없습니다.

소수점도 정확하게 입력받아야 하는 경우 NUMERIC 데이터 타입을 사용합니다. 이 데이터 타입을 사용할 때는 NUMERIC(p, q) 형태로 작성하고 p에는 전체 자릿수, q에는 소수점 자릿수를 입력합니다. 참고로 q를 뺀 NUMERIC(p)은 p자리 정수를 나타냅니다. 일반적으로 입력받는 숫자의 길이를 제한해야 할 때 NUMERIC(p) 형태를 사용합니다.

필요한 때에 따라 부동소수점을 활용한 연산을 할 때는 FLOAT 데이터 타입을 사용할 수 있습니다. FLOAT 데이터 타입은 표준 SQL이고, PostgreSQL에서는 REAL 데이터 타입과 DOUBLE PRECISION 데이터 타입으로 인식합니다. 정확한 실수 계산을 위해 NUMERIC 데이터 타입을 사용하는 것을 권장합니다.

마지막으로 SERIAL 데이터 타입이 있습니다. INTEGER를 기본 값으로 1씩 추가되어 값이 자동 생성되며 테이블의 ‘프라이머리 키 컬럼’의 데이터 타입으로 주로 쓰입니다.

데이터 타이설명저장용량

INTEGER	나타낼 수 있는 수의 범위와 저장용량 사이의 밸런스가 적절. 일반적으로 많이씀. 숫자 길이 제한 불가.	4 bytes
NUMERIC(p, q)	소수점자리 표시 기능. DECIMAL과 같음.	가변적
FLOAT	부동소수점을 사용. REAL 또는 DOUBLE PRECISION으로 인식.	4 bytes, 8 bytes
SERIAL	INTEGER 기본 값으로 1씩 추가되며 값이 자종 생성, 프라이머리 키 데이터 타입으로 주로 사용	4 bytes

화폐형(Monetary)

화폐형(MONEY)은 말 그대로 금액을 저장하는 데이터 타입이고, 분수의 형태로 금액을 저장합니다. 분수의 정밀도는 lc_monetary 설정을 따릅니다. 다만, 테이블에서는 소수점 두 자릿수까지 표현됩니다.

데이터를 입력할 때, 정수 및 부동 소수점과 일반적인 화폐 형식을 포함하여 다양한 형식의 입력이 가능합니다. 출력은 로캘 설정에 따라 다릅니다.

문자형(Character)

문자를 입력받으려면 데이터 타입 자리에 문자형을 입력하면 됩니다. 문자형에는 VARCHAR(n), CHAR(n), TEXT 세 가지가 있습니다. 여기서 VARCHAR(n)와 CHAR(n)에 들어가는 n은 양의 정수이며, 데이터의 크기가 아닌 문자열의 길이입니다.

VARCHAR(n)는 character varying의 약자로 문자열의 길이가 가변적입니다. 즉 VARCHAR(20)으로 최대 길이를 20으로 제한한다면, 20자 이내의 길이를 가진 실제 문자열을 저장합니다. VARCHAR(n)에 n을 지정하지 않았다면, 임의의 길이의 모든 문자열을 허용합니다.

VARCHAR(n), CHAR(n)에 공백이 아닌, 길이 n을 초과하는 문자열을 입력하면 오류가 발생하며, 이 경우 문자열이 최대길이 n개의 문자열로 잘리게 됩니다. 보통 주민등록번호나 전화번호같이 길이가 일정한 데이터의 데이터 타입을 CHAR로 쓰고 그외의 경우에는 VARCHAR를 주로 사용합니다.

PostgreSQL에서는 길이에 상관없이 문자열을 지정하는 TEXT 문자형을 지원합니다. TEXT는 SQL 표준이 아니지만 다른 여러 SQL 데이터베이스 관리 시스템에 존재합니다. 게시판 같이 긴 문자열의 형태를 저장해야한다면, (n)으로 길이를 제한하지 않는 VARCHAR와 TEXT를 씁니다.

데이터 타입실행

VARCHAR(n)	n 이하 문자의 길이 그대로 저장
CHAR(n)	“문자 길이 + 공백” 형태로 n에 맞추어 저장
TEXT	길이에 상관없이 모든 문자열을 저장(=n을 쓰지 않은 VARCHAR)

날짜 및 시간(Date & Time)

TIMESTAMP는 날짜와 시간 정보 모두를 나타낸다. 시간은 세계 표준시(UTC)가 있고, 현재 우리가 보는 시간대가 합쳐진 시간이 있다. 그래서 TIMESTAMP는 시간대 정보를 반영하지 않은 TIMESTAMP WITHOUT TMEZONE(TIMESTAMP)와 시간대 정보를 반영한 TIMESTAMP WITH TIME ZONE(TIMESTAMPTZ)의 데이터 타입을 사용합니다.

DATE는 문자 그대로 날짜 정보만 저장하는 자료형입니다.

TIME은 시간 정보만 저장하는 자료형이고, TIME 또한 시간대 정보를 반영하지 않은 TIME WITHOUT TIME ZONE과 시간대 정보를 반영한 TIME WITH TIME ZONE 두 가지로 나뉩니다.

TIMESTAMP와 TIME은 (p)를 붙여 ‘초' 단위의 소수점 값을 정확하게 표현할 수 있습니다. 기본적으로 정밀도에는 명시적인 한계가 없지만, 허용되는 p의 범위는 0에서 6입니다.

데이터 타입설명저장용량

TIMESTAMP	현재 세계 표준시(UTC) (시간대 정보 반영x)	8 bytes
TIMESTAMPTZ	세계 표준시 + 시간대 정보 반영 (한국은 GMT + 9)	8 bytes
DATE	날짜 정보만 표시	4 bytes
TIME	시간 정보만 표시, 세계표준시(시간대 정보 반영x)	8 bytes
TIME WITH TIME ZOME	시간 정보만 표시, 세계표준시 + 시간대 정보 반영	12 bytes

불리언형(Boolean)

불리언 데이터 타입은 일반적으로 참(True)과 거짓(False)을 나타냅니다.

• 불리언 타입은 다음을 모두 True로 인식합니다.
  True, yes, on, 1
• 또한 다음을 모두 False로 인식합니다.
  False, no, off, 0

불리언 데이터 타입은 출력의 결과로 True일 경우 t, False일 경우 f를 내보냅니다. Null은 True도 아니고 False도 아닌 데이터 값이며, 일부 불확실성을 나타내는 값입니다.

데이터 타이설명

TRUE	True, yes, on, 1 모두 참
FALSE	False, no, off, 0 모두 거짓
Null	알 수 없는 정보 또는 일부 불확실

보통 두 가지 선택지를 가질 때 불리언 데이터 타입을 선택합니다.

배열형(Array)

배열은 여러 데이터를 하나의 결합으로 관리하기 위한 데이터 타입입니다. 보통 배열이 아닌 데이터 타입의 컬럼에는 하나의 데이터 값만 올 수 있습니다. 하지만 배열은 하나 이상의 여러 데이터를 저장할 수 있습니다.

제이슨형(JSON)

JSON은 자바스크립트 객체 표기법(JavaScript Object Notation)의 약자이며 주로 서버와 웹 애플리케이션 간에 데이터를 주고받을 때 사용합니다. JSON은 키-밸류의 쌍으로 구성된 JSON 오브젝트와 배열과 비슷한 구조를 갖는 JSON 배열로 나뉩니다. JSON 오브젝트의 밸류에는 숫자, 문자열, JSON 배열, JSON 등이 올 수 있습니다.

JSON은 동일한 값의 집합을 받아들이는 두 가지의 데이터 타입을 갖는데, 입력 텍스트를 정확한 사본을 만들어서 저장하고 이것을 불러와 처리할 때는 데이터를 재분석한 다음 실행하는 JSON 타입과 텍스트를 이진(binary) 형태로 분해 후 저장해서 입력이 느리지만, 출력 시에는 재분석을 하지 않기 때문에 처리 속도가 빠른 JSONB 타입이 있습니다.

장점단점

JSON	입력한 텍스트의 정확한 사본 생성	처리 속도가 느림
JSONB	처리 속도가 비교적 빠름	데이터 저장 속도가 비교적 느림

데이터 타입 변경하기

CAST 연산자

CAST 연산자는 형변환을 할 수 있는 가장 기본적인 방법입니다.

CAST (표현식 AS 바꿀 데이터 타입)

CAST 형 연산자

CAST 연산자를 사용하지 않고 더 간단하게 데이터 타입을 바꿀 수 있는 방법입니다.

datatype_example=# SELECT
'값::바꿀 데이터 타입';

데이터의 값 제한하기

무결성이란 무엇일까?

무결성은 데이터베이스 내에 정확하고 유효한 데이터만을 유지시키는 속성입니다. 불필요한 데이터는 최대한 제거하고, 합칠 수 있는 데이터는 최대한 합하는 것입니다. 또한 우리가 생성, 조회, 수정, 삭제할 때 데이터 값을 일관되고 정확하게 유지하는 것입니다.

무결성을 유지하면서 얻게 되는 이점은 데이터베이스를 설계할 때 응용 프로그램 단계에서 일관성을 확인하는 작업을 할 필요가 없어진다는 것입니다. 응용 프로그램 단계에서 일관성을 검증하는 것보다 데이터베이스에서 무결성을 유지하는 것이 오류의 가능성을 줄여줍니다.

무결성의 제약조건

개체 무결성

개체 무결성은 모든 테이블이 프라이머리 키를 가져야 하며 프라이머리 키로 선택된 컬럼은 고유하고 NULL 값을 허용하지 않아야 한다는 속성입니다.

개개인의 고유한 식별 번호화 같이 데이터베이스에서도 개체를 식별하기 위한 정보가 필요합니다 .

이런 정보를 DBMS에서는 프라이머리 키로 지정하여 특별대우 하고 있습니다. 프라이머리 키는 데이터를 구분하는 고유한 정보이기 때문에 이 값은 비어있으면 안됩니다. 그 의미가 바로 null 값을 허용하지 않는 다는 것입니다. 다시 말해, 우리는 데이터베이스에 고유한 프라이머리 키를 부여함으로 데이터를 효과적으로 관리할 수 있는 것입니다.

참조 무결성

참조 무결성은 외래 키 값이 빈 값이거나 참조된 테이블의 기본 키 값과 동일해야 합니다.

논리적으로 데이터를 모델링 했을 때, 두 테이블간의 관계를 유효하게 하는 연결고리(외래 키)가 있을 때 두 테이블은 참조 관계를 갖는다고 합니다.

범위 무결성

범위 무결성은 사용자가 정의한 도메인 내에서 관계형 데이터베이스의 모든 열을 정의하도록 규정합니다.

도메인 형은 기본 데이터 타입을 기반으로 선택적으로 제약조건을 걸수 있는 사용자 정의 데이터 타입입니다. CREATE DOMAIN이라는 명령어로 도메인 데이터 타입을 정의할 수 있습니다.

컬럼 값 제한하기

NOT NULL

NOT NULL 제약조건은 빈 값을 허용하지 않는 조건입니다. 이 조건이 있으면 빈 값이 입력되면 오류가 발생합니다. 필수로 입력해야 하는 정보들은 NOT NULL 제약조건을 넣습니다. 반대로 빈 값을 허용할 때는 NULL을 쓰거나 아무것도 쓰지 않아도 됩니다.

UNIQUE

UNIQUE 제약조건은 말 그대로 유일한 값을 가져야 합니다. 따라서 UNIQUE 제약조건에 해당하는 컬럼 값은 테이블 내에서 유일한 값을 가져야 합니다.

또한, UNIQUE 제약조건이 여러 개인 경우에는 새로운 줄에 적는 형식으로 선언할 수 있습니다. 모든 컬럼들을 선언한 후에 유니크 제약조건을 설정하는 것입니다.

프라이머리 키

프라이머리 키는 주 식별자, 주 키, 또는 줄여서 PK라고도 말합니다. 데이터베이스를 설계할 때에는 보통 PK라고 씁니다. 프라이머리 키의 컬럼 값은 서로 달라야하며, 빈 값을 허용하지 않아야 합니다. 즉, UNIQUE해야 하며, NOT NULL해야 합니다.

외래 키

자식이 부모의 행동을 참조하듯이, 잠조 관계에서 참조되는 테이블은 먼저 생성되어 있어야 하며, 해당 테이블을 부모 테이블이라고 부릅니다.

1. 부모 테이블이 자식 테이블보다 먼저 생성되어야 합니다.
2. 부모 테이블은 자식 테이블과 같은 데이터 타입을 가져야 합니다.
3. 부모 테이블에서 탐조된 컬럼의 값만 자식 테이블에서 입력 가능합니다.
4. 참조되는 컬럼은 모두 프라이머리 키이거나 UNIQUE 제약조건 형식이어야 합니다.

기본적으로 부모 테이블은 자식 테이블보다 먼저 삭제 또는 수정할 수 없습니다. 테이블을 지우는 상황을 ON DELETE라고 하며, 테이블을 수정하는 상황을 ON UPDATE라고 합니다.

1. ON DELETE NO ACTION - 지우면 안되는 경우
2. ON DELETE RESTRICT - 지우면 안되는 경우
3. ON DELTE SET NULL - 지우면 안되는 경우
4. ON DELTE CASCADE - 지워야 하는 경우
5. ON DELTE SET DEFAULT - 지워야 하는 경우

CHECK

CHECK 제약 조건은 가장 일반적인 제약 조건으로, CHECK 뒤에 나오는 식이 불리언 타입의 True를 만족해야 합니다.

Alter Table

만들어진 테이블에 컬럼 추가하기

ALTER TABLE 테이블 이름
ADD COLUMN 컬럼이름 데이터 타입 제약조건;

NOT NULL 제약 조건을 추가하고 싶다면

1. NOT NULL 제약조건 없이 컬럼을 생성한다.
2. 컬럼 값을 수정한다.
3. NOT NULL 제약조건을 추가한다.

만들어진 테이블에 컬럼 삭제하기

AFTER TABLE 테이블이름
DROP COLUMN 컬럼이름;

다른 컬럼이 지우려고 하는 컬럼을 참조한다면 CASCADE 속성을 활용할 수 있다.

ALTER TABLE 테이블이름
DROP COLUMN 컬럼이름 CASCADE;

만들어진 테이블에 컬럼명 바꾸기

ALTER TABLE 테이블이름
RENAME 기존컬럼이름 TO 새컬럼이름;

ALTER 테이블이름2
RENAME COLUMN 기존컬럼이름 TO 새컬럼이름;

주의해야 할 점은 존재하지 않는 컬럼의 이름은 수정할 수 없으며, 한번에 한 컬럼의 이름만 수정이 가능하다는 것입니다. 또한, 이름이 바뀌는 컬럼을 참조하는 다른 컬럼이나 객체가 있으면 PostgreSQL에서는 자동으로 이름이 변경됩니다.

만들어진 테이블에 제약조건 추가하기

NOT NULL 제약조건 추가 및 제거

AFTER TABLE 테이블이름
AFTER COLUMN 컬럼이름 DROP NOT NULL;

프라이머리 키 및 다른 제약조건 추가 및 제거

AFTER TABLE 테이블이름
ADD PRIMARY KEY (컬럼이름);

외래 키 제약조건은 프라이머리 키와 비슷한 형태이다

AFTER TABLE 테이블이름
ADD FOREIGN KEY (컬럼이름1) REFERENCES 부모테이블 (컬럼이름2);

만들어진 테이블에 데이터 타입 변경하기

ALTER TABLE 테이블이름
ALTER COLUMN 컬럼이름 TYPE 새로운 데이터 타입,
ALTER COLUMN 컬럼이름 SET DATA TYPE 새로운 데이터 타입;

SET DATA 키워드는 생략해도 무관합니다.

데이터 값을 형변환 하는 동시에 컬럼의 데이터 타입을 바꾸려면 USING 절을 사용합니다. USING 절은 CAST형 연산자를 사용하여 다음과 같이 사용합니다.

USING 컬럼이름::새로운데이터 타입