🐘 PostgreSQL 기본 Ⅱ - 데이터베이스와 테이블 생성

📙 『실용 SQL』을 읽고 정리한 글입니다.

개요

• SQL은 단순히 데이터로부터 지식을 추출하는 작업이 아니라, 데이터가 담고 있는 구조를 정의하여 우리가 데이터 안의 관계를 정의할 수 있도록 한다.
• 이러한 구조 중 으뜸은 테이블이다.
• 테이블은 데이터를 담고 있는 행과 열로 이루어진 표이다.
• 각 행들은 열들의 모음이고, 각 열은 대개 숫자, 문자, 그리고 날짜로 이루어진 특정 타입의 데이터를 담고 있다.
• 우리들은 테이블의 구조를 정의하고, 각 테이블이 데이터베이스 안의 다른 테이블과 어떤 연관이 있는지 확인하고, 테이블에서 데이터를 추출 또는 쿼리하기 위해 SQL을 사용할 것이다.
• 이 장에서는 다음 작업을 수행해볼 것이다.

1. 데이터베이스 생성
2. 생성한 데이터베이스에 테이블 생성
3. 생성한 테이블에 행을 여러 개 삽입
4. 이후 테이블 데이터 확인인

테이블 이해하기

• 데이터베이스의 데이터를 이해하려면 우선 테이블을 알아야 한다.
• 우선적으로 데이터베이스에 포함된 테이블을 살펴보고, 테이블의 이름과 열 구조를 보고 단서를 찾는다.
• 다음으로 데이터베이스에 테이블이 몇 개나 있는지 살펴보고, 분석할 테이블이 많다는 것은 곧 분석할 데이터가 많다는 것을 뜻하는 동시에, 각 테이블 사이의 관계도 살펴봐야 한다는 것을 뜻한다.

데이터베이스 생성

CREATE DATABASE analysis;  -- 데이터베이스 생성
SELECT current_database(); -- 현재 사용 중 데이터베이스 조회

• 기본 데이터베이스 대신 예제에 사용하는 새로운 데이터베이스를 만들어 특정 주제 또는 응용 프로그램과 관련된 개체를 함께 구성할 수 있는데, 이렇게 할 경우 서로 관련이 없는 데이터베이스에 테이블이 쌓이는 것을 방지하고, 해당 데이터가 애플리케이션을 구동하는 데 사용될 경우 관련 정보만 포함하여 관리할 수 있다.
• 서버에 analysis라는 이름의 데이터베이스를 생성하는 명령문이다.
• 명령문은 세미콜론(;)으로 마치며, 이는 표준 ANSI SQL에 해당한다.
• 세미콜론을 생략해도 되는 경우가 있지만, 여러 명령문을 실행할 때는 세미콜론을 생략해서는 안되는 경우도 존재하므로 언제나 세미콜론으로 명령문을 마무리하는 습관을 들이는 것이 좋다.
• 데이터베이스 생성 후 목록에서 안 보이는 경우도 있는데, DBeaver의 문제이므로 다음 과정을 거치면 된다.
• 데이터베이스 우클릭 - Edit Connection - Main - Show all databases 체크

생성한 데이터베이스에 테이블 생성

CREATE TABLE teachers (
	id bigserial,
	first_name varchar(25),
	last_name varchar(50),
	school varchar(50),
	hire_date date,
	salary numeric
);

• 이 테이블 정의는 전혀 포괄적이지 않다.
• 반드시 채워져야 하는 값이 데이터를 담고 있는지, 또는 무심코 중복된 값을 삽입하지는 않았는지 등을 확인하는 제약 조건이 누락되어 있다.
• 제약 조건은 8장에서 다룰 예정이다.
• 테이블을 만들 때 각 열의 이름과 데이터 타입을 지정해주는데, 데이터 타입은 데이터의 무결성을 확인하는 방법이 된다.
• 가령 날짜 데이터를 입력 받는 data 열이 있다고 할 때 해당 열에 날짜 데이터가 아닌 "peach" 같은 문자열을 입력하면 오류가 발생한다.

생성한 테이블에 행을 여러 개 삽입

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
	   ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
	   ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
	   ('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
	   ('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
	   ('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

• 테이블에 데이터를 추가하는 가장 쉬운 방법은 텍스트 파일에서 데이터를 추출해 오거나 다른 데이터베이스에서 추출하여 테이블에 직접 넣는 것이다.
• 여기에서 PostgreSQL구문은 ANSI SQL 표준을 따른다.
• 문자나 날짜는 작은 따옴표(')로 감싸져야 하며, 정수와 소수를 포함한 숫자는 그렇지 않다.
• id의 경우 데이터 타입을 bigserial로 정의했기 때문에 별도로 데이터를 넣을 필요 없이 PostgreSQL에 데이터를 하나씩 삽입할 때마다 id 열을 증가하는 정수로 채우게 된다.

Updated Rows	6
Execute time	0.018s
Start time	Wed Aug 13 16:47:47 KST 2025
Finish time	Wed Aug 13 16:47:47 KST 2025
Query	INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
	VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
		   ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
		   ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
		   ('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
		   ('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
		   ('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500)

• 명령문을 통해 6개의 행이 업데이트 되었고, 0.018s의 실행 시간이 소요되었음을 알 수 있다.

코드가 잘못되었을 때 도움 구하기

SQL Error [42601]: ERROR: syntax error at or near "("
  Position: 150

• 가령 행을 여러 개 삽입하는 명령문에서 쉼표 하나를 빼 먹는다면, 위와 같이 에러 메시지를 제공한다.
• 해당 힌트를 참고하여 직접 수정하거나, [42601]: ERROR: syntax error at or near 등의 메시지를 인터넷에 검색하면 된다.

SQL을 가독성 있게 표현하기

• SQL을 실행하는 데에는 특별한 포맷이 필요하지 않기 때문에 대소문자를 구분하지 않거나, 들여쓰기를 마음대로 해도 실행은 된다.
• 하지만 그렇게 마구잡이로 작성하면 추후 누군가가 해당 코드로 작업을 할 때 곤란한 상황을 맞이하게 된다.
• 아래 코딩 컨벤션을 참고하여 작성하도록 하자.

1. SELECT 같은 SQL 키워드는 모두 대문자로 작성하자.
   • 몇몇 SQL 코더들은 데이터 타입까지 모두 대문자로 작성하는 경우도 있다.
   • 이는 팀 컨벤션에 맞추면 된다.
2. 카멜 케이스를 지양하라.
   • 그 대신 테이블이나 열과 같은 개체 이름에는 소문자와 언더바(_)를 사용하자.
3. 절과 코드 블록은 두 칸 또는 네 칸을 들여 써서 가독형을 높이자.
   • 몇몇 SQL 코더들은 스페이스 대신 탭을 선호하기도 한다.
   • 이 또한 팀 컨벤션에 맞추면 된다.