PostgreSQL 정리

PostgreSQL 개념 정리

1. PostgreSQL이란?

PostgreSQL은 객체-관계형 데이터베이스 관리 시스템(ORDBMS)으로, 데이터를 저장하고 관리하는데 사용됩니다. 오픈 소스이며, SQL 표준을 준수하고 ACID(원자성, 일관성, 격리성, 지속성) 특성을 지원하는 강력한 데이터베이스 시스템입니다.

주요 특징

• 오픈 소스: 무료로 사용할 수 있으며, 커스터마이징이 가능합니다.
• 확장성: 다양한 데이터 타입과 확장 기능을 지원합니다.
• 고성능: 대규모 데이터 처리와 복잡한 쿼리도 효율적으로 처리합니다.
• ACID 지원: 데이터의 신뢰성과 일관성을 보장합니다.

---

2. PostgreSQL의 구조

PostgreSQL은 다음과 같은 주요 구조로 이루어져 있습니다

• Database: 데이터를 저장하는 기본 단위입니다. 여러 개의 데이터베이스를 하나의 PostgreSQL 인스턴스에서 관리할 수 있습니다.
• Schema: 데이터베이스 내에서 테이블, 뷰, 함수 등의 객체들을 논리적으로 그룹화하는 역할을 합니다.
• Table: 데이터를 행(row)과 열(column) 형태로 저장하는 구조입니다.
• Index: 데이터를 빠르게 조회할 수 있도록 돕는 구조입니다.
• Role: 사용자와 권한을 관리하는 구조입니다. 권한을 통해 특정 작업을 할 수 있는지 여부를 결정합니다.

---

3. SQL 기본 명령어

PostgreSQL에서 데이터를 조작하는 기본적인 SQL 명령어는 다음과 같습니다

1. 데이터베이스 생성 및 삭제

• CREATE DATABASE: 새로운 데이터베이스를 생성합니다

CREATE DATABASE mydb;

• DROP DATABASE: 기존 데이터베이스를 삭제합니다.

DROP DATABASE mydb;

2. 테이블 생성 및 삭제

• CREATE  TABLE: 새로운 테이블을 생성합니다.

CREATE TABLE users ( id SERIAL PRIMARY KEY, 
					user_id VARCHAR(80) NOT NULL, 
                    user_pw VARCHAR(20) NOT NULL );

• DROP TABLE: 기존 테이블을 삭제합니다.

 

DROP TABLE users;

3. 데이터 삽입 및 수정

• INSERT INTO: 테이블에 데이터를 삽입합니다.
   
• UPDATE: 기존 데이터를 수정합니다.

INSERT INTO users (user_id, user_pw) VALUES ('admin', '1234');

• UPDATE: 기존 데이터를 수정합니다.

INSERT INTO users (user_id, user_pw) VALUES ('admin', '1234');

4. 데이터 삭제

• DELETE: 특정 조건에 맞는 데이터를 삭제합니다.

DELETE FROM users WHERE user_id = 'admin';

5. 데이터 조회

• SELECT: 데이터를 조회합니다.

SELECT * FROM users;

---

4. PostgreSQL의 데이터 타입

PostgreSQL에서는 다양한 데이터 타입을 제공합니다. 주요 데이터 타입은 다음과 같습니다:

• 문자열 타입: CHAR, VARCHAR, TEXT
• 숫자 타입: INTEGER, DECIMAL, FLOAT
• 날짜 및 시간 타입: DATE, TIME, TIMESTAMP
• 불리언 타입: BOOLEAN
• 배열: ARRAY
• JSON: JSON, JSONB (비정형 데이터 처리)

---

5. PostgreSQL의 주요 기능

1. 트랜잭션(Transaction)

PostgreSQL은 ACID 트랜잭션을 지원하여 데이터의 원자성, 일관성, 격리성, 지속성을 보장합니다. 트랜잭션을 사용하면 여러 쿼리가 하나의 단위로 실행되며, 중간에 오류가 발생하면 모든 작업이 취소됩니다.

2. 외래 키(Foreign Key)

테이블 간의 관계를 설정할 수 있습니다. 이를 통해 데이터 무결성을 유지하고, 참조되는 데이터가 존재하는지 확인할 수 있습니다.

3. 인덱스(Index)

데이터 조회 성능을 최적화할 수 있습니다. 인덱스는 특정 컬럼에 대해 빠르게 데이터를 검색할 수 있도록 도와줍니다.

4. 뷰(View)

복잡한 쿼리를 재사용하기 위해 뷰를 사용할 수 있습니다. 뷰는 SELECT 쿼리의 결과를 가상 테이블로 제공하며, 실제 데이터를 변경하지 않습니다.

5. 함수 및 트리거

PostgreSQL에서는 사용자 정의 함수와 트리거를 사용하여 데이터베이스 작업을 자동화할 수 있습니다. 예를 들어, 데이터 삽입 시 자동으로 시간 기록을 추가하는 트리거를 만들 수 있습니다.

---

6. PostgreSQL 설치

PostgreSQL은 다양한 운영체제에서 설치할 수 있습니다. 일반적으로는 공식 웹사이트에서 다운로드하여 설치하거나 패키지 관리자를 통해 설치할 수 있습니다.

 

Windows에서 설치

Windows에서는 PostgreSQL 공식 웹사이트에서 설치 파일을 다운로드하여 설치할 수 있습니다.

---

7. PostgreSQL과 다른 DBMS의 차이점

PostgreSQL은 관계형 데이터베이스 시스템(RDBMS) 중에서도 기능이 매우 풍부하고 확장성이 뛰어난 시스템입니다. MySQL이나 SQLite와 같은 다른 RDBMS와 비교했을 때, PostgreSQL은 복잡한 쿼리와 대규모 데이터 처리에 강점이 있습니다. 또한 JSON과 같은 비정형 데이터를 다룰 때도 유리합니다.

 

 

8. users 테이블에 데이터 삽입

SQL

INSERT INTO users (id, user_id, user_pw, created_date) VALUES 
(1, 'admin', '1234', '2025-04-17'), 
(2, 'user1', '4321', '2025-04-17'), 
(3, 'user2', '4321', '2025-04-17'), 
(4, 'user3', '4321', '2025-04-17');

결과

 id | user_id | user_pw | created_date
----+---------+---------+--------------
  1 | admin   | 1234    | 2025-04-17
  2 | user1   | 4321    | 2025-04-17
  3 | user2   | 4321    | 2025-04-17
  4 | user3   | 4321    | 2025-04-17

 

---

9. board 테이블에 게시글 삽입

SQL

INSERT INTO board (id, board_user, created_date, title, description, likes)
VALUES
    (1, 1, '2025-04-17', 'good', '좋다', 3),
    (2, 1, '2025-04-17', 'bad', '나쁘다', 3),
    (3, 4, '2025-04-17', 'sea', '바다다', 4),
    (4, 4, '2025-04-17', 'power', '힘찬 하루', 1);​

 

결과

 id | board_user | created_date | title | description | likes
----+------------+--------------+-------+-------------+-------
  1 |          1 | 2025-04-17   | good  | 좋다        |     3
  2 |          1 | 2025-04-17   | bad   | 나쁘다      |     3
  3 |          4 | 2025-04-17   | sea   | 바다다      |     4
  4 |          4 | 2025-04-17   | power | 힘찬 하루   |     1

---

10. users 테이블에서 비밀번호 변경 (id = 5)

SQL

UPDATE users SET user_pw = '1234' WHERE id = 5;

 

결과

 id | user_id | user_pw | created_date
----+---------+---------+--------------
  1 | admin   | 1234    | 2025-04-17
  2 | user1   | 4321    | 2025-04-17
  3 | user2   | 4321    | 2025-04-17
  4 | user3   | 4321    | 2025-04-17
  5 | user4   | 1234    | 2025-04-17

 

---

11. board 테이블에서 'sea' 게시글 삭제

SQL

DELETE FROM board WHERE title = 'sea';

결과

 id | board_user | created_date | title | description | likes
----+------------+--------------+-------+-------------+-------
  1 |          1 | 2025-04-17   | good  | 좋다        |     3
  2 |          1 | 2025-04-17   | bad   | 나쁘다      |     3
  4 |          4 | 2025-04-17   | power | 힘찬 하루   |     1

---

12. board 테이블에서 id 수정 (4 -> 3)

SQL

UPDATE board SET id = 3 WHERE id = 4;

결과

 id | board_user | created_date | title | description | likes
----+------------+--------------+-------+-------------+-------
  1 |          1 | 2025-04-17   | good  | 좋다        |     3
  2 |          1 | 2025-04-17   | bad   | 나쁘다      |     3
  3 |          4 | 2025-04-17   | power | 힘찬 하루   |     1

---

 

2. DDL (Data Definition Language) - 데이터 정의어

DDL은 데이터베이스 구조를 정의하고 관리하는 데 사용되는 SQL 명령어입니다. 테이블, 스키마, 인덱스, 뷰 등을 정의하는 데 사용됩니다.

1. CREATE DATABASE

새로운 데이터베이스를 생성하는 명령어입니다.

CREATE DATABASE mydb;

2. CREATE SCHEMA

새로운 스키마를 생성하는 명령어입니다. 스키마는 데이터베이스 내에서 객체를 그룹화하는 용도로 사용됩니다.

CREATE SCHEMA myschema;

3. CREATE TABLE

새로운 테이블을 생성하는 명령어입니다.

 

CREATE TABLE users ( id SERIAL PRIMARY KEY, user_id VARCHAR(80) NOT NULL, user_pw VARCHAR(20) NOT NULL );

4. ALTER TABLE

기존 테이블의 구조를 변경하는 명령어입니다. 예를 들어, 테이블에 새로운 열을 추가하거나 기존 열의 이름을 변경할 수 있습니다.

 

ALTER TABLE users ADD COLUMN email VARCHAR(100);

5. DROP DATABASE

기존 데이터베이스를 삭제하는 명령어입니다.

 

DROP DATABASE mydb;

6. DROP TABLE

기존 테이블을 삭제하는 명령어입니다.

DROP TABLE users;

 

---

3 DML (Data Manipulation Language) - 데이터 조작어

DML은 데이터베이스 내 데이터를 조작하는 명령어입니다. 데이터를 삽입, 수정, 삭제, 조회하는 데 사용됩니다.

1. SELECT

데이터를 조회하는 명령어입니다. 특정 테이블에서 데이터를 가져옵니다.

SELECT * FROM users;

 

2. INSERT INTO

테이블에 새로운 데이터를 삽입하는 명령어입니다.

 

INSERT INTO users (user_id, user_pw) VALUES ('admin', '1234');

3. UPDATE

기존 데이터를 수정하는 명령어입니다.

 

UPDATE users SET user_pw = 'newpassword' WHERE user_id = 'admin';

4. DELETE

기존 데이터를 삭제하는 명령어입니다.

 

DELETE FROM users WHERE user_id = 'admin';

---

4 DCL (Data Control Language) - 데이터 제어어

DCL은 데이터베이스의 보안과 권한 관리를 담당하는 명령어입니다.

1. GRANT

사용자에게 특정 권한을 부여하는 명령어입니다.

 

GRANT SELECT, INSERT ON users TO user1;

2. REVOKE

사용자에게 부여한 권한을 취소하는 명령어입니다.

 

REVOKE SELECT ON users FROM user1;

---

5. 스키마 (Schema)

스키마는 데이터베이스 내에서 테이블, 뷰, 함수, 인덱스 등 데이터베이스 객체들을 논리적으로 그룹화하는 단위입니다. 여러 스키마를 사용하여 데이터베이스 내의 객체들을 구분할 수 있습니다. PostgreSQL에서는 기본적으로 public 스키마가 제공됩니다.

스키마 사용 예시

• 스키마 생성

CREATE SCHEMA myschema;

• 특정 스키마의 테이블 조회

SELECT * FROM myschema.users;

---

6. 테이블 (Table)

테이블은 데이터를 저장하는 기본적인 구조입니다. 데이터는 행(Row)과 열(Column) 형태로 저장됩니다. 테이블을 정의할 때, 각 열의 데이터 타입과 제약 조건을 지정할 수 있습니다.

테이블 생성 예시

CREATE TABLE employees ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, department VARCHAR(50) );

---

7. 조건절 (Conditionals)

SQL 쿼리에서 조건을 설정할 때 사용되는 여러 가지 조건절을 소개합니다. 조건절은 데이터를 조회하거나 수정할 때 특정 조건을 만족하는 데이터를 선택하거나 변경하는 데 사용됩니다.

1. WHERE

WHERE 절은 특정 조건을 만족하는 행만 선택하거나 수정하는 데 사용됩니다.

SELECT * FROM users WHERE user_id = 'admin';

2. AND, OR

여러 조건을 결합할 때 사용합니다.

SELECT * FROM users WHERE user_id = 'admin' AND user_pw = '1234';

3. BETWEEN

범위 내의 값을 선택하는 데 사용됩니다.

SELECT * FROM employees WHERE age BETWEEN 30 AND 40;

4. IN

여러 값 중 하나와 일치하는 데이터를 선택할 때 사용됩니다.

 

SELECT * FROM users WHERE user_id IN ('admin', 'user1', 'user2');

5. LIKE

문자열에서 특정 패턴을 찾을 때 사용됩니다. %는 여러 문자를, _는 하나의 문자를 의미합니다.

 

SELECT * FROM users WHERE user_id LIKE 'admin%';

6. IS NULL / IS NOT NULL

NULL 값을 가진 데이터 조회에 사용됩니다.

 

SELECT * FROM users WHERE email IS NULL;

7. ORDER BY

결과를 정렬할 때 사용됩니다.

SELECT * FROM employees ORDER BY age DESC;

8. GROUP BY

결과를 그룹화하여 집계 함수(Aggregate Function)와 함께 사용할 때 사용됩니다.

SELECT department, COUNT(*) FROM employees GROUP BY department;

 

9. HAVING

GROUP BY와 함께 사용되며, 그룹화된 데이터에 조건을 설정할 때 사용됩니다.

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 2;