PostgreSQL 02: 스키마 & DML 실습 (오라클 비교)

실습 환경

OS	Ubuntu 22.04 Server
PostgreSQL	14
CPU	2코어
RAM	2GB
디스크	40GB
선행 조건	PostgreSQL 01 실습 완료 (testuser / testdb 생성된 상태)

---

1. 스키마(Schema) 개념 이해

• 오라클에서는 유저를 생성하면 같은 이름의 스키마가 자동으로 생성됨. 유저와 스키마가 사실상 동일한 개념
• PostgreSQL에서는 유저(Role)와 스키마가 분리됨 
• 하나의 데이터베이스 안에 여러 스키마 생성 가능. 스키마는 테이블을 담는 네임스페이스 역할

개념	오라클	PostgreSQL
유저 생성 시 스키마	자동 생성 (유저 = 스키마)	자동 생성 안 됨 (분리)
기본 스키마	유저명과 동일	public
스키마 지정 방법	유저명.테이블명	스키마명.테이블명
스키마 생성	CREATE USER 시 자동	CREATE SCHEMA 별도 실행

 

-- postgres 계정으로 psql 접속
sudo -i -u postgres
psql

-- testdb에 접속
\c testdb

-- 현재 스키마 확인 (기본값: public)
SHOW search_path;

-- 스키마 목록 확인
\dn

postgres 계정으로 testdb 접속 후 현재 스키마 및 스키마 목록 확인

-- 새 스키마 생성
CREATE SCHEMA myschema AUTHORIZATION testuser;

-- 스키마 목록 다시 확인
\dn

testuser 소유 myschema 생성

---

2. 테이블 생성 — SERIAL vs IDENTITY (오라클 SEQUENCE 비교)

• 오라클에서는 자동 증가 값을 위해 시퀀스를 별도로 생성한 뒤 컬럼에 연결
• PostgreSQL에서는 SERIAL 키워드 또는 GENERATED ALWAYS AS IDENTITY로 자동 증가 간단하게 처리

개념	오라클	PostgreSQL
자동 증가 컬럼	CREATE SEQUENCE + seq.NEXTVAL	SERIAL 또는 IDENTITY
NUMBER 타입	NUMBER(10)	INTEGER / BIGINT
VARCHAR2	VARCHAR2(100)	VARCHAR(100)
DATE (날짜+시간)	DATE	TIMESTAMP
현재 시간	SYSDATE	NOW() / CURRENT_TIMESTAMP

-- testuser로 testdb 접속
psql -U testuser -d testdb -h 127.0.0.1

-- SERIAL 방식으로 테이블 생성 (PostgreSQL 전통 방식)
CREATE TABLE myschema.employees (
    emp_id     SERIAL PRIMARY KEY,
    emp_name   VARCHAR(100) NOT NULL,
    dept_name  VARCHAR(50),
    salary     NUMERIC(10, 2),
    hire_date  TIMESTAMP DEFAULT NOW()
);

testuser로 testdb 접속 후 serial 방식으로 employees 테이블 생성

-- IDENTITY 방식으로 테이블 생성 (SQL 표준, 권장 방식)
CREATE TABLE myschema.departments (
    dept_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    dept_name  VARCHAR(50) NOT NULL,
    location   VARCHAR(100)
);

identity 방식으로 departments 테이블 생성

-- 테이블 목록 확인
\dt myschema.*

-- 테이블 구조 확인 (오라클의 DESC 대응)
\d myschema.employees

테이블 목록 확인 및 각 테이블 구조 확인

3. DML 실습 — INSERT / UPDATE / DELETE

 

INSERT

-- departments에 데이터 삽입
INSERT INTO myschema.departments (dept_name, location)
VALUES ('Engineering', 'Seoul'),
       ('Operations', 'Busan'),
       ('Planning', 'Seoul');

-- 결과 확인
SELECT * FROM myschema.departments;

departments에 데이터 삽입 후 확인

-- employees에 데이터 삽입
INSERT INTO myschema.employees (emp_name, dept_name, salary)
VALUES ('Kim Minjun', 'Engineering', 4500000),
       ('Lee Seoyeon', 'Engineering', 4200000),
       ('Park Jihun', 'Operations', 3800000),
       ('Choi Sua', 'Planning', 3500000),
       ('Jung Dohyun', 'Operations', 4000000);

-- 결과 확인
SELECT * FROM myschema.employees;

employees에 데이터 삽입 후 확인

 

RETURNING 절 — 오라클에 없는 PostgreSQL 문법

• 오라클은 INSERT/UPDATE/DELETE 후 변경된 값을 확인하려면 별도 SELECT 필요
• PostgreSQL은 RETURNING 절로 변경된 행의 값을 바로 반환받을 수 있음

-- INSERT 후 생성된 행 바로 반환
INSERT INTO myschema.employees (emp_name, dept_name, salary)
VALUES ('Han Jimin', 'Engineering', 4800000)
RETURNING emp_id, emp_name, hire_date;

insert 후 생성된 행 바로 반환

-- UPDATE 후 변경된 행 확인
UPDATE myschema.employees
SET salary = salary * 1.1
WHERE dept_name = 'Engineering'
RETURNING emp_id, emp_name, salary;

update 후 변경된 행 확인

-- DELETE 후 삭제된 행 확인
DELETE FROM myschema.employees
WHERE emp_name = 'Han Jimin'
RETURNING emp_id, emp_name;

delete 후 삭제된 행 확인

 

UPDATE / DELETE 기본 실습

-- 특정 직원 급여 수정
UPDATE myschema.employees
SET salary = 4300000
WHERE emp_name = 'Lee Seoyeon';

-- 결과 확인
SELECT emp_id, emp_name, salary FROM myschema.employees;

특정 직원 급여 수정 후 확인

-- 기획팀 직원 삭제
DELETE FROM myschema.employees
WHERE dept_name = 'Planning';

-- 결과 확인
SELECT * FROM myschema.employees;

기획팀 직원 삭제 후 확인

---

4. 트랜잭션 처리

• 오라클은 DML 실행 시 자동으로 트랜잭션 시작, COMMIT 전까지 반영되지 않음
• PostgreSQL도 동일하게 동작하지만, psql 클라이언트 기본 설정이 autocommit ON 상태.
• 명시적 트랜잭션은 BEGIN으로 시작

개념	오라클	PostgreSQL
트랜잭션 시작	DML 실행 시 자동	BEGIN 명시 (또는 autocommit)
커밋	COMMIT	COMMIT
롤백	ROLLBACK	ROLLBACK
저장점	SAVEPOINT	SAVEPOINT

-- 명시적 트랜잭션 시작
BEGIN;

INSERT INTO myschema.employees (emp_name, dept_name, salary)
VALUES ('test_user', 'Engineering', 9999999);

-- 현재 세션에서 확인 (다른 세션에서는 아직 안 보임)
SELECT * FROM myschema.employees WHERE emp_name = 'test_user';

-- 롤백
ROLLBACK;

-- 롤백 후 확인 (사라진 것 확인)
SELECT * FROM myschema.employees WHERE emp_name = 'test_user';

명시적 트랜잭션 시작 후 update, 현재 세션에서는 확인 가능, 다른 세션에서는 확인 불가

롤백 후엔 사라진 것 확인 가능

-- SAVEPOINT 실습
BEGIN;

INSERT INTO myschema.departments (dept_name, location) VALUES ('Temp_A', 'Seoul');
SAVEPOINT sp1;

INSERT INTO myschema.departments (dept_name, location) VALUES ('Temp_B', 'Busan');

-- sp1 이후만 롤백 (Temp_A는 유지, Temp_B만 취소)
ROLLBACK TO sp1;

COMMIT;

-- 결과 확인 (Temp_A만 남아 있어야 함)
SELECT * FROM myschema.departments;

트랜잭션 시작 후 Temp_A를 insert한 뒤 세이브포인트 sp1 생성, 이어서 Temp_B를 insert

세이브포인트 sp1으로 롤백 후 커밋, 조회 결과 Temp_A만 insert 완료

---

5. 조회 실습 — 오라클과의 차이점 위주

 

LIMIT / OFFSET (오라클 ROWNUM 대응)

개념	오라클	PostgreSQL
상위 N건 조회	WHERE ROWNUM <= N / FETCH FIRST N ROWS ONLY	LIMIT N
페이지 처리	OFFSET (12c 이후 지원)	LIMIT N OFFSET M

-- 연봉 액수 상위 3건 조회
SELECT * FROM myschema.employees
ORDER BY salary DESC
LIMIT 3;

연봉 액수 상위 3건 조회

-- 페이지 처리 (2번째부터 2건)
SELECT * FROM myschema.employees
ORDER BY emp_id
LIMIT 2 OFFSET 2;

emp_id 3번부터 2건 조회

 

NULL 처리 — COALESCE (오라클 NVL 대응)

-- 부서 이름 없는 테스트용 사원 생성
INSERT INTO myschema.employees (emp_name, salary)
VALUES ('Test_A', 9999999);

-- NVL 대응: COALESCE
SELECT emp_name,
       COALESCE(dept_name, 'Unassigned') AS dept_name
FROM myschema.employees;

부서 이름이 없는 Test_A는 dept_name이 Unassigned로 처리됨

 

문자열 연결 — || 동일, CONCAT 함수도 사용 가능

-- 오라클과 동일하게 || 사용 가능
SELECT emp_name || ' (' || dept_name || ')' AS emp_info
FROM myschema.employees;

문자열 연결해서 사원 이름 및 부서 출력

---

6. 시스템 카탈로그 조회 — 오라클 딕셔너리 뷰 대응

• 오라클에서는 DBA_TABLES, DBA_USERS 등의 딕셔너리 뷰로 메타 정보 조회
• PostgreSQL은 시스템 카탈로그(pg_catalog) 또는 information_schema 사용

조회 대상	오라클	PostgreSQL
테이블 목록	DBA_TABLES	pg_tables / \dt
컬럼 정보	DBA_TAB_COLUMNS	information_schema.columns
유저(Role) 목록	DBA_USERS	pg_roles / \du
세션 정보	V$SESSION	pg_stat_activity
인덱스 정보	DBA_INDEXES	pg_indexes

-- 테이블 목록 조회
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname = 'myschema';

myschema에 속한 테이블 목록 조회

-- 컬럼 정보 조회
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'myschema'
  AND table_name = 'employees';

myschema의 employees 테이블 컬럼 정보 조회

-- 현재 세션 정보 조회 (오라클 V$SESSION 대응)
SELECT pid, usename, datname, state, query
FROM pg_stat_activity
WHERE datname = 'testdb';

 

현재 세션 정보 조회

---

오라클 vs PostgreSQL DML/스키마 핵심 비교 정리

항목	오라클	PostgreSQL
자동 증가 컬럼	SEQUENCE + NEXTVAL	SERIAL / IDENTITY
변경 결과 즉시 반환	미지원 (별도 SELECT)	RETURNING 절
상위 N건 조회	ROWNUM / FETCH FIRST	LIMIT
NULL 대체 함수	NVL()	COALESCE()
현재 시간 함수	SYSDATE	NOW() / CURRENT_TIMESTAMP
유저 = 스키마	O (자동 매핑)	X (분리)
트랜잭션 시작	DML 시 자동	BEGIN 명시 권장
메타 정보 조회	DBA_ 뷰	pg_* 카탈로그 / information_schema

---