PostgreSQL 기초 실습 및 개념

출처 : https://www.postgresql.org/

 

1. DB & Table 생성 실습

-- 테이블 생성 : 숫자 및 문자형
CREATE TABLE develop_book(
book_id INTEGER, -- 숫자형
pub_date  INTEGER, -- 숫자형
book_name VARCHAR(80), -- 문자형
price MONEY -- 화폐형
);

-- 데이터 추가 : 숫자 및 문자형
INSERT INTO develop_book(book_id, pub_date, book_name, price)
VALUES(1, 20231013, '잠', 15000);

 

 

1) 데이터 타입

숫자형 (Numeric)

- INTEGER : 정수

- NUMERIC : 가변 소수점 실수

- FLOAT : 부동 소수점 실수

- SERIAL : INTEGER 기본값, 1씩 자동 생성

 

문자형 (Character)

- VACHAR(N) : N길이 내에서의 길이 저장

- CHAR(N) : N길이 저장 공백도 처리

- TEXT : 길이와 상관없이 모든 문자열 저장

 

-- 테이블 생성 : 날짜 및 시간
CREATE TABLE datetime_study(
type_ts TIMESTAMP, -- 날짜와 시간 모두
type_tstz TIMESTAMPTZ, -- 날짜, 시간, 시간대 포함
type_date DATE, -- 날짜 정보만 표시
type_time TIME -- 시간 정보만 표시
);

-- 데이터 추가 : 날짜 및 시간
INSERT INTO datetime_study(type_ts, type_tstz, type_date, type_time)
VALUES(
'2023-10-13 10:00:01+09', '2023-10-13 10:00:02+09', '2023-10-13', '10:00:01'

 

날짜 및 시간 (Date & Time)

- TIMESTAMP : 날짜와 시간 모두

- TIMESTAMPTZ : 날짜와 시간, 시간대 모두

- INTERVAL : 시간 간격

- DATE : 날짜

- TIME : 시간

 

불리언형 

-  TRUE : 참, YES, 1..

-  FALSE : 거짓, NO, 0..

-  NULL : 알 수 없는 정보 또는 일부 불확실

 

-- 테이블 생성 : 배열형 테이블
CREATE TABLE contact_info(
cont_id NUMERIC(3),
name VARCHAR(15),
tel INTEGER[],
email VARCHAR
);

-- 데이터 추가 : 배열형 테이블
-- 배열형의 경우 ARRAY[] 또는 {}로 데이터 저장
INSERT INTO contact_info
VALUES(001, 'kathy', ARRAY[01012345678, 01011113333],'abc@gmail.com');

INSERT INTO contact_info
VALUES(002, 'ella', '{01012345670, 01011113330}','def@gmail.com');

 

배열형

하나의 변수에 여러 값을 저장할 수 있는 데이터 타입.

INTEGER[], VARCHAR[], NUMERIC[] 등과 같이 저장 가능.

 

배열형 : contact_info 조회 결과

CREATE TABLE develop_book_order(
id NUMERIC(3),
order_info JSON NOT NULL
);

INSERT INTO develop_book_order 
VALUES(001, '{"customer": "evan", "books": {"product": "postgreSQL", "qty": 2}}');

JSON형

텍스트 기반의 경량 데이터 교환 방식. (이진 형태로 저장되어 빠른 쿼리 수행이 가능)

JSON 형태로 데이터 저장.

 

JSON형 : develop_book_order 조회 결과

 

2. CAST 연산자

-- 형변환 : CAST 메서드 또는 '::'활용
SELECT book_id, CAST(pub_date AS VARCHAR) -- pub_date의 데이터 타입을 VARCHAR로 변환
FROM develop_book;

SELECT book_id, pub_date::VARCHAR -- '::' 기호를 통해 위와 동일하게 변환
FROM develop_book;

 

CAST 연산자는 데이터 타입을 변환하는데 사용되는 연산자

'::' 기호를 통해 동일한 결과를 얻을 수 있음.

 

3. 컬럼 값 제한하기

1) 무결성(Integrity)

데이터베이스의 일관성과 정확성을 보장하려는 특성

 

(1) 도메인 무결성 
- 각 열에 저장된 데이터의 유효성을 보장.

- 각 열은 정의된 도메인에 속하는 값만을 가질 수 있어야 함.

-- 테이블 생성 , 숫자가 0~9인 숫자만 입력되도록 설정
CREATE DOMAIN phoneint AS INTEGER CHECK (VALUE > 0 AND VALUE <9);

CREATE TABLE domain_type_study(
id phoneint
);

INSERT INTO domain_type_study VALUES(1); -- 성공
INSERT INTO domain_type_study VALUES(10); -- 실패 (숫자가 0~9에 속하지 않기 때문에 삽입 실패)

(2) 5가지 제약 조건

 

1. NOT NULL 제약조건

: 빈 값을 허용하지 않는 조건

-- NOT NULL
DROP TABLE IF EXISTS contact_info;

CREATE TABLE contact_info(
cont_id NUMERIC(3) NOT NULL,
name VARCHAR(15) NOT NULL,
tel INTEGER[] NOT NULL,
email VARCHAR -- NOT NULL 제약 조건이 없음, NULL 값이 허용됨.
);

 

2. UNIQUE 제약조건 

: 유일한 값을 갖는 조건

-- 여러 컬럼에 UNIQUE 적용
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id NUMERIC(3) NOT NULL,
name VARCHAR(15) NOT NULL,
tel INTEGER[] NOT NULL,
email VARCHAR,
UNIQUE(cont_id, tel, email) -- 해당 열에는 유일한 값만 있도록 지정
);

3. Primary Key(기본키) 제약 조건

: NOT NULL + UNIQUE 제약 조건

-- 기본키 지정
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
cont_id SERIAL NOT NULL PRIMARY KEY, -- cont_id 기본키 설정, 테이터 타입 SERIAL로 지정
name VARCHAR(15) NOT NULL,
tel INTEGER[] NOT NULL,
email VARCHAR
);

기본키로 설정된 cont_id 열은 각 행을 고유하게 식별하고

SERIAL 타입으로 자동으로 증가하게 된다.

 

4. Foreign Key(외래키) 제약조건

- 부모 테이블이 자식 테이블보다 먼저 생성

- 부모 테이블은 자식 테이블과 같은 데이터 타입

- 부모 테이블에서 참조된 컬럼의 값만 자식 테이블에서 입력 가능

- 참조된 컬럼은 모두 Primary Key이거나 UNIQUE 제약조건 형식

- REFERENCES 부모 테이블(컬럼명) 으로 외래키 제약조건을 생성.

 

-- 외래키 지정
CREATE TABLE book(
book_id SERIAL NOT NULL,
name VARCHAR(15) NOT NULL,
admin_no SERIAL NOT NULL REFERENCES contact_info(cont_id), -- REFERENCES 부모 테이블(컬럼명)
email VARCHAR,
PRIMARY KEY (book_id, admin_no)
);

 

*외래키 예시

-- 수업 과목 테이블 생성
CREATE TABLE subject(
subj_id NUMERIC(5) NOT NULL PRIMARY KEY,
subj_name VARCHAR(10) NOT NULL
);

-- 수업 과목 데이터 추가
INSERT INTO subject
VALUES(00001, '수학'), (00002, '과학'), (00003, '사회');
INSERT INTO subject
VALUES(00004, '국어');

-- 선생님 테이블 생성
CREATE TABLE teacher(
teach_id NUMERIC(5) NOT NULL PRIMARY KEY,
teach_name VARCHAR(20) NOT NULL,
subj_id NUMERIC(5) REFERENCES subject,
teach_certified_date DATE
);

-- 선생님 테이블 추가
INSERT INTO teacher
VALUES(00011, '정선생', 00001, '2023-10-12');
INSERT INTO teacher
VALUES(00021, '김선생', 00002, '2023-10-12');
INSERT INTO teacher
VALUES(00031, '박선생', 00003, '2023-10-12');

 

수업 과목 테이블, 선생님 테이블을 각각 생성하고

수업 과목 테이블에서는 sub_id를 primary key(기본키)로 설정하고

선생님 테이블에서는 sub_id를 외래키로 설정하였음.

수업 과목 테이블에 있는 과목 코드에 맞춰서

선생님 테이블에 데이터를 추가하니 문제 없이 데이터가 추가되었음.

 

 

* 에러 주의 *

INSERT INTO teacher
VALUES(00041, '이선생', 00004, '2023-10-12');

 

위의 쿼리문은 subject 테이블에 없는 sub_id인 4인 값을 teacher 테이블에 추가한다는 내용.

외래키는 부모 테이블의 기본키를 참조하므로 부모 테이블에 없는 값을 외래키를 가질 수는 없음.

에러를 해결하려면 subject 테이블에 해당 값을 미리 넣어두어야 함.

5. CHECK 제약조건

특정 컬럼에 대해 설정한 조건이 항상 참인지 검사하는데 사용

 

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    -- CHECK 제약조건: 나이는 음수가 아니어야 함
    CONSTRAINT chk_age CHECK (age >= 0)
);

 

위의 쿼리에서 CONSTRAINT chk_age CHECK(age >= 0) 을 통해

나이가 음수가 아니라는 조건을 나타냄.

 

-- 유효한 데이터
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 35);

-- 부적절한 데이터 (음수 나이)
INSERT INTO users (name, age) VALUES ('Charlie', -10); -- 에러 발생

 

음수가 아닌 값을 넣으면 문제없이 데이터가 삽입되지만,

음수값이 나오면 에러가 발생되어 age 컬럼에 음수가 들어가는 것을 방지할 수 있음.

 

 

위의 5가지 제약 조건을 통해 데이터 무결성을 유지하고

조건에 맞지 않는 데이터가 삽입되지 않도록 보장할 수 있음!

 

3. EXISTS 서브쿼리

EXISTS 서브 쿼리는 서브 쿼리가 하나 이상의 행이 존재할 경우

TRUE를 반환하여 외부 쿼리의 결과에 포함시킨다.

행이 존재하지 않을 경우에는 쿼리문이 실행되지 않음. 

 

SELECT * 
FROM real_amount
WHERE EXISTS (
SELECT * FROM assumption_amount
);

WHERE 절의 EXISTS 절에서 assumption_amount 테이블에 결과가 존재하는지 확인,

만약 assumption_amount 테이블에 데이터가 하나라도 존재하면

real_amount 테이블의 해당 행이 결과에 포함됨!