[ 살펴보기 ] PostgreSQL - Data Manipulation Language

이전 포스트에서 살펴보았듯이 Database와 Table을 생성, 수정, 삭제와 같은 작업은 위해 Data Definition Lagnauge를 통해 수행하지만 Table의 데이터 생성, 수정, 삭제와 같은 작업은 Data Manipulation Language ( DML )을 통해 수행된다.

pdAdmin과 같은 Graphic Interface를 제공하는 client를 사용해도 무방하지만 해당 포스트에선 psql를 통해 명령어를 살펴본다.

Postgres 15 버전 이후부터 일반 유저는 권한을 부여받지 않으면 public schema의 database에서 DML 작업을 수행할 수 없다는 것에 주의하자.

데이터 추가

테이블에 새로운 데이터를 추가하기 위해선 INSERT statement를 사용한다. 위에서 생성한 members table을 기준으로 테스트를 진행해보자.

INSERT INTO members (name, email) values ('test name', 'test email');

위의 예제에서 볼 수 있듯이 table을 생성할 때 id field에 GENERATED ALWAYS AS IDENTITY를 적용해 주었기 때문에 새로운 데이터 row를 추가할 때 id field 데이터를 별도로 추가하지 않아도 id field의 값을 자동으로 할당이 된다.

한번에 여러 data row을 추가하고자 한다면 다음과 같이 할 수 있다.

INSERT INTO members (name, email)
VALUES ('test name3', 'test email3'),
('test name4', 'test email4');

만약 구조가 동일한 새로운 table을 만들고 기존의 table의 데이터를 전부 새로운 table로 추가하고 싶으면 다음과 같이 할 수 있다.

INSERT INTO temp_members SELECT * FROM members;

위의 예제는 members의 table 데이터를 모두 temp_members table에 추가하는 예제다. 하지만 우리는 위의 예제에서 GENERATED ALWAYS AS IDENTITY를 통해 id field가 sql server에 의해 자동으로 생성되게 설정해놓았으므로 위의 코드는 관련 오류를 반환할 수 있다. 그럴 땐 다음과 같이 override를 통해 기존 table의 id까지 모두 새로운 table의 id로 복사해올 수 있다.

INSERT INTO temp_members OVERRIDING SYSTEM VALUE
SELECT * FROM members;

만약 기존 테이블에서 데이터를 복사해 올 때 기존 테이블의 id를 그대로 override하는 것이 아니라 id를 제외한 데이터는 그대로 복사하되 id는 sql server가 자동으로 생성하게 하고 싶다면 다음과 같이 처리할 수 있다.

INSERT INTO temp_members (name, email)
SELECT name, email
FROM members;

데이터 업데이트

Table 데이터 업데이트는 update statement를 통해서 할 수 있다.다음은 id가 2인 data row의 name을 jake로 변경하는 예제 코드다.

UPDATE members SET name = 'jake' WHERE id = 2;

다음 table의 데이터를 기준으로 현재 table의 데이터를 업데이트 할 수도 있다. 다음 두 테이블이 존재한다고 가정해보자.

/* 테이블 이름 */
library_a 

/* 테이블 데이터 */
id   name     category     available
1    'book1'  'category1'  t
2    'book2'  'category2'  t
3    'book3'  'category3'  f
4    'book4'  'category3'  f

/* 테이블 이름 */
library_b 

/* 테이블 데이터 */
id   name     category     available
1    'book1'  'category1'  t
2    'book2'  'category2'  t
3    'book3'  'category3'  t
5    'book5'  'category3'  t

그리고 library_b table의 available data를 library_a table의 available data를 기반으로 업데이트 하고자 할 때는 다음과 같이 update를 수행할 수 있다.

UPDATE library_b SET available = library_a.available 
FROM library_a WHERE library_b.id = library_a.id;

위의 sql statement는 id data가 일치하는 data에 한하여 library_b table의 available 데이터를 library_a table의 available 데이터로 업데이트 한다.

위의 방법 외에 다음과 같이 merge를 사용하여 업데이트 할 수도 있다. 아래의 sql statement의 결과는 위의 예제와 동일하다.

MERGE INTO library_b 
USING library_a ON library_b.id = library_a.id 
WHEN MATCHED THEN UPDATE SET available = library_a.available;

데이터 삭제하기

Table 데이터 삭제는 delete statement를 통해서 할 수 있다. 다음은 id가 2인 data row를 삭제하는 예제 코드다.

DELETE FROM members WHERE id = 2;

만약 WHERE 조건 없이 DELETE만 수행하면 해당 table의 모든 데이터가 삭제 되므로 주의해야한다. 아래는 members table의 모든 데이터를 삭제하는 예제 코드다.

DELETE FROM members;

DELTE statement 뿐만 아니라 TRUCATE를 통해 테이블 전체 데이터를 삭제할 수 있다.

TRUNCATE TABLE members;

데이터 조회하기

특정 table의 데이터를 조회할 때는 Select statement를 사용한다. 아래 예제는 members table의 모든 데이터를 조회하는 예제다.

SELECT * FROM members;

members table에서 특정 field만 조회하고 싶다면 조회하고 싶은 field을 선언해준다. 아래의 예제는 members 테이블에서 name field만 조회한다.

SELECT name FROM members;

만약 member 테이블에서 특정 이름을 가진 data row를 찾고 싶으면 다음과 같이 WHERE 조건을 통해 조회할 수 있다.

SELECT * from FROM members WHERE name = 'jake';

다수의 값을 기준으로 OR 조회를 적용하고자 한다면 IN operator를 사용할 수 있다. 다음은 city가 seoul이거나 busan인 모든 member 데이터를 조회한다.

SELECT * FROM members IN ('seoul', 'busan');

반면에 city가 seoul이나 busan인 데이터를 제외한 모든 member를 조회하고 싶다면 다음과 같이 NOT IN을 통해 조회할 수 있다.

SELECT * FROM members NOT IN ('seoul','busan');

만약 특정 field가 null인 데이터를 조회하고 싶다면 다음과 같이 조회할 수 있다.

SELECT * FROM members WHERE name IS NULL;

만약 table 조회 결과를 특정 field 기준으로 순서대로 정렬하고 싶다면 order by statement를 사용할 수 있다. 예를 들어 다음은 email field를 기준으로 조회 결과를 정렬하는 예제 코드다.

SELECT * FROM members ORDER BY email;

ORDER BY statement로 정렬할 때 오름차순( ASC )으로 정렬할 것인지 내림차순( DESC )으로 정렬할 것인지 추가로 설정할 수 있다. 관련해서 아무런 설정이 없으면 default로 오름차순 정렬이 적용된다.

SELECT * FROM members ORDER BY email ACS;
// email field를 기준으로 오름차순 정렬

SELECT * FROM members ORDER BY email DESC;
// email field를 기준으로 내림차순 정렬

만약 특정 field를 기준으로 정렬하되 해당 field의 값이 NULL인 row의 순서를 실제 값이 있는 row보다 뒤로 보내어 정렬하고 싶다면 다음과 같이 정렬할 수 있다.

SELECT * FROM members ORDER BY email NULLS LAST;

반대로 특정 field를 기준으로 정렬하되 값이 NULL인 row를 실제 값이 있는 row보다 앞으로 당기고 싶다면 다음과 같이 정렬한다.

SELECT * FROM members ORDER BY email NULLS FIRST;

만약 NULLS LAST나 NULLS FIRST와 같이 NULLS에 대한 정렬이 명시적으로 선언되어 있지 않았을 때 ORDER BY ASC는 NULLS LAST가 default로 적용되고 ORDER BY DESC는 NULLS FIRST가 default로 적용된다.

즉, 다음 select statement에선 email field가 null인 row가 정렬된 데이터의 상단에 온다.

SELECT * FROM members ORDER BY email DESC;

반면 아래 select statement에선 email field가 null인 row가 정렬된 데이터의 하단에 온다.

SELECT * FROM members ORDER BY email ASC;

만약 table에 city라는 field가 있고 table이 관리하는 데이터 중 어떤 city가 있는지 조회하기 위해 SELECT city from members statement를 통해 city field를 조회해 볼 수 있을 것이다. 하지만 member table에 seoul이라는 값을 가진 데이터가 여러개 있다면 위의 statement는 중복된 값을 결과 값에 포함하게 된다. 만약 데이터의 결과 중에 중복된 값을 없애고 싶으면 distinct statement를 사용할 수 있다.

SELECT DISTINCT city from members;

Like을 통한 데이터 조회

WHERE clause를 통해 특정 조건을 적용한 조회를 수행할 때 특정 문자열이 포함된 데이터를 조회하고자 한다면 Like clause를 추가하여 조회할 수 있다. 예를 들어 이름이 Jack으로 시작하는 member를 찾고자 한다면 다음과 같이 조회를 수행한다.

SELECT * FROM members WHERE name LIKE 'Jack%';

반대로 이름이 Jack으로 끝나는 member를 찾고 싶다면 다음과 같이 조회를 수행한다.

SELECT * FROM members WHERE name LIKE '%Jack';

만약 Jack이라는 string이 firstname이나 lastname이 아닌 middlename이라면 위의 SELECT statement는 아무런 데이터도 조회하지 못할 수 있다. 만약 name column에 위치와 상관없이 Jack이라는 string이 포함되어 있는 데이터를 조회하고 싶다면 다음과 같이 %기호를 조회하고자 하는 search string 앞, 뒤에 모두 추가해준다.

SELECT * FROM members WHERE name LIKE '%Jack%';

LIKE cluase는 대,소문자를 구분한다는 것에 주의하자. 아래처럼 소문자로 조회를 수행하면 Jack과 같이 대문자로 시작하는 데이터는 조회 결과에 포함되지 않는다.

SELECT * FROM members WHERE name LIKE '%jack%';

만약 대,소문자를 구분하지 않고 조회를 수행하고 싶으면 다음과 같이 ILIKEclause를 통해 조회할 수 있다.

SELECT * FROM members WHERE name ILIKE '%jack%';

위의 예제를 실행하면 소문자로만 이루어진 jack string과, 대문자로 시작하는 Jack string이 포함된 모든 데이터를 조회할 수 있다.

조회시 Limit과 Offset 설정

Select을 통해 table의 데이터를 조회할 시 offset에 설정한 숫자 이후의 data row부터 조회를 하고 limit은 조회할 data의 row 숫자를 제한한다.

예를 들어 다음 statement는 data row 중 세 번째 row부터 조회 결과에 넣는다. ( offset 2이므로 두 번째 이후의 row인 세 번째 row부터 조회에 포함 )

SELECT * FROM members OFFSET 2;

반면 다음의 statement는 전체 data row중 다섯 번째 data row까지만 조회한다.

SELECT * FROM members LIMIT 5;

Pagination 기능을 구현할 때 OFFSET과 LIMIT을 함께 사용할 수 있다. 다음은 11번째 data row부터 시작해서 10개 까지만 조회하는 예제다.

SELECT * FROM members OFFSET 10 LIMIT 10;

서브쿼리

서브쿼리란 SQL 쿼리에 포함되어 있는 또 다른 쿼리를 뜻한다. 만약 다음 두 개의 테이블이 존재한다고 가정해보자.

( books table )
id int
name text
categoryId int

( category table )
id int
title text

그리고 현재 category 테이블에 존재하는 데이터가 아래와 같다고 가정해보자.

id  title 
1   typescript
2   sql
3   aws

위의 두 table을 기준으로 서브쿼리를 사용해 category 이름이 typescript인 모든 책 정보를 조회하는 방법은 아래와 같다.

SELECT * FROM books WHERE categoryId = ( SELECT id FROM category where title = 'typescript' );

위의 예제와 같이 ( SELECT id FROM category where title = ‘typescript’ )라는 서브쿼리를 통해 필요한 값을 우선 조회하고 서브쿼리로 조회한 데이터를 기준으로 메인 쿼리를 통해 최종 조회 작업을 수행한다.

만약 category가 typescript인 데이터를 제외한 모든 book을 조회하고자 한다면 다음과 같이 not in을 사용할 수도 있다.

SELECT * FROM books WHERE categoryId NOT IN ( SELECT id FROM category where title = 'typescript' );