PostgreSQL CREATE등 테이블 CRUD 문법 정리

CREATE

• 테이블 또는 기타 여러 객체들을 생성하는 명령어 이다.

-- 기본 문법
CREATE TABLE table_name(
  column_name TYPE column_constraint,
  column_name TYPE column_constraint,
  table_constraint table_cnostraint
) INHERITS existing_table_name;

-- 더 단순한 문법
CREATE TABLE table_name(
  column_name TYPE column_constraint,
  column_name TYPE column_constraint,
);

-- 예시: players 테이블 생성, player_id 컬럼은 serial pk
CREATE TABLE players(
  player_id SERIAL PRIMARY KEY,
  age SMALLINT NOT NULL
);

CREATE TABLE account(
    user_id SERIAl PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(250) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
)

CREATE TABLE job(
    job_id SERIAL PRIMARY KEY,
    job_name VARCHAR(200) UNIQUE NOT NULL
);

CREATE TABLE account_job(
    user_id INTEGER REFERENCES account(user_id),
    job_id INTEGER REFERENCES job(job_id),
    hired_date TIMESTAMP
)

INSERT

• 테이블에 데이터를 삽입하는 명령어 이다.

-- 기본 문법
INSERT INTO table (column1, column2, ...)
VALUES
	(value1, value2, ...),
    (value1, value2, ...),...;

• 다른 테이블의 값을 삽입하려면 SELECT ~ FROM ~ WHERE 활용

INSERT INTO table (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

• 예제

INSERT INTO account(username, password, email, created_on)
VALUES ('Jose', 'password', 'jose@mail.com', CURRENT_TIMESTAMP)

INSERT INTO job(job_name)
VALUES ('Astronaut')

INSERT INTO job(job_name)
VALUES ('President')

INSERT INTO account_job(user_id, job_id, hire_date)
VALUES (1, 1, CURRENT_TIMESTAMP)

• 10번 user_id와 job_id가 존재하지 않다면 아래 쿼리는 제약 조건을 위반하여 실행되지 않는다

INSERT INTO account_job(user_id, job_id, hire_date)
VALUES (10, 10, CURRENT_TIMESTAMP)

UPDATE

• 테이블의 데이터를 수정하는 명령어 이다.

-- 기본 문법
UPDATE table
SET column1 = value1,
	column2 = value2, ...
WHERE condition;

• account테이블에서 last_login이 NULL이면 현재시간(CURRENT_TIMESTAMP)으로 수정하는 쿼리

UPDATE account
SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL;

• 모든 last_login컬럼 값을 created_on의 값으로 수정

UPDATE account
SET last_login = created_on;

• 다른 테이블의 값 사용

UPDATE tableA
SET original_col = tableB.new_col
FROM tableB
WHERE tableA.id = tableB.id;

• RETURNING: RETURNING이 없으면 쿼리를 실행한 후에 그냥 쿼리가 실행됐다고만 뜨는데 RETURNING을 사용하면 실행한 쿼리의 결과를 SELECT한 것처럼 보여준다.

UPDATE account
SET last_login = created_on
RETURNING account_id, last_login;

• 예제

UPDATE account
SET last_login = CURRENT_TIMESTAMP;

UPDATE account
SET last_login = created_on;

UPDATE account_job
SET hire_date = account.created_on
FROM account
WHERE account_job.user_id = account.user_id

UPDATE account
SET last_login = CURRENT_TIMESTAMP
RETURNING email, created_on, last_login

DELETE

• 테이블의 데이터를 삭제하는 명령어 이다.
• 레코드 단위로만 삭제할 수 있다.

-- 기본 문법
DELETE FROM table
WHERE row_id = 1

• 다른 테이블에 있는 행 지우기

DELETE FROM tableA
USING tableB
WHERE tableA.id = tableB.id

• 테이블의 모든 행 지우기

DELETE FROM table

• RETURNING사용 가능

DELETE FROM job
WHERE job_name = 'Cowboy'
RETURNING job_id, job_name

ALTER

• 이미 존재하는 테이블의 구조를 변경하는 명령어 이다.
• 컬럼 추가, 삭제, 변경, 기본값 지정, 제약조건 추가 등의 작업을 할 수 있다.

-- 기본 문법
ALTER TABLE table_name action

-- 컬럼 추가
ALTER TABLE table_name
ADD COLUMN new_col TYPE

-- 컬럼 삭제
ALTER TABLE table_name
DROP COLUMN col_name

-- 제약 조건 변경
ALTER TABLE table_name
ALTER COLUMN col_name
ADD CONSTRAINT constraint_name

• 예제

-- 테이블명 변경
ALTER TABLE information
RENAME TO new_info

-- 컬럼명 변경
ALTER TABLE new_info
RENAME COLUMN person TO people

-- 제약조건 변경: null 허용
ALTER TABLE new_info
ALTER COLUMN people DROP NOT NULL

DROP

• 테이블의 컬럼이나 테이블 전체를 삭제하는 명령어 이다.
• 테이블의 컬럼 삭제 시 ALTER와 조합하여 사용한다.
• PostgreSQL에서 DROP을 실행하면 삭제되는 컬럼에 관련된 모든 인덱스와 제약조건 또한 삭제한다.
• 단 뷰, 트리거, 저장 프로시저에 연관된 컬럼은 CASCADE조건이 없으면 삭제되지 않는다.

-- 기본 문법
ALTER TABLE table_name
DROP COLUMN col_name

-- 존재하지 않는 컬럼에 대한 에러 방지
ALTER TABLE table_name
DROP COLUMN IF EXISTS col_name

-- 여러 컬럼 삭제 가능
ALTER TABLE table_name
DROP COLUMN col_one,
DROP COLUMN col_two

-- 테이블 삭제
DROP TABLE table_name;

• 예제

ALTER TABLE new_info
DROP COLUMN people

ALTER TABLE new_info
DROP COLUMN IF EXISTS people

CHECK

• 특정 조건에 맞춘 제약조건을 쓸 수 있게 해주는 명령어 이다.

-- 기본 문법
CHECK (conditions)

CREATE TABLE example(
	ex_id SERIAl PRIMARY KEY,
    age SMALLINT CHECK (age > 21),
    parent_age SMALLINT CHECK(parent_age > age)
);

CREATE TABLE employees(
    emp_id SERIAl PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_nmae VARCHAR(50) NOT NULL,
    birthdate DATE CHECK (birthdate > '1900-01-01'),
    hire_date DATE CHECK (hire_date > birthdate),
    salary INTEGER CHECK (salary > 0)
)

INSERT INTO employees(
    first_name,
    last_name,
    birthdate,
    hire_date,
    salary
)
VALUES(
    'Jose',
    'Portilla',
    '1899-11-03', -- 제약 조건 위반으로 insert 안됨
    '2010-01-01',
    100
)

연습문제

CREATE TABLE students(
    student_id SERIAL PRIMARY KEY, 
    first_name VARCHAR(50) NOT NULL, 
    last_name VARCHAR(50) NOT NULL,
    homeroom_number INTEGER CHECK(homeroom_number > 0), 
    phone VARCHAR(500) UNIQUE NOT NULL,
    email VARCHAR(250) UNIQUE,
    graduation_year INTEGER CHECK(graduation_year > 0)
);

CREATE TABLE teachers(
    teacher_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL, 
    last_name VARCHAR(50) NOT NULL,
    homeroom_number INTEGER CHECK(homeroom_number > 0), 
    department VARCHAR(50),
    phone VARCHAR(500) UNIQUE,
    email VARCHAR(250) UNIQUE
);

참고자료

• https://www.udemy.com/course/best-sql-2022/