[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate

지난번 포스팅에서는 PostgreSQL, Greenplum DB 에서 Database, Schema, Table 생성/삭제 및 Insert Into 를 사용한 값 입력을 소개(https://rfriend.tistory.com/537)하였습니다. 

이번 포스팅에서는 데이터 조작 언어(DML: Data Manipulation Language) 중에서 데이터 수정(갱신) Update와 삭제 Detele 문에 대해서 소개하겠습니다. 그리고 테이블 전체를 비우는 Truncate 도 제일 뒤에 마저 소개하겠습니다.  (단, 이들 Insert, Update, Delete, Truncate 의 경우 사용자가 권한(Privilege)을 부여받은 상태에서만 가능합니다.)  

RDBMS 에 속하는 PostgreSQL, Greenplum DB와 NoSQL Hive 의 Insert, Update, Delete 별 기능에 대해 아래에 비교 정리를 해보았습니다. (셋 모두 오프 소스입니다.)

PostgreSQL DB 는 작은 규모의 데이터를 관리하는 OLTP (Online Transaction Processing) 성격의 RDBMS (Relational Database Management System) 로서 Insert, Update, Delete 를 모두 지원합니다. 

Hive는 Big Data를 Batch 로 처리, 분석하는데 사용하는 Hadoop 기반의 NoSQL 의 하나로서, Insert 는 지원하지만 Update 와 Delete 는 지원하지 않습니다. (Hive version 0.14 부터 Delete 기능 추가). 

Greenplum DB 는 PostgreSQL 엔진 기반에 Shared nothing 구조의 MPP (Massively Parallel Processing) 아키텍처의 DW, Analytics Platform 으로서, PostgreSQL과 Hive 의 양쪽의 장점은 취하고 단점은 피하는 기능상의 이점을 제공합니다. 

(1) 데이터 수정 (갱신): UPDATE table_name SET column=value WHERE;

UPDATE Syntax

UPDATE table_name  SET column1 = value1, column2 = value2, ..., columnN = valueN  WHERE [condition];

먼저 예제로 사용할 cust_master 테이블을 생성(create)하고, 데이터를 입력(insert into) 해보겠습니다. 

DROP TABLE IF EXISTS cust_master; CREATE TABLE cust_master (     id INT PRIMARY KEY     , first_name VARCHAR(30) NOT NULL     , last_name VARCHAR(30) NOT NULL     , gender TEXT DEFAULT 'UNKNOWN'     , age INT CHECK (age > 0 AND age < 120)     , joindate DATE CHECK (joindate > '2020-01-03') ); -- INSERT INTO VALUES INSERT INTO cust_master (id, first_name, last_name, gender, age, joindate) VALUES  (1, 'kildong', 'hong', 'M', 30, '2020-05-15') , (2, 'younghee', 'kim', 'F', 20, '2020-04-29') , (3, 'chulsu', 'lee', 'M', 40, '2020-06-16'); SELECT * FROM cust_master;

(1-1) Where 조건에 해당하는 행의 특정 칼럼 값 수정하기 

이제, 위의 cust_master 테이블에서 'id'가 '1'인 행의 'age' 칼럼 값을 '25'로 수정(갱신, update) 해보겠습니다.  

-- Update with Where clause UPDATE cust_master SET age = 25 WHERE id = 1; SELECT * FROM cust_master ORDER BY id;

(1-2) 모든 행의 특정 칼럼 값을 동일한 값으로 모두 수정하기

아래처럼 Where 조건절이 없으면 모든 행의 값이 똑같은 값으로 수정되므로 Where 절을 빼먹지 않도록 유의하시기 바랍니다. 

-- Update without Where clause UPDATE cust_master SET age = 50; SELECT * FROM cust_master ORDER BY id;

(1-3) Where 조건을 만족하는 복수개의 칼럼 값을 수정(갱신, Update) 하기

아래 예처럼, 콤마(',')로 수정하고자 하는 칼럼 이름을 구분해서 Update 문을 작성해주면 됩니다. 

-- Update of multiple values using Comma UPDATE cust_master SET age = 40, joindate = '2020-06-20' WHERE id = 3; SELECT * FROM cust_master ORDER BY id;

(2) 데이터 삭제: DELETE FROM table_name WHERE;

DETELE Syntax

DELETE FROM table_name  WHERE [condition];

(2-1) Where 조건을 만족하는 행만 삭제하기

위에서 만들었던 cust_master 테이블에서 Where 조건절을 사용하여 id = 1 인 행 전체를 삭제(DELETE)해보겠습니다. 

-- Delete row with Where clause DELETE FROM cust_master WHERE id = 1; SELECT * FROM cust_master ORDER BY id;

참고로, 테이블 내에 중복된 행이 있는 경우, DELETE 문을 사용해서 중복된 행들 중에서 첫번째 행 한개만 남겨놓고 나머지 중복된 행들은 삭제하는 방법은 https://rfriend.tistory.com/386  를 참고하세요. 

(2-2) 모든 행의 데이터를 삭제하기

만약에 Where 조건절문을 포함하지 않고 DELETE 문을 사용하면 테이블 내 모든 행의 데이터가 삭제되므로 조심하시기 바랍니다. 

-- Delete all rows DELETE FROM cust_master; SELECT * FROM cust_master;

(3) 테이블 비우기 (모든 데이터 삭제): TRUNCATE table_name;

TRUNCATE Syntax

TRUNCATE table_name1, table_name2, ..., table_nameN   [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

위의 (2-2)에서 Where 조건절 없이 DELETE 문을 사용할 경우 테이블 내 모든 행의 데이터가 삭제되었습니다. 

만약에 데이터 크기가 매우 큰 테이블에서 모든 데이터를 빠르게 삭제하고 싶은 경우 DELETE 문보다는 TRUNCATE 함수를 사용할 수 있습니다.  DELETE 와 다르게 TRUNCATE는 table scan 이 없어서 빠르며, VACUUM operation 없이 디스크 공간을 즉시 재생성 해줍니다. 

(TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.)

대신에 TRUNCATE 는 ROLLBACK 정보를 만들지 않고 바로 COMMIT 을 하기 때문에 나중에 ROLLBACK을 할 수는 없으므로, 역시 조심해서 해야 하겠습니다. 

여러개의 테이블을 한꺼번에 모든 데이터를 비우고 싶으면 TRUNCATE table_name1, table_name2, ..., table_nameN 처럼 콤마로 구분하여 테이블 이름을 여러개 써주면 됩니다. 

-- Truncate 1 table TRUNCATE cust_master; -- Truncate multiple tables TRUNCATE cust_master, cust_grade;

TRUNCATE 의 여러 Parameter 값들에 대한 설명은 아래 링크를 참조하세요. 

https://www.postgresql.org/docs/9.5/sql-truncate.html

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)