업무 자동화/PostgreSQL

pgAdmin Study] Table 만들기 & 사용 용어 정의

ToolBOX01 2025. 7. 29. 09:15
반응형

▣ Table이란?

테이블은 데이터베이스에서 데이터를 체계적으로 저장하고 관리하기 위한 기본 구조로, 행(row)과 열(column)로 구성된 격자 형태입니다. 마치 엑셀 시트처럼 생각하면 됩니다. 각 열은 특정 데이터 유형(예: 텍스트, 숫자)을 정의하고, 각 행은 개별 레코드(데이터 항목)를 나타냅니다. 예를 들어, "고객" 테이블에는 이름, 나이, 주소 같은 열이 있고, 각 고객 정보가 행으로 저장됩니다. PostgreSQL 같은 데이터베이스에서 테이블은 데이터를 조직화하고 쿼리로 검색하거나 수정하는 데 사용됩니다.

테이블은 관계형 데이터베이스에서 실제로 데이터가 저장되는 기본적인 구조입니다. 우리가 흔히 보는 표와 같은 형태로, 행(Row 또는 Tuple)과 열(Column 또는 Attribute)로 구성됩니다.

  • 물리적 저장:
    테이블은 데이터베이스 내부에 물리적으로 데이터를 저장합니다.
    즉, 실제 디스크 공간을 차지하며, 데이터가 직접적으로 기록됩니다.
  • 데이터의 기본 단위:
    모든 데이터는 테이블 안에 존재하며, 테이블은 데이터의 집합을 체계적으로 정리하여 관리합니다.
  • 생성 및 수정:
    CREATE TABLE 문을 사용하여 생성하며, ALTER TABLE을 사용하여 구조를 변경하거나 DROP TABLE로 삭제할 수 있습니다.
  • 데이터 조작:
    INSERT, UPDATE, DELETE와 같은 DML(Data Manipulation Language) 명령어를 통해 데이터를 직접 추가, 수정, 삭제할 수 있습니다.
  • 독립성:
    테이블은 다른 테이블에 의존하지 않고 독립적으로 존재할 수 있습니다.
예시: 학생 정보를 담는 Students 테이블은 '학번', '이름', '전공', '학년' 등의 열과 각 학생에 대한 정보를 담는 행들로 구성됩니다.

 

▷ 기본 테이블 구조

1. schema

스키마는 관계형 데이터베이스에서 테이블의 기본 구조를 정의하는 것으로, 쉽게 말해 테이블의 제목 부분이라고 볼 수 있습니다. 이는 테이블의 첫 행인 헤더를 나타내며, 속성(열의 이름)과 자료타입(예: 정수, 문자) 같은 정보를 포함합니다. 스키마는 데이터를 체계적으로 저장하고 관리하기 위한 틀 역할을 합니다.

  • 스키마는 테이블에 적재될 데이터의 구조화 형식 정의 하는 것이다.
  • 스키마는 테이블의 설계도라고 할 수 있다.
  • 테이블에 어떤 데이터를 저장해야하는지 사전에 정의해야 한다.

테이블의 이름을 설정하고 스키마 schema (컬럼 이름과 데이터 타입)를를 설정한다. 컬럼이름= 이름. 나이, 성별
SQL 문법으로 테이블을 만들고, 스키마(schema)를 정의 합니다.  

CREATE TABLE person (
    ID INT NOT NULL,         -- 정수 타입
    name VARCHAR(20) NOT NULL, -- 가변 문자 타입, 최대 20자
    age INT NOT NULL,           -- 정수 타입
    gender CHAR(1) NOT NULL  -- 문자 타입,최대 1자만 저장 
);

 

2. Instance

인스턴스는 테이블에서 실제로 저장된 데이터를 의미합니다. 이는 테이블의 전체 데이터 영역, 즉 외연에 해당하며, 스키마에 정의된 구조에 따라 입력된 구체적인 값들을 포함합니다. 데이터베이스의 현재 상태를 반영하며, 스키마(구조)와 구분되도록 개념적으로 구체화된 데이터를 표현합니다. 인스턴스가 사용됨으로써 데이터의 동적 특성을 이해하고 관리할 수 있습니다.

▷ 인스턴스 데이터 삽입 예시

INSERT INTO person (ID, name, age, gender)
VALUES (1, 'John', 30, 'M');

여기서 (1, 'John', 30, 'M')는 person 테이블에 삽입되는 실제 인스턴스 데이터입니다.

 

▷ 인스턴스 데이터 조회 예시

SELECT * FROM person;

 

3. Tuple 

튜플은 릴레이션(테이블)에서 행(가로)의 개수를 의미하며, 행 또는 카디널리티라고도 불립니다. 이는 테이블에 저장된 개별 데이터 레코드를 나타냅니다. SQL에서 튜플은 테이블의 개별 행을 나타내며, 데이터를 체계적으로 저장하고 조회, 수정, 삭제하는 데 사용됩니다. 튜플을 사용함으로써 각 행이 고유한 데이터 레코드로 관리되고, 관계형 데이터베이스의 구조적 무결성을 유지할 수 있습니다. 튜플은 데이터 조작의 기본 단위로, 테이블 내 데이터를 효율적으로 관리하기 위해 사용됩니다.

 

  • 튜플은 릴레이션에서 행(가로)의 개수를 의미합니다. 테이블에서 튜플의 개수는 3입니다
  • 튜플은 행, 카디널리티 라고 불리기도 합니다
 

▷ 튜플 삽입 (INSERT):

INSERT INTO person (ID, name, age, gender)
VALUES (1, 'John', 30,'M');

여기서 (1, 'John', 30, 'M')는 하나의 튜플로, person 테이블에 새로운 행을 추가합니다.

 

▷ 튜플 조회 (SELECT):

SELECT * FROM person WHERE ID = 1;

이는 ID가 1인 튜플을 조회합니다.

 

▷ 튜플 수정 (UPDATE):

UPDATE person SET name = 'Johnny' WHERE ID = 1;

D가 1인 튜플의 name 값을 'Johnny'로 변경합니다.

 

▷ 튜플 삭제 (DELETE):

DELETE FROM person WHERE ID = 1;

ID가 1인 튜플을 삭제합니다.

 

4. 속성 (attribute)

데이터베이스의 테이블에서 속성(attribute)은 테이블의 열(column)을 의미합니다. 각 속성은 데이터베이스 테이블에 저장되는 특정한 데이터의 특성이나 정보를 나타냅니다. 예를 들어, "학생" 테이블이 있다면 속성으로는 학생의 이름, 학번, 전공, 생년월일 등이 있을 수 있습니다.

주요 특징:

  1. 열(Column)로 표현: 속성은 테이블의 열로 구현되며, 각 열은 특정 데이터 유형(예: 문자열, 숫자, 날짜 등)을 가집니다.
  2. 데이터의 특성 정의: 속성은 엔터티(예: 학생, 책, 주문 등)의 특정 속성을 정의합니다.
  3. 고유성: 각 속성은 테이블 내에서 고유한 이름을 가지며, 데이터의 의미를 명확히 전달합니다.
  4. 데이터 값: 각 속성은 특정 레코드(행, row)에 대해 하나의 값을 가집니다. 예를 들어, 학생 테이블의 "이름" 속성에서 특정 학생의 이름은 "홍길동"일 수 있습니다.

 

▷ 테이블 생성 (CREATE TABLE)

CREATE TABLE Students (
    StudentID INT PRIMARY KEY, -- 속성: 학번 (기본 키)
    Name VARCHAR(50),         -- 속성: 이름
    Major VARCHAR(50),        -- 속성: 전공
    BirthDate DATE            -- 속성: 생년월일
);

학생(테이블 이름 : Students) 테이블을 만들고, 속성(열)으로 학번, 이름, 전공, 생년월일을 정의합니다.

 

▷ 데이터 삽입 (INSERT INTO)

INSERT INTO Students (StudentID, Name, Major, BirthDate)
VALUES 
    (1001, '홍길동', '컴퓨터공학', '2000-01-01'),
    (1002, '김영희', '경영학', '2001-02-15'),
    (1003, '이철수', '전자공학', '1999-05-20');
    
학생 데이터를 테이블에 추가합니다.

 

▷ 데이터 조회 (SELECT)

SELECT * FROM Students;

학생 테이블의 모든 속성을 조회합니다.

 

결과 조회

StudentID,   Name,    Major,       BirthDate
1001,        홍길동,   컴퓨터공학,   2000-01-01
1002,        김영희,   경영학,       2001-02-15
1003,        이철수,   전자공학,     1999-05-20

 

▷ 특정 속성 조회

SELECT Name, Major FROM Students;

이름과 전공만 조회하는 예시입니다.

결과 조회

Name,    Major
홍길동,  컴퓨터공학
김영희,  경영학
이철수,  전자공학

 

▷ 데이터 갱신 (UPDATE)

UPDATE Students
SET Major = '데이터과학'
WHERE StudentID = 1002;

특정 학생의 전공을 변경합니다.

 

▷ 데이터 삭제 (DELETE)

DELETE FROM Students
WHERE StudentID = 1003;

특정 학생 데이터를 삭제합니다.

 

4. 도메인

데이터베이스에서 도메인(domain)은 특정 속성(attribute)이 가질 수 있는 값의 범위 또는 제약 조건을 정의하는 개념입니다. 즉, 속성이 허용하는 데이터의 형식, 범위, 또는 가능한 값들의 집합을 의미합니다. 도메인은 데이터의 무결성을 유지하고, 올바른 데이터만 입력되도록 보장하는 데 사용됩니다.

도메인의 특징:

  • 데이터 유형: 속성의 데이터 타입(예: INT, VARCHAR, DATE 등).
  • 제약 조건: 값의 범위(예: 나이는 0~150), 패턴(예: 이메일 형식), 또는 특정 값 집합(예: 성별은 'M', 'F'만 허용).
  • 논리적 정의: 도메인은 단순히 데이터 타입뿐 아니라 속성이 나타내는 의미와 제약을 포함합니다.

예시:

  • 속성: 나이(Age) → 도메인: 0에서 150 사이의 정수.
  • 속성: 성별(Gender) → 도메인: {'M', 'F', 'Other'}.
  • 속성: 이메일(Email) → 도메인: '@' 포함, 특정 패턴(예: user@domain.com)을 따르는 문자열.
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,                     -- 도메인: 고유한 정수
    Name VARCHAR(50) NOT NULL,                    -- 도메인: 50자 이내의 문자열, NULL 불가
    Age INT CHECK (Age >= 0 AND Age <= 150),      -- 도메인: 0~150 사이의 정수
    Gender CHAR(1) CHECK (Gender IN ('M', 'F', 'O')), -- 도메인: 'M', 'F', 'O'만 허용
    Email VARCHAR(100) CHECK (Email LIKE '%@%.%') -- 도메인: '@'와 '.' 포함 문자열
);



일반적으로 데이터 타입과 제약 조건(CHECK, NOT NULL, ENUM 등)을 통해 도메인을 간접적으로 구현합니다. 
위 코드는 도메인을 반영한 테이블 생성 및 데이터 작업 예시입니다.

 

 

▣ View

뷰는 하나 이상의 테이블(또는 다른 뷰)로부터 파생된 가상 테이블입니다. 실제 데이터를 물리적으로 저장하지 않고, 뷰가 정의될 때 사용된 쿼리(SQL 문)의 결과 집합을 보여줍니다. 뷰는 사용자가 마치 테이블처럼 데이터를 조회할 수 있도록 하지만, 그 데이터는 원본 테이블에서 실시간으로 가져옵니다.

  • 가상 테이블:
    뷰는 물리적으로 데이터를 저장하지 않고, 뷰가 정의된 쿼리(SELECT 문)를 실행하여 결과를 보여주는 논리적인 구조입니다. 마치 '창문'처럼 원본 테이블의 데이터를 보여주는 역할을 합니다.
  • 물리적 저장 없음:
    뷰 자체는 데이터가 아닌 뷰를 생성하는 SQL 쿼리만 데이터베이스에 저장됩니다.
    뷰를 조회할 때마다 해당 쿼리가 실행되어 데이터를 가져옵니다.
  • 데이터 독립성 제공:
    원본 테이블의 구조가 변경되더라도 뷰의 정의를 통해 변경 사항을 숨길 수 있어, 뷰를 사용하는 애플리케이션은 영향을 덜 받습니다.
  • 편의성:
    복잡한 조인이나 조건이 포함된 쿼리를 뷰로 정의해두면, 이후에는 간단하게 뷰를 조회하는 것만으로 원하는 데이터를 얻을 수 있습니다. 이는 개발자의 편의성을 높여줍니다.
  • 보안성:
    특정 사용자에게 필요한 데이터만 뷰로 제공함으로써, 원본 테이블의 모든 정보를 노출하지 않고 데이터에 대한 접근을 제어할 수 있습니다.
    예를 들어, 민감한 정보가 포함된 컬럼은 뷰에 포함시키지 않을 수 있습니다.
  • 제한적인 데이터 조작:
    일반적으로 뷰를 통한 데이터 삽입, 수정, 삭제는 제약이 따르며, 특정 조건(예: 단일 테이블 기반의 뷰, 기본 키 포함 등)을 만족하는 경우에만 가능하거나 아예 불가능할 수도 있습니다.
  • 의존성:
    뷰는 원본 테이블(또는 다른 뷰)에 의존합니다. 원본 테이블이 삭제되면 해당 뷰도 사용할 수 없게 됩니다.
예시: Students 테이블과 Courses 테이블을 조인하여 '학생 이름', '수강 과목', '학점'을 보여주는 StudentCourses 뷰를 생성할 수 있습니다. 
이 뷰는 실제 데이터를 저장하는 것이 아니라, 두 테이블에서 필요한 정보를 조합하여 보여주는 역할을 합니다.

테이블은 데이터의 '실체'이며, 뷰는 그 실체를 특정 목적에 맞게 '재구성하여 보여주는 창'이라고 이해할 수 있습니다.

▣ Table 만들기

▷ PostgreSQL 테이블을 만들 때 다음 사항에 주의하세요:

  • 데이터 타입 일관성:
    테이블 스키마에서 각 컬럼에 적합한 데이터 타입을 지정해야 합니다 (예: SERIAL for ID, boolean for status, timestamp with time zone for created 및 updated). 잘못된 타입 선택은 데이터 무결성을 해칠 수 있습니다.

  • 기본 키 설정: 
    ID와 같이 고유 식별자를 사용한다면, 이를 기본 키(PRIMARY KEY)로 설정하여 중복을 방지하세요.
  • NULL 값 관리:
    컬럼이 NULL 값을 허용할지 여부를 명시적으로 정의하세요.
    예를 들어, ID는 NOT NULL로 설정하는 것이 일반적입니다.
  • 인덱싱:
    자주 검색하거나 정렬하는 컬럼에 인덱스를 추가하여 성능을 최적화하세요.
  • 타임존 고려:
    timestamp with time zone을 사용할 경우, 시간대 차이를 고려한 데이터 처리가 필요합니다.
  • 제약 조건:
    UNIQUE, FOREIGN KEY, CHECK 등 필요한 제약 조건을 추가하여 데이터 무결성을 유지하세요.
  • 테이블 이름 및 컬럼 이름: 
    명확하고 충돌이 없는 이름을 사용하며, 키워드(예: order)는 피하거나 따옴표로 감싸세요.

1. UNIQUE 제약 조건

  1. 설명: 동일한 컬럼 값이 테이블 내에서 중복되지 않도록 보장합니다. 
    위 예시에서는 status 컬럼에 UNIQUE 제약을 추가하여 동일한 TRUE 또는 FALSE 값이 여러 행에 나타나지 않도록 했습니다.
  2. 활용: 고유한 상태 값(예: 특정 상태 코드)을 관리할 때 유용합니다.
  3. 주의: NULL 값은 중복 허용 가능하니, NOT NULL과 함께 사용할 경우 신중히 설계해야 합니다.

2. FOREIGN KEY 제약 조건

  1. 설명: 다른 테이블의 기본 키를 참조하여 데이터 간의 관계를 정의합니다.
    위 예시에서는 category_id가 가상의 category_table의 ID 컬럼을 참조하도록 설정했습니다.
    category_table에 해당 ID가 없으면 삽입이 실패합니다.
  2. 활용: 테이블 간 데이터 무결성을 유지하고, 참조 무결성을 보장할 때 사용됩니다(예: 주문 테이블에서 제품 ID 참조).
  3. 주의:
    참조하는 테이블(category_table)이 먼저 생성되어 있어야 하며, ON DELETE 또는 ON UPDATE 옵션으로 동작을 커스터마이징할 수 있습니다(예: ON DELETE CASCADE).

3. CHECK 제약 조건

  1. 설명: 컬럼 값이 특정 조건을 만족하도록 제한합니다.
    위 예시에서는 created가 updated보다 이전이어야 한다는 조건을 설정했습니다.
    이 조건을 위반하면 데이터 삽입/업데이트가 실패합니다.
  2. 활용: 데이터의 논리적 유효성을 보장하는 데 유용합니다(예: 가격이 음수가 되지 않도록 CHECK (price >= 0)).
  3. 주의: 복잡한 조건은 트리거나 애플리케이션 로직으로 대체하는 경우도 있으니 성능을 고려하세요.

▷ PostgreSQL에서 사용되는 주요 데이터 타입은 다음과 같이 분류됩니다:

1. 숫자 타입 (Numeric Types)

  • 정수:
    • SMALLINT: 2바이트, -32,768 ~ 32,767
    • INTEGER (또는 INT): 4바이트, -2,147,483,648 ~ 2,147,483,647
    • BIGINT: 8바이트, -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
  • 부동 소수점:
    • REAL: 4바이트, 단정밀 부동 소수점
    • DOUBLE PRECISION: 8바이트, 배정밀 부동 소수점
  • 정밀 숫자:
    • NUMERIC(precision, scale) 또는 DECIMAL: 임의 정밀도, 소수점 이하 자릿수 지정 가능
  • 시퀀스:
    • SERIAL: 자동 증가 정수 (SMALLINT, INTEGER, BIGINT 기반)

2. 문자 타입 (Character Types)

  • CHAR(n): 고정 길이 문자열, n자리
  • VARCHAR(n): 가변 길이 문자열, 최대 n자리
  • TEXT: 길이 제한 없는 가변 길이 문자열

3. 날짜/시간 타입 (Date/Time Types)

  • DATE: 날짜 (YYYY-MM-DD)
  • TIME: 시간 (HH:MM:SS)
  • TIMESTAMP: 날짜와 시간 (YYYY-MM-DD HH:MM:SS)
  • TIMESTAMPTZ: 타임존 포함 타임스탬프
  • INTERVAL: 시간 간격

4. 논리 타입 (Boolean Type)

  • BOOLEAN: true, false, 또는 NULL

5. 바이너리 타입 (Binary Types)

  • BYTEA: 바이너리 데이터 (이진 문자열)

6. JSON 타입

  • JSON: 텍스트 기반 JSON 데이터
  • JSONB: 바이너리 형태로 저장된 JSON, 쿼리 최적화

7. 배열 타입 (Array Types)

  • 모든 데이터 타입에 대해 배열 선언 가능 (예: INTEGER[], TEXT[])

8. 기하학적 타입 (Geometric Types)

  • POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE

9. 네트워크 주소 타입 (Network Address Types)

  • INET: IPv4/IPv6 주소
  • CIDR: 네트워크 주소
  • MACADDR: MAC 주소

10. 텍스트 검색 타입

  • TSVECTOR: 텍스트 검색용 데이터
  • TSQUERY: 텍스트 검색 쿼리

11. UUID 타입

  • UUID: 고유 식별자 (128비트)

12. 범위 타입 (Range Types)

  • INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE: 특정 범위 데이터

13. 기타

  • MONEY: 통화 값
  • XML: XML 데이터
  • ENUM: 사용자 정의 열거형 타입
  • DOMAIN: 사용자 정의 데이터 타입

▷ PostgreSQL 테이블 실습 (유튜브 동영상)

 

by korealionkk@gmail.com

 

반응형
저작자표시 비영리 변경금지 (새창열림)