PostgreSQL에서 Bulk Upsert

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 rows
upsert AS (
  UPDATE page_views o
  SET visits=n.visits, clicks=n.clicks
  FROM n WHERE o.ip = n.ip
  RETURNING o.ip
)
-- insert missing rows
INSERT INTO page_views (ip,visits,clicks)
SELECT n.ip, n.visits, n.clicks FROM n
WHERE n.ip NOT IN (
  SELECT ip FROM upsert
)

[위 sql 출처](Faster data updates with CartoDB — CARTO Blog)

주의점

select문과 update, insert 문과의 차이점은 select문은 복수행을 한번에 취득한다는 점이고, update, insert 문은 row by row로 결과를 취득한다는 것이다. 때문에 위의 UPDATE문에서 n테이블을 사용했을 때, 마치 for loop 도는 것과 같은 액션이 가능한 것

PostgreSQLのデータ型とその挙動のまとめ(数値型)

SQLPostgreSQLDBpostgres

Postgresにテーブルを作成する際に、Postgresのデータ型を見てみると、数値型だけで10個もある。。どうやって使い分けたら良いのかわからなかったので、調べてみた。

この表は、PostgreSQL公式ページに記載されている内容。

数値型の大まかな区分け

Postgresの数値型には、①整数型、②小数点型、③自動採番型の3つがある。

  1. 整数型
    • smallint
    • integer
    • bigint
  2. 浮動小数点データ型
    • decimal
    • numeric
    • real
    • double precision
  3. 自動採番型
    • smallserial
    • serial
    • bigserial

整数型

整数型の特徴は、整数しか格納できないこと。小数を含んだ値を入れようとするとエラーになる。この3つの型の違いは、格納できる数値の最大桁数。

型名最小桁

smallint -32768 to +32767
integer -2147483648 to +2147483647
bigint -9223372036854775808 to +9223372036854775807

最大桁数が多くなれば、それだけハードディスクの容量を消費することになる。公式ページによると、整数型の中ではintegerが一番バランスの取れた型となっているため、整数を扱う場合、特に理由のない場合はinteger型を利用する事との記述があった。

具体的にはHDDのコストが高く、容量を出来る限り節約したい場合のみsmallintを利用し、integerの桁数で足りない値を格納する可能性がある場合はbigintを利用すると言った具合。

浮動小数点データ型

浮動小数点データ型の特徴は、小数を格納できること。型によって桁数に制限があるものとないものがある。桁数に制限がある型を利用しているフィールドにそれ以上の桁数を持つ値が入れられた場合、自動的に計算し、値を切り詰めて格納する。
例えば、real型のフィールドに12.34567という値が入った場合、格納後の値は12.3457となる。(最後の桁を四捨五入)

型名最小桁

real 6桁までの整数及び小数
double precision 15桁までの整数及び小数
numeric 最大 131072桁の整数部と16383桁の小数部
decimal 最大 131072桁の整数部と16383桁の小数部

numericとdecimalの違い

Postgresでは、これらの値の違いはない。pgadminでは、decimal型のフィールドが選択できないようになっている(リストに表示されない)。 sqlではdecimal型を指定することはできるが、作成後に型を確認するとnumericに変換される。

自動採番型

この型は、値を入力しなくても自動的にシーケンステーブルから取得した値が登録される型となる。
serial型を指定すると、テーブル名シーケンスの値名seqという名前のシーケンスオブジェクトが作成され、関連付けられる。種類によって、最大値が異なる。

型名最小桁

real 6桁までの整数及び小数
double precision 15桁までの整数及び小数
numeric 最大 131072桁の整数部と16383桁の小数部
decimal 最大 131072桁の整数部と16383桁の小数部

シーケンスが最大値に達した場合の挙動について

最大値に達した場合の挙動は、DB作成時に選択ができるようになっている。シーケンスオブジェクトの定義をプロパティから開くと、限界リセットというチェックボックスがある。ここがONになっていると限界値に達したときに一旦リセットされる。選択した型よりも限界値が大きくなっていたりすると、値がリセットされずにエラーが出続けてしまうので注意。


※キー項目などで限界値リセットすると、当然古い値との重複が避けられない。古いものを順に削除しているログなどの情報以外は、この選択は取れない可能性が高い。

まとめ

数値を格納する型の数だけを見てもこれだけの数の型があると、どれを使うか迷ってしまいますが、HDDの価格や記録できる領域が劇的に大きくなった昨今では、real型やsmallint型などの精度を犠牲にしてでも利用領域を小さくするような型を利用するシーンは少なくなってきている様に思います。整数を扱うフィールドならinteger型を、小数を扱うフィールドならnumeric型を選択するようにしておけば、間違いがないように思います。

'DataBase > postgreSql' 카테고리의 다른 글

PostgreSQL에서 Bulk Upsert  (0) 2020.02.27
PostgreSQL 설치 및 사용 방법 정리 (리눅스 기준)  (0) 2019.05.21

https://m.blog.naver.com/PostView.nhn?blogId=alice_k106&logNo=220847310053&proxyReferer=https%3A%2F%2Fwww.google.com%2F

본 포스트는 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) 유저 생성하기 권한주기

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE 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 라는 스키마에 테이블을 생성한 경우이다.

생성된 테이블들은\dt명령어로 확인할 수 있다.

?!? 테이블이 없다고 나온다. \dt 명령어는 public 스키마에 속한 테이블만 보여주는 것 같다. 정확히는 search_path 에 설정된 스키마를 사용한다고 한다.4 스키마에 속한 테이블을 보려면\dt aliceschema.*를 입력한다.

테이블에 대한 자세한 정보를 보려면\d를 사용한다. \d는 public 스키마에 있는 테이블만 보여줄 것이므로, 특정 스키마에 존재하는 테이블을 보려면\d aliceschema.alicetable과 같은 형식으로 입력해야 한다.

(7) 테이블에 내용 넣기 (CRUD)

CRUD는 모두 기존의 SQL과 동일하므로 insert 하나만 설명한다. 딱히 특별할 것이 없기 때문이다.

insert into (테이블 명) values(....)를 쓰는데, 한 가지 다른 점이 있다면, 스키마를 지정하지 않으면 search_path의 스키마를 지정한다는 점이다. 따라서 특정 스키마의 테이블을 쓰고 싶다면 insert into(스키마 명).(테이블 명)... 처럼 쓴다. 아래는 aliceschema의 alicetable에 데이터를 넣는 예시이댜.

'DataBase > postgreSql' 카테고리의 다른 글

PostgreSQL에서 Bulk Upsert  (0) 2020.02.27
postgreSql 정수자료형 정리  (0) 2019.05.21

+ Recent posts