PostgreSQL 9.5에서 ON CONFLICT 가 도입된 후 Upsert (Insert or Update) 문을 쓸수 있게 되었지만、복수행을 bulk로 보내는건 불가했다.
ON CONFLICT 를 사용하지 않아도、CTE 로 로직을 만들어 Bulk Upsert 와 같은 결과를 내는 것이 가능하다.
WITH-- write the new values
n(ip,visits,clicks) AS (
VALUES ('192.168.1.1',2,12),
('192.168.1.2',6,18),
('192.168.1.3',3,4)
),
-- update existing rowsupsertAS (
UPDATE page_views o
SET visits=n.visits, clicks=n.clicks
FROM n WHERE o.ip = n.ip
RETURNING o.ip
)
-- insert missing rowsINSERTINTO page_views (ip,visits,clicks)
SELECT n.ip, n.visits, n.clicks FROM n
WHERE n.ip NOTIN (
SELECT ip FROMupsert
)
[위 sql 출처](Faster data updates with CartoDB — CARTO Blog)
주의점
select문과 update, insert 문과의 차이점은 select문은 복수행을 한번에 취득한다는 점이고, update, insert 문은 row by row로 결과를 취득한다는 것이다. 때문에 위의 UPDATE문에서 n테이블을 사용했을 때, 마치 for loop 도는 것과 같은 액션이 가능한 것
본 포스트는 MySQL, MongoDB 등 다른 DB를 사용해 본 사람들을 대상으로 작성되었다. 왜냐면 필자가 써본 DB라고 해봤자 MySQL, MongoDB, SQLite(이건 SQL이라고 하기도 좀...) NoSQL은 Hive, HBase 등등.. 이기 때문이다. 이정도면 기본적으로 사용하는 것에는 문제가 없겠지 하는 정도로 postgreSQL을 정리해 보려고 한다. 필자도 익숙하지 않은 DB라서 삽질을 했으니 여러분은 안그러길 기원하며...
1. PostgreSQL
PostgreSQL은 한국어로 '포스트그레스큐엘' 이라고 읽는다. 이 이름도 유래가 있는 듯 하다. Postgre와 SQL을 나눠서 생각해보면 SQL을 사용할 수 있는 관계형 데이터베이스(RDBMS) 이라는 것을 암시하고 있는 듯 하다. Postgre 라는 것도 Post + Ingres의 합성어같다. Ingres DB의 후속작이라는 느낌이 있다.
즉, PostgreSQL는 MySQL, MariaDB와 비슷한 관계형 데이터베이스이다. 그러나 사용법은 꽤 상이하므로 따로 익혀두는 것이 좋다.
2. PostgreSQL 설치 방법
Ubuntu 14.04 에서 아래를 입력해 설치한다. 이 글을 작성하는 시점에서 설치되는 버전은 9.3.14 이다.
# apt-get install postgresql postgresql-contrib
MySQL Workbench 처럼 PG Admin 이라고 하는 PostgreSQL 전용 Web Workbench가 있는 듯 한데, 버그가 많은 모양이다. 굳이 다루지 않기로 했다.
3. CLI 사용하기
설치가 완료됬으면 postgreSQL의 CLI를 사용해 볼 차례다.
psql이라는 명령어로 postgreSQL의 CLI를 사용할 수 있다. 그러나 psql 명령어를 사용할 때, 데이터베이스 내의 사용자를 명시해야 한다. 그냥 root 상태에서 psql을 입력하면 아래와 같은 메시지를 뿜는다.
root@ip-172-31-5-35:~# psql
psql: FATAL: role "root" does not exist
postgreSQL은 root 사용자를 보안상의 이유로 좋아하지 않는 듯 하다. postgres 사용자로 전환해야만 psql 명령어를 사용할 수 있다.
root@ip-172-31-5-35:~#su - postgres
postgres@ip-172-31-5-35:~$psql
psql (9.3.14)
Type "help" for help.
postgres=#
postgreSQL의 CLI로 접속했다. MySQL, MongoDB, HBase, Hive에서 했던 것처럼 똑같이 DB를 제어하면 된다.
4. postgreSQL의 구조
postgreSQL은 우리가 알던 DB와 조금 다른 구조가 있다. 중간에 스키마라고 하는 계층이 존재한다.
MySQL과 비교해보면 이해가 쉽다. 최상위 계층에 Database 라고 하는 레이어가 있다. 이는 우리가 흔히 아는 데이터베이스이다. create database .... 명령어로 생성하던 것이다. 이 데이터베이스는물리적으로 데이터를 구분해 놓은 것이다.
스키마는 데이터베이스 아래에 위치한 계층이다. 데이터베이스를 생성하면 기본적으로public 스키마가 존재한다. 스키마 레벨은 데이터베이스 내에서논리적으로 구분된 계층이다. 그리고 이 아래에 테이블들이 생성된다.
정리하면MySQL에서 쓰던 데이터베이스의 개념은 PostgreSQL의 스키마와 유사하다.1그러나 조금 다른 점은, PostgreSQL의 데이터베이스는 데이터의 물리적 집합을 정의하기 때문에 데이터베이스 내의 스키마들은 조인(JOIN) 이 가능하다는 점이다. 예를 들어 위 그림에서 public 스키마의 mytable은 myscheme의 mytable3과 조인이 가능하다(고 한다).
그럼 이제 스키마가 왜 있냐.. 라는 의문이 들 법 하다. 스키마를 사용하면 1. 여러 사용자가 서로 충돌 없이 하나의 데이터베이스를 사용하는 것이 가능하며 2. 데이터베이스를 여러 개의 논리적인 그룹으로 만들어 더욱 관리하기 쉽게 하고 3. Third-Party 애플리케이션이 서로 충돌할 일 없게 다른 스키마를 사용하도록 한다고 한다.2 또는 다른 사람 소유의 데이터베이스에 테이블을 생성할 수 있다 라는 이유라고도 하는데..3 확실하지는 않다.
이와 별도로, 데이터가 저장될 공간을 뜻하는 tablespace 라는 것이 있지만 아직은 이 단계까지 자세히 보지 않기로 했다. 각 데이터베이스는 tablespace라는 단위에 저장될 수 있는데, tablespace는 실제로 파일시스템에서 저장되는 위치를 의미한다고 한다.
5. postgreSQL 사용하기
(1) 데이터베이스 목록 확인하기
현재 데이터베이스의 목록을 확인하는 명령어는 \l (역슬래시와 소문자 L) 이다. 존재하는 데이터베이스를 확인해보자.
3개의 데이터베이스가 존재한다. postgres, template0, template1 이다. 우리는 지금 postgres 라는 데이터베이스에 접속해 있는데, 이는 쉘에서 확인할 수 있는postgres=# 에서 알 수 있다.
(2) 사용하는 데이터베이스 변경하기
사용하는 데이터베이스를 다른 데이터베이스로 변경해보자. 아래의 명령어를 입력한다. 이번에는 \c 이다.
정상적으로 데이터베이스가 변경되었으며, 쉘의 template1=# 에서 이를 확인할 수 있다.
(3) 데이터베이스 생성하기
새로운 데이터베이스를 생성하고 이 데이터베이스로 변경한다. 데이터베이스 생성은 MySQL과 동일하다.create database (DB명)이다.
(4) 유저 생성하기 권한주기
CREATEDATABASE yourdbname;
CREATEUSER youruser WITH ENCRYPTED PASSWORD'yourpass';
GRANTALLPRIVILEGESONDATABASE yourdbname TO youruser;
(5) 스키마 목록 확인, 생성하기
\dn 을 입력해 스키마 목록을 확인할 수 있다. 데이터베이스를 생성하면 기본적으로 스키마는 public 하나밖에 없다.
create SCHEMA (스키마 명) 을 입력해 새로운 스키마를 생성한다. 스키마 명, 디비 명, 테이블 명은 모두 대소문자를 구별하지 않는 듯 하다. Owner는 스키마의 소유자를 뜻하는데,create SHCEMA (스키마 명) authorization (사용자 명)으로 설정할 수 있다.
(6) 테이블 목록 확인, 생성하기
테이블을 생성하는 명령어는 MySQL과 동일하다.create table (테이블 명) (test varchar(20) ... )이런 형식이다. 조금 다른 점은, 테이블을 생성할 때, 테이블이 속하게 될 스키마를 지정할 수 있다는 것이다. 스키마를 따로 지정하지 않으면 public 스키마에 테이블이 생성된다.
아래는 create tablealiceschema.alicetable(id varchar(20) primary key, pw varchar(20)); 으로 테이블을 생성했다. aliceschema 라는 스키마에 테이블을 생성한 경우이다.
CRUD는 모두 기존의 SQL과 동일하므로 insert 하나만 설명한다. 딱히 특별할 것이 없기 때문이다.
insert into (테이블 명) values(....)를 쓰는데, 한 가지 다른 점이 있다면, 스키마를 지정하지 않으면 search_path의 스키마를 지정한다는 점이다. 따라서 특정 스키마의 테이블을 쓰고 싶다면 insert into(스키마 명).(테이블 명)... 처럼 쓴다. 아래는 aliceschema의 alicetable에 데이터를 넣는 예시이댜.