자료형 - 분류 내용
| 숫자 | smallint | 작은 범위의 정수, 2 bytes(-32768 ~ +32767) |
| integer | 정수에 대한 일반적인 선택, 4 bytes(-2147483648 ~ +2147483647) | |
| bigint | 큰 범위의 정수, 8 bytes(-9223372036854775808 ~ 9223372036854775807) | |
| decimal | 사용자 지정 정밀도, variable, 정확(제한 없음) | |
| numeric | 사용자 지정 정밀도, variable, 정확(제한 없음) | |
| real | 가변 정밀도, 부정확, 4 bytes(6자리 소수점 정밀도) | |
| double precision | 가변 정밀도, 부정확, 8 bytes(15자리 소수점 정밀도) | |
| serial | 자동 증가 정수, 4 bytes(1 ~ 2147483647) | |
| bigserial | 큰 자동 증가 정수, 8 bytes(1 ~ 9223372036854775807 | |
| 화폐 | money | 통화 금액, 8 bytes(-92233720368547758.08 ~ +92233720368547758.07) |
| 문자 | character varying(n), varchar(n) | 제한이있는 가변 길이 |
| character(n), char(n) | 고정 길이, 공백 채우기 | |
| text | 가변 길이 무제한 | |
| "char" | 1byte 내부 유형, 1 byte | |
| name | 개체 이름의 내부 유형, 64 bytes | |
| 이진 데이터 | bytea | 가변 길이 이진 문자열, 1 또는 4 byte와 실제 2진 문자열 |
| 날짜/시간 | timestamp [(p)][without time zone] | 날짜와 시간 모두(시간대 없음), 8 bytes |
| timestamp [(p)] with time zone | 시간대가 있는 날짜와 시간 모두, 8 bytes | |
| date | 날짜(시간 없음), 4 bytes | |
| time [(p)][without time zone] | 시간(날짜 없음), 8 bytes | |
| time [(p)] with time zone | 시간대만 포함, 12 bytes | |
| interval [fields][(p)] | 시간 간격, 16 bytes | |
| 부울 | boolean | 참 또는 거짓 상태, 1 byte |
| 기하학 | point | 비행기 위의 포인트, 16 bytes, (x,y) |
| line | 무한 라인(완전히 구현되지 않음), 32 bytes, ((x1,y1),(x2,y2)) | |
| lseg | 유한 선분, 32 bytes, ((x1,y1),(x2,y2)) | |
| box | 직사각형 상자, 32 bytes, ((x1,y1),(x2,y2)) | |
| path | 닫힌 경로(폴리곤과 유사), 16+16n bytes, ((x1,y1),...) | |
| path | 열린 경로, 16+16n bytes, [(x1,y1),...] | |
| polygon | 다각형(닫힌 경로와 유사), 40+16n bytes, ((x1,y1),...) | |
| circle | 원, 24 bytes, <(x,y),r> (중심점 및 반경) | |
| 네트워크 주소 | cidr | IPv4 및 IPv6 네트워크, 7 또는 19 bytes |
| inet | IPv4 및 IPv6 호스트 및 네트워크, 7 또는 19 bytes | |
| macaddr | MAC 주소, 6 bytes | |
| 비트 문자열 | bit(n) | 길이 n과 정확히 일치 |
| bit varying(n) | 최대 길이 n까지 가변 길이 | |
| 텍스트 검색 | tsvector | 텍스트 검색에 최적화된 형식의 문서 |
| tsquery | 유사한 텍스트 쿼리 | |
| 개체 식별자 | oid | 숫자 객체 식별자 (예: 564182) |
| regproc | 함수 명 (예: sum) | |
| regprocedure | 인수 유형이 있는 함수 (예: sum(int4)) | |
| regoper | 연산자 이름 (예: +) | |
| regoperator | 인수 유형이 있는 연산자 (예: *(integer,integer) 또는 -(NONE,integer)) | |
| regclass | 관계 이름 (예: pg_type) | |
| regtype | 데이터 유형 이름 (예: integer) | |
| regconfig | 텍스트 검색 구성 (예: english) | |
| regdictionary | 텍스트 검색 사전 (예: simple) | |
| Pseudo | any | 함수가 모든 입력 데이터 유형을 허용 |
| anyarray | 함수가 모든 배열 데이터 유형을 허용 | |
| anyelement | 함수가 모든 데이터 유형을 허용 | |
| anyenum | 함수가 모든 열거형 데이터 유형을 허용 | |
| anynonarray | 함수가 배열이 아닌 데이터 유형을 허용 | |
| cstring | 함수가 null로 끝나는 C 문자열을 받거나 반환 | |
| internal | 함수가 서버 내부 데이터 유형을 수락하거나 반환 | |
| language_handler | 절차 언어 호출 핸들러가 language_handler를 반환하도록 선언 | |
| record | 지정되지 않은 행 유형을 반환하는 함수를 식별 | |
| trigger | 트리거를 반환하기 위해 트리거 함수가 선언 | |
| void | 함수가 값을 반환하지 않음 | |
| opaque | 이전에 위의 모든 목적을 수행했던 오래된 유형 이름 | |
| 열거 | 열거형 값은 디스크에서 4 bytes를 차지 | |
| 열거형 값의 텍스트 레이블 길이는 최대 63 bytes | ||
| 열거형 레이블은 대소문자를 구분 | ||
| 레이블의 공백도 중요 | ||
| UUID | 하이픈으로 구분, 8자리 그룹, 4자리 그룹 3개, 12자리 그룹 순으로 소문자 16진수 시퀀스(총 32자리) | |
| 128 bits | ||
| XML | xml 데이터 유형을 사용하여 XML 데이터를 저장 | |
| 배열 | 테이블의 열을 가변 길이 다차원 배열로 정의 | |
| 내장 또는 사용자 정의 기본 유형, 열거 유형 또는 복합 유형의 배열을 작성 | ||
| 특정 배열 요소를 검색하는 것은 데이터베이스 설계 오류의 징후일 가능성 (배열 요소가 될 각 항목에 대해 행이 있는 별도의 테이블을 사용하는 것 권장) | ||
| 복합 | 행 또는 레코드의 구조(필드 이름과 데이터 유형의 목록) |
n = 양의정수 / p = 초 필드에 보유되는 소수 자릿수를 지정하는 정밀도 값 (범위: 0 ~ 6) / 비트 문자열: 0과 1의 문자열
테이블 생성시 컬럼의 제약 조건
| 제약조건명 | 설명 |
| NOT NULL | 해당 제약 조건이 있는 컬럼은 NULL이 저장될 수 없습니다. |
| UNIQUE | 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 합니다.(중복X) |
| PRIMARY KEY(기본키) | 해당 제약 조건이 있는 컬럼의 값은 테이블내에서 유일해야 하고 반드시 NOT NULL 이어야 합니다. |
| CHECK | 해당 제약 조건이 있는 컬럼은 지정한 조건에 맞는 값만 입력 가능 |
| REFERENCES | 해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 합니다. |
| FOREIGN KEY(외래키) |
‘참조하는 테이블에서 존재하는 값만 사용 가능'을 의미
|
| Serial | 자동증가 4bytes 정수형, 보통 primary idx로 많이 사용 AUTO INCREMENT 속성이 포함되어 있다. |
Postgresql SQL 쿼리문 작성법
1.
2.
CREATE TABLE public."comment_tb"
(
comment_id text,
user_id text NOT NULL,
notice_id text NOT NULL,
comment_substance text NOT NULL,
comment_today date NOT NULL,
PRIMARY KEY (comment_id),
CONSTRAINT user_id FOREIGN KEY (user_id)
REFERENCES public.client_tb (user_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT notice_id FOREIGN KEY (notice_id)
REFERENCES public.notice_tb (notice_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);
ALTER TABLE IF EXISTS public."comment_tb"
OWNER to postgres;
(ALTER는 넣어도 되고 빼도 됨)
progresql의 constraints(제약조건)에서 action
1. RESTRICT : 참조된 데이터가 있을 경우 삭제하지 않으며 단지 무결성 검증을 트랜잭션 실행 이전에 실행한다.
2. CASCADE : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 함께 변경/삭제됩니다.
3. NO ACTION : 데이터의 변경/삭제를 진행하고 삭제/변경된 데이터의 무결성을 검사한다.
MySQL의 경우 이 검증 시점이 no action,restrict와 동일하기 때문에 두개의 용어가 같다 라는 설명이 많다.
4. SET NULL : 개체를 변경/삭제할 때 다른 개체가 변경/삭제할 개체를 참조하고 있을 경우 참조하고 있는 값은 NULL로 세팅됩니다.
DB란?
DB는 DBMS에 의해 저장된 데이터 객체 중 가장 큰 객체를 의미한다. DBMS는 사용자 프로세스와 DB 사이에서 인터페이스 역할을 수행하는 프로세스와 메모리의 집합체다.
DB(Database)에는 다양한 데이터 객체들이 존재한다. 그리고 DB는 아래의 객체들이 저장되어 있는 파일의 집합체다. (때론 파일이 아닌 Raw Device 인 경우도 있다.)
1. 시스템카달로그 (System Catalog)
DB의 기본적인 설정 값들이 저장되어 있는 시스템카다로그(테이블의 일종)에는 DB의 경로와 DB사용자 정보와 접근권한 그리고 데이터가 저장되는 테이블에 대한 속성과 인덱스에 대한 정보 등 DBMS가 데이터를 저장하고 조회하고 관리하는데 필요한 핵심적인 데이터가 저장된다.
2. 테이블(Table)
실제 데이터가 저장되는 객체가 테이블이다. 테이블은 컬럼(Column)과 행(row)로 이루어져 있는 표와 같다고 생각하면 된다. DBMS는 SQL(Standard Query Language)이라는 언어로 사용자 프로세스와 대화하며 테이블 이름과 컬럼 이름을 기준으로 DB에서 데이터를 조회하고 삽입하고 수정하고 삭제한다.
3. 인덱스(Index)
테이블에는 사용자프로세스에서 입력하는 순서대로 행(로우)가 순차적으로 증가한다. 엑셀과 같은 표문서에서 데이터를 저장하면서 행이 증가하는 것과 같다. 따라서 검색시에 특정 행을 찾기 위해서는 첫번째 행부터 표 전체를 검색해야 한다. 당연히 검색 속도가 느릴 수 밖에 없다. 이러한 검색 속도 문제를 해결하기 위해 검색 조건에 많이 사용되는 컬럼, 즉 사람이라면 주민번호, 전화번호, 이름 등의 컬럼을 내림차순, 올림차순에 따라 정리해 놓은 검색을 위한 표가 바로 인덱스다.
인덱스가 많을 수록 좋을 것 같지만 데이터가 추가(insert) 될 때 인덱스도 함께 생성하고 경우에 따라서는 정리도 해주어야 하기 때문에 인덱스의 생성은 신중해야 한다. 테이블 생성 시 Primary Key를 지정하는 경우가 많은데 이또한 인덱스의 일종이다.
4. 뷰(View)
뷰는 실제로 데이터가 저장되지 않는 논리적인 테이블이다. 두개의 테이블에 모두 존재하는 데이터를 별도의 테이블로 만들고 싶을 때 View를 생성하면 된다. View는 SQL의 select 문을 이용해 정의되어 시스템카다로그에 저장된다. 뷰도 논리적으로는 테이블이기 때문에 테이블의 이름과 뷰의 이름이 중복될 수 없다.
4. 프로시저(Procedure)
트리거 혹은 DBMS 자체에서 지원하는 4GL언어 (예를 들면 PL/SQL 혹은 T-SQL) 또는 사용자프로세스에서 호출할 수 있는 일종의 사용자 정의 함수다. DBMS 자체에서 지원하는 4GL 언어 또는 C언어 등의 언어와 SQL이 혼합되어 작성되며 시스템카달로그에 저장된다. 대부분 여러 사용자 프로세스 혹은 트리거에서 많이 사용되는 절차를 프로시저로 정의해 놓고 호출하여 사용한다. DBMS에 의해 관리되기 때문에 미리 컴파일(Pre-Compile)되어 있고 개발언어 독립적이라는 장점이 있다.
5. 트리거(Trigger)
쉽게 이해하기 위해서는 트리거를 일종의 이벤트 라고 생각하면 된다. 즉 "특정 테이블에 특정 값이 입력될 때 어떤 프로시저를 호출해라"와 같이 이벤트와 처리루틴을 정의하는 것을 말한다. 사용자 프로세스에서 모든 처리를 수행할 수 있으나 여러 사용자 프로세스에서 공통으로 처리해야 하는 프로시저 중에서 "이벤트"기반으로 처리해야할 내용들을 트리거로 지정하는 경우가 많다.
DBMS는 Database Management System이다. 우리말로 쓰자면 데이터베이스 관리 시스템이다. 예전에 DBMS 기술지원 일을 할 때는 DB와 DBMS를 엄격히 분리하여야 했지만 보안이나 시스템소프트웨어 관련 일을 하면서 궂이 분리해야할 필요를 느끼지는 못한다. DB와 DBMS가 운영체제보다도 더 전문적인 지식을 필요하다보니 특히나 보안 분야에서 일하는 분들은 이 둘을 구별해야할 필요를 전혀 느끼지 못하고 있다.
DBMS 중에서 가장 많이 쓰이는 Oracle이나 MySQL , MS-SQL 그리고 PostgreSQL은 기본적으로 RDBMS다. 여기서 R은 Relational(관계형)를 의미한다. 즉, 테이블에 저장되는 데이터들이 관계를 맺고 있고 이 관계에 따라 가공되고 표현됨을 의미한다. 이 관계형에 대한 설명은 다음을 참고하면 좋다. (커드의 12가지 규칙)
모든 DBMS는 접속관리자(Connection Manager),SQL 파서(Parser), 쿼리 옵티마이저(Query Optimizer), 트랜잭션 관리자(Transaction Manager), DB관리자(Database Manager) 등으로 불리는 프로세스들로 구성된다. 제품에 따라 개수는 다르지만 하나의 프로세스로 구성되어 동작하지 않고 여러개로 분리되어 각각의 역할을 수행하도록 만들어져 있다.
Tablespace란?
Tablespace는 일부 DB에서 등장하는 개념이다. 오라클과 PostgreSQL에서만 사용되고 MySQL과 MS-SQL 같은 DBMS에서는 언급조차 되지 않는 개념이다.
하지만 PostgreSQL에서 DB는 PGDATA라는 환경변수에 지정된 디렉토리를 통째로 DB로 사용한다. 그리고 그 하위에 테이블이 파일로 생성된다. 즉 테이블스페이스를 따로 생성하지 않아도 DB에 사용자 테이블을 만들 수 있다. 기본적으로 DB로 지정된 디렉토리 전체가 하나의 기본 테이블스페이스로 인식되는 것이다.
PostgreSQL 공식사이트에서는 다음과 같이 테이블스페이스를 정의하고 있다.
포스트그레스-큐엘에서 테이블스페이스는 DB관리자에 의해 데이터베이스의 객체가 저장될 수 있는 파일시스템의 경로로 정의된다. 테이블스페이스가 생성되면 데이터베이스 객체에 객체를 생성할 때 이름에 의해서 테이블스페이스가 참조될 수 있다.
포스트그레스-큐엘이 설치될 때 디스크레이아웃에 따라 관리자가 생성할 수 있는데 두가지 관점에서 매우 유용하다. 첫번째는 DB가 생성된 볼륨 또는 파티션에 여유공간이 부족할 때 테이블스페이스를 다른 파티션이나 디스크에 생성하여 시스템을 재구성할 때까지 DB를 확장할 수 있다. 또 다른 하나는 데이터베이스 객체의 성능 최적화를 위해 사용할 수 있다는 것이다. 예를 들어 매우 사용량이 많으면서 자주 업데이트 되는 인덱스를 위해 고성능 SSD를 장착하고테이블스페이스를 생성하여 인덱스를 SSD에 생성하여 성능을 개선하는데 사용될 수 있다.
만약 물리적으로 다른 경로를 DB에 사용하고자 할 때 테이블스페이스로 지정하여 DB를 확장할 수 있다.