Tables and Data Types

Previous Database Management
πŸ“‹ Topic list
Next CRUD Basics

Tables and Data Types

1. Table Basic Concepts

A table is a structure that stores data organized into rows and columns.

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚                    users ν…Œμ΄λΈ”                       β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”¬β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚   id   β”‚   name   β”‚      email      β”‚  created_at   β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”Όβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚   1    β”‚  κΉ€μ² μˆ˜  β”‚ kim@email.com   β”‚ 2024-01-15    β”‚
β”‚   2    β”‚  이영희  β”‚ lee@email.com   β”‚ 2024-01-16    β”‚
β”‚   3    β”‚  λ°•λ―Όμˆ˜  β”‚ park@email.com  β”‚ 2024-01-17    β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
  컬럼(Column)           ↑ 각 행은 ν•˜λ‚˜μ˜ λ ˆμ½”λ“œ

2. Table Creation

Basic Syntax

CREATE TABLE ν…Œμ΄λΈ”λͺ… (
    컬럼λͺ…1 λ°μ΄ν„°νƒ€μž… [μ œμ•½μ‘°κ±΄],
    컬럼λͺ…2 λ°μ΄ν„°νƒ€μž… [μ œμ•½μ‘°κ±΄],
    ...
);

Basic Example

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

Create Only If Not Exists

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

3. Numeric Data Types

Integer Types

νƒ€μž… 크기 λ²”μœ„
SMALLINT 2 bytes -32,768 ~ 32,767
INTEGER (INT) 4 bytes -2,147,483,648 ~ 2,147,483,647
BIGINT 8 bytes -9κ²½ ~ 9κ²½ 
CREATE TABLE products (
    id INTEGER,
    quantity SMALLINT,
    total_sold BIGINT
);

Auto-Increment (Serial)

νƒ€μž… λ²”μœ„
SMALLSERIAL 1 ~ 32,767
SERIAL 1 ~ 2,147,483,647
BIGSERIAL 1 ~ 9κ²½ 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,  -- μžλ™μœΌλ‘œ 1, 2, 3, ... 증가
    order_date DATE
);

-- PostgreSQL 10+ μ—μ„œλŠ” IDENTITY ꢌμž₯
CREATE TABLE orders (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_date DATE
);

Floating-Point Types

νƒ€μž… μ„€λͺ…
REAL 4 bytes, 6자리 정밀도
DOUBLE PRECISION 8 bytes, 15자리 정밀도
NUMERIC(p, s) μ •ν™•ν•œ 숫자 (p: 전체 자릿수, s: μ†Œμˆ˜μ  자릿수)
DECIMAL(p, s) NUMERICκ³Ό 동일 
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),      -- μ΅œλŒ€ 10자리, μ†Œμˆ˜μ  2자리 (예: 99999999.99)
    weight REAL,               -- 뢀동 μ†Œμˆ˜μ 
    rating DOUBLE PRECISION    -- 더 μ •λ°€ν•œ 뢀동 μ†Œμˆ˜μ 
);

INSERT INTO products (price, weight, rating) VALUES
(19900.00, 1.5, 4.7);

4. Character Data Types

νƒ€μž… μ„€λͺ…
CHAR(n) κ³ μ • 길이 λ¬Έμžμ—΄ (λ‚¨λŠ” 곡간은 곡백으둜 채움)
VARCHAR(n) κ°€λ³€ 길이 λ¬Έμžμ—΄ (μ΅œλŒ€ n자)
TEXT 길이 μ œν•œ μ—†λŠ” λ¬Έμžμ—΄ 
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    code CHAR(10),           -- 항상 10자 (μ½”λ“œ 등에 μ‚¬μš©)
    title VARCHAR(200),      -- μ΅œλŒ€ 200자
    content TEXT             -- 길이 μ œν•œ μ—†μŒ
);

VARCHAR vs TEXT

-- μ‹€μ§ˆμ μœΌλ‘œ 큰 차이 μ—†μŒ. PostgreSQLμ—μ„œλŠ” TEXT μ„ ν˜Έν•˜λŠ” κ²½μš°λ„ 많음
CREATE TABLE posts (
    title VARCHAR(255),  -- 길이 μ œν•œμ΄ ν•„μš”ν•œ 경우
    body TEXT            -- 길이 μ œν•œμ΄ ν•„μš” μ—†λŠ” 경우
);

5. Date/Time Data Types

νƒ€μž… μ„€λͺ… μ˜ˆμ‹œ
DATE λ‚ μ§œλ§Œ 2024-01-15
TIME μ‹œκ°„λ§Œ 14:30:00
TIMESTAMP λ‚ μ§œ + μ‹œκ°„ 2024-01-15 14:30:00
TIMESTAMPTZ λ‚ μ§œ + μ‹œκ°„ + νƒ€μž„μ‘΄ 2024-01-15 14:30:00+09
INTERVAL μ‹œκ°„ 간격 2 days 3 hours 
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,
    start_time TIME,
    created_at TIMESTAMP DEFAULT NOW(),
    scheduled_at TIMESTAMPTZ,
    duration INTERVAL
);

INSERT INTO events (event_name, event_date, start_time, duration) VALUES
('회의', '2024-01-20', '14:00:00', '2 hours'),
('μ›Œν¬μƒ΅', '2024-01-25', '09:00:00', '1 day');

Date/Time Functions

-- ν˜„μž¬ μ‹œκ°„
SELECT NOW();                    -- 2024-01-15 14:30:00.123456+09
SELECT CURRENT_DATE;             -- 2024-01-15
SELECT CURRENT_TIME;             -- 14:30:00.123456+09
SELECT CURRENT_TIMESTAMP;        -- NOW()와 동일

-- λ‚ μ§œ μ—°μ‚°
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '2 hours';
SELECT '2024-01-20'::DATE - '2024-01-15'::DATE;  -- 5 (일수)

-- λ‚ μ§œ μΆ”μΆœ
SELECT EXTRACT(YEAR FROM NOW());
SELECT EXTRACT(MONTH FROM NOW());
SELECT EXTRACT(DOW FROM NOW());  -- μš”μΌ (0=μΌμš”μΌ)

6. Boolean Data Type

κ°’ TRUE FALSE NULL
μž…λ ₯ true, 't', 'yes', 'y', '1' false, 'f', 'no', 'n', '0' null 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    is_admin BOOLEAN DEFAULT false
);

INSERT INTO users (name, is_active, is_admin) VALUES
('κΉ€μ² μˆ˜', true, false),
('κ΄€λ¦¬μž', true, true);

SELECT * FROM users WHERE is_active = true;
SELECT * FROM users WHERE NOT is_admin;

7. JSON Data Types

νƒ€μž… μ„€λͺ…
JSON JSON ν…μŠ€νŠΈ μ €μž₯ (맀번 νŒŒμ‹±)
JSONB JSON λ°”μ΄λ„ˆλ¦¬ μ €μž₯ (인덱싱 κ°€λŠ₯, ꢌμž₯) 
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

INSERT INTO products (name, attributes) VALUES
('λ…ΈνŠΈλΆ', '{"brand": "Samsung", "ram": 16, "storage": "512GB"}'),
('마우슀', '{"brand": "Logitech", "wireless": true, "color": "black"}');

-- JSON 데이터 쑰회
SELECT name, attributes->>'brand' AS brand FROM products;
SELECT name, attributes->'ram' AS ram FROM products;

-- JSON 쑰건 검색
SELECT * FROM products WHERE attributes->>'brand' = 'Samsung';
SELECT * FROM products WHERE (attributes->>'ram')::int >= 16;

-- JSON λ°°μ—΄
INSERT INTO products (name, attributes) VALUES
('ν‚€λ³΄λ“œ', '{"brand": "Keychron", "colors": ["white", "black", "gray"]}');

SELECT attributes->'colors'->0 FROM products WHERE name = 'ν‚€λ³΄λ“œ';  -- "white"

8. Other Data Types

UUID

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO sessions (user_id) VALUES (1);
-- id: 550e8400-e29b-41d4-a716-446655440000

Arrays

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[]
);

INSERT INTO posts (title, tags) VALUES
('PostgreSQL μž…λ¬Έ', ARRAY['database', 'postgresql', 'sql']),
('Docker μ‹œμž‘ν•˜κΈ°', '{"docker", "container", "devops"}');

-- λ°°μ—΄ 쑰회
SELECT title, tags[1] FROM posts;  -- 첫 번째 μš”μ†Œ

-- λ°°μ—΄ 포함 μ—¬λΆ€
SELECT * FROM posts WHERE 'docker' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql'];

ENUM

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

CREATE TABLE user_moods (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    current_mood mood
);

INSERT INTO user_moods (user_id, current_mood) VALUES (1, 'happy');

9. Constraints

PRIMARY KEY

-- 단일 컬럼 κΈ°λ³Έν‚€
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 볡합 κΈ°λ³Έν‚€
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

NOT NULL

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,  -- NULL ν—ˆμš© μ•ˆν•¨
    email VARCHAR(255) NOT NULL
);

UNIQUE

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,  -- 쀑볡 λΆˆκ°€
    phone VARCHAR(20) UNIQUE             -- 쀑볡 λΆˆκ°€ (NULL은 μ—¬λŸ¬ 개 κ°€λŠ₯)
);

-- 볡합 μœ λ‹ˆν¬
CREATE TABLE memberships (
    user_id INTEGER,
    group_id INTEGER,
    UNIQUE (user_id, group_id)
);

DEFAULT

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    quantity INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders DEFAULT VALUES;  -- λͺ¨λ“  컬럼 κΈ°λ³Έκ°’ μ‚¬μš©

CHECK

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC(10, 2) CHECK (price > 0),
    quantity INTEGER CHECK (quantity >= 0),
    discount NUMERIC(3, 2) CHECK (discount >= 0 AND discount <= 1)
);

-- 이름 μžˆλŠ” μ œμ•½μ‘°κ±΄
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    age INTEGER,
    salary NUMERIC(10, 2),
    CONSTRAINT valid_age CHECK (age >= 18 AND age <= 100),
    CONSTRAINT positive_salary CHECK (salary > 0)
);

FOREIGN KEY

-- λΆ€λͺ¨ ν…Œμ΄λΈ”
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- μžμ‹ ν…Œμ΄λΈ”
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category_id INTEGER REFERENCES categories(id)
);

-- 상세 μ˜΅μ…˜
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE CASCADE      -- λΆ€λͺ¨ μ‚­μ œ μ‹œ μžμ‹λ„ μ‚­μ œ
        ON UPDATE CASCADE      -- λΆ€λͺ¨ μˆ˜μ • μ‹œ μžμ‹λ„ μˆ˜μ •
);

ON DELETE / ON UPDATE Options

μ˜΅μ…˜ μ„€λͺ…
CASCADE λΆ€λͺ¨μ™€ ν•¨κ»˜ μ‚­μ œ/μˆ˜μ •
SET NULL NULL둜 μ„€μ •
SET DEFAULT κΈ°λ³Έκ°’μœΌλ‘œ μ„€μ •
RESTRICT μ‚­μ œ/μˆ˜μ • λΆˆκ°€ (κΈ°λ³Έκ°’)
NO ACTION RESTRICT와 μœ μ‚¬

10. Table Modification

Add Column

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT false;

Drop Column

ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users DROP COLUMN IF EXISTS phone;

Change Column Type

ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- 데이터 λ³€ν™˜μ΄ ν•„μš”ν•œ 경우
ALTER TABLE users ALTER COLUMN price TYPE INTEGER USING price::INTEGER;

Rename Column

ALTER TABLE users RENAME COLUMN name TO full_name;

Add/Drop Constraints

-- NOT NULL μΆ”κ°€
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- NOT NULL 제거
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- DEFAULT μ„€μ •
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- DEFAULT 제거
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- μ œμ•½μ‘°κ±΄ μΆ”κ°€
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT valid_age CHECK (age >= 0);

-- μ œμ•½μ‘°κ±΄ μ‚­μ œ
ALTER TABLE users DROP CONSTRAINT users_email_unique;

Rename Table

ALTER TABLE users RENAME TO members;

11. Table Deletion

-- κΈ°λ³Έ μ‚­μ œ
DROP TABLE users;

-- μ‘΄μž¬ν•˜λŠ” κ²½μš°μ—λ§Œ μ‚­μ œ
DROP TABLE IF EXISTS users;

-- 의쑴 객체와 ν•¨κ»˜ μ‚­μ œ
DROP TABLE users CASCADE;

12. Table Information

-- ν…Œμ΄λΈ” λͺ©λ‘
\dt

-- ν…Œμ΄λΈ” ꡬ쑰
\d users

-- 상세 정보
\d+ users

-- SQL 쿼리둜 확인
SELECT
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

13. Practice Examples

Practice: Online Shopping Mall Table Design

-- 1. μ‚¬μš©μž ν…Œμ΄λΈ”
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 2. μΉ΄ν…Œκ³ λ¦¬ ν…Œμ΄λΈ”
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 3. μƒν’ˆ ν…Œμ΄λΈ”
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES categories(id),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 4. μ£Όλ¬Έ ν…Œμ΄λΈ”
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending' CHECK (
        status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')
    ),
    total_amount NUMERIC(12, 2) NOT NULL,
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 5. μ£Όλ¬Έ 상세 ν…Œμ΄λΈ”
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- ν…Œμ΄λΈ” ꡬ쑰 확인
\dt
\d products

Next Steps

Learn about data insertion, querying, updating, and deletion in 04_CRUD_Basics.md!

Previous Database Management
πŸ“‹ Topic list
Next CRUD Basics
← → to navigate between lessons