[PostgreSQL] Insert 쿼리의 모든 사용법

PostgreSQL의 Insert 쿼리들

1. 기본 INSERT 쿼리

특정 컬럼에 값을 지정하여 데이터 삽입 (기본인거라)

INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

2. INSERT INTO SELECT 쿼리

다른 테이블에서 데이터를 가져와 삽입

INSERT INTO table_name (column1, column2, column3)

SELECT column_a, column_b, column_c FROM another_table

WHERE condition;

 

-- 여기서부터 좀 다른것들이 나오는듯

3. DEFAULT 값을 사용한 INSERT

모든 컬럼이 기본값을 가지는 경우 DEFAULT를 사용할 수 있음:

 

INSERT INTO table_name DEFAULT VALUES;

4. 다중 행 삽입

한 번에 여러 레코드 삽입:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1a, value2a, value3a),
(value1b, value2b, value3b),
(value1c, value2c, value3c);

예시)
INSERT INTO employees (name, age, department)
VALUES ('Alice', 25, 'HR'),
  ('Bob', 28, 'Sales'),
  ('Charlie', 35, 'Marketing');

5. ON CONFLICT (UPSERT)

중복 키가 발생했을 때 대체 작업 수행

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
ON CONFLICT (conflict_column)
DO UPDATE SET
  column2 = excluded.column2,
  column3 = excluded.column3;
  
예시)
INSERT INTO employees (id, name, department)
VALUES (1, 'Jane Doe', 'Engineering')
ON CONFLICT (id)
DO UPDATE SET
  name = excluded.name,
  department = excluded.department;
  
중복 시 아무 작업도 수행하지 않으려면)
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT DO NOTHING;

 

6. RETURNING 사용

삽입 후 자동으로 생성된 값 또는 특정 컬럼 값을 반환

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
RETURNING column1, column2;

예시)
INSERT INTO employees (name, age, department)
VALUES ('Daniel', 32, 'IT')
RETURNING id, name;

7. JSON 데이터 삽입

JSON 데이터를 삽입하려면 ::json 형식으로 변환

INSERT INTO table_name (json_column)
VALUES ('{"key": "value"}'::json);

예시)
INSERT INTO logs (event_data)
VALUES ('{"event": "login", "user_id": 123}'::json);

 

8. COPY FROM 쿼리를 사용한 대량 삽입

CSV 파일 또는 외부 데이터를 한 번에 삽입:

COPY table_name (column1, column2, column3)
FROM '/path/to/file.csv'
WITH (FORMAT csv, HEADER true);

9. 시퀀스 사용

시퀀스에서 기본 키 값을 자동 생성: 오라클과 다른 Sequence사용법 확인할

INSERT INTO table_name (id, column1, column2)
VALUES (nextval('sequence_name'), value1, value2);

예시)
INSERT INTO orders (id, product_name, quantity)
VALUES (nextval('orders_id_seq'), 'Laptop', 2);

 

10. Prepared Statement를 사용한 INSERT

성능을 높이기 위해 준비된 쿼리:

PREPARE insert_statement (text, int, text) AS
INSERT INTO table_name (column1, column2, column3)
VALUES ($1, $2, $3);

EXECUTE insert_statement('Alice', 30, 'Engineering');

 

오라클은 바인드변수를 활용해서 넣기도 하던데, PostgreSQL의 활용은?

PostgreSQL에서도 바인드 변수를 사용할 수 있으며, 이를 통해 SQL 구문과 데이터 값을 분리하고 효율적이고 안전하게 데이터를 처리할 수 있다.

PostgreSQL은 주로 준비된 쿼리 (Prepared Statements)와 플레이스홀더를 사용하여 바인드 변수와 비슷한 기능을 제공한다.

 

1. Oracle 바인드 변수 예제

Oracle에서는 바인드 변수를 :변수명 형태로 사용합니다.

SQL*Plus 또는 PL/SQL 블록:

VARIABLE emp_id NUMBER
EXEC :emp_id := 1001;

SELECT first_name, last_name
FROM employees
WHERE employee_id = :emp_id;

바인드 변수를 사용한 INSERT:

VARIABLE name VARCHAR2(100)
VARIABLE dept VARCHAR2(50)

EXEC :name := 'John Doe';
EXEC :dept := 'Engineering';

INSERT INTO employees (name, department)
VALUES (:name, :dept);

2. PostgreSQL 바인드 변수와 비교: Prepared Statements

PostgreSQL에서는 바인드 변수와 유사한 기능을 Prepared Statements로 구현하며, 플레이스홀더($1, $2, ...)를 사용합니다.

PostgreSQL 바인드 변수 스타일 SELECT

PREPARE employee_query (int) AS
SELECT first_name, last_name
FROM employees
WHERE employee_id = $1;

EXECUTE employee_query(1001);

바인드 변수를 사용한 INSERT:

PREPARE insert_employee (text, text) AS
INSERT INTO employees (name, department)
VALUES ($1, $2);

EXECUTE insert_employee('John Doe', 'Engineering');

 

3. 주요 차이점

특징	Oracle	PostgreSQL
바인드 변수 문법	:변수명	$1, $2, ...
바인드 변수 정의	VARIABLE 또는 PL/SQL에서 정의	Prepared Statements 사용
실행 방식	EXEC 또는 자동적으로 매핑됨	EXECUTE prepared_statement(값)
보안 및 효율성	SQL Injection 방지, 재사용으로 성능 향상	SQL Injection 방지, 캐싱으로 성능 향상