PostgreSQLに入門して感じた18のギャップ

  • 76
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

はじめに

数百万件程度のデータをPostgreSQLにて集計・分析する作業を通して得た知見をまとめました。
ギャップを感じたりハマった順番に並べていきますので、自然と徐々にレベルが上がるような構成になっています:)

内容はPostgreSQLとOracleとの比較になります。
どちらも触ってみて思いました。PostgreSQLもOracleもどちらも良い!(キリッ

posora.png

データ操作(DML)

1. from句なしでクエリが実行できる

Oracleではfrom dualが必須ですね。
たったこれだけ書かなくて済むだけで非常に快適にクエリが書けます。

postgresql
> select 1 as a, 2 as b ,3 as c
+-----+-----+-----+
|   a |   b |   c |
|-----+-----+-----|
|   1 |   2 |   3 |
+-----+-----+-----+
oracle比較
SQL> select 1 as a, 2 as b ,3 as c from dual;

         A          B          C
---------- ---------- ----------
         1          2          3

2. シーケンスの取得方法

OracleだとSQLにはfrom句が必須なので、dual表を指定します。
また、<シーケンスオブジェクト名>.nextvalで値を取得します。

postgresql版シーケンス値取得
> select nextval('seq_test') as seq_no;

+----------+
|   seq_no |
|----------|
|      166 |
+----------+

この差はあまり違和感ないです。

oracle版シーケンス値取得
> select seq_test.nextval from dual;

   NEXTVAL
----------
         2

3. 暗黙的型変換

PostgreSQLは基本的に暗黙的型変換しないので、ちゃんとcastします。
下記のような'1.00' + 1のような演算は文字列側のキャストが必要です。

postgresqlのNGバージョン
> select 1 + '1.0'
invalid input syntax for integer: "1.0"
LINE 1: select 1 + '1.0'
postgresql型変換
> select
    cast('1.00' as float) + 1 as float
,   cast('1.00' as numeric) + 1 as numeric
,   cast('1.00' as double precision) + 1 as double
+---------+-----------+----------+
|   float | numeric   |   double |
|---------+-----------+----------|
|       2 | 2.00      |        2 |
+---------+-----------+----------+

ただ、小数点が入っていないならPostgreSQLも暗黙的型変換してくれます。
Integer型に合わせれば前後左右から演算してもOKです。

postgresql版暗黙的型変換
> select 1 + '1' + 1
+------------+
|   ?column? |
|------------|
|          3 |
+------------+

Oracleは頑張って暗黙的に型変換してくれますので、
小数点が入ろうが精度が異なろうが演算してくれます。

Oracle暗黙的型変換
> select 1 +'1.00' + 1 as result from dual;

    RESULT
----------
         3

4. to_single_byteがない

PostgreSqlではtranslateで置換するしかないようです。
一手間くらいなのでそこまで負荷ではないですが、標準であると嬉しかったかもしれません。

postgresql版全半角変換
> select translate('123456789円です', '0123456789', '0123456789');
+-----------------+
| translate       |
+-----------------+
| 123456789円です |
+-----------------+

そういえば、初めてto_single_byteの関数を先輩から教えてもらった時にあまりの便利さに魂が震えた記憶があります。

Oracle版to_single_byte
> select to_single_byte('123456789円です') from dual;

TO_SINGLE_BYTE('123456789円
------------------------------------
123456789円です

5. where句での正規表現

PostgreSQLでwhrere句に正規表現でフィルタリングを行いたい場合です。
Oracleでいうregexp_likeが無く、チルダで代用するようです。
よくあるスクリプト言語系の正規表現の演算子なので分かりやすいですし、並び順も良いですね。

postgresql版正規表現の検索
select * from table_nm where title ~ '[a-zA-Z]';

Oracleのregexp_likeも違和感は無いですが、関数であることを一瞬忘れてよく間違います。
(regexp_likeという演算子があるのかと。笑)

Oracle版正規表現の検索
select * from table_nm where regexp_like(column_nm, '[a-zA-Z]');

6. where正規表現で文字列結合するときは丸括弧()で囲む

上記の関連ですが、~演算子を用いて検索したい正規表現を文字列結合で生成したい場合は丸括弧()で囲む必要があります。

postgresq1の正規表現の検索その2
select * where table_nm where column_nm1 ~ (column_nm2 || '[0-9]+.*');

これだとERROR: argument of WHERE must be type boolean, not type textというsyntaxエラーになっちゃいます。

postgresq1のsyntaxエラー(丸括弧が無いため)
select * where table_nm where column_nm1 ~ column_nm2 || '[0-9]+.*';

ERROR:  argument of WHERE must be type boolean, not type text

7. 現在日時取得

PostgreSQLでは現在日時の取得方法が複数あるようです。
非標準なnow()は使わず、current_xxx系を使ったほうが良いようです。

now()は以下のようにハマりやすいようなので注意が必要です。

(注意)PostgreSQLのnow()はトランザクションの開始時刻を返す
https://siguniang.wordpress.com/2013/05/17/how-do-i-return-current-timestamp-with-postgresql/

postgresqlの現在日時取得
select CURRENT_TIMESTAMP as now
union all select now() as now -- 非標準
union all select TIMESTAMP 'now' as now;
+----------------------------------+
| now                              |
|----------------------------------|
| 2015-12-02 17:21:32.889558+09:00 |
| 2015-12-02 17:21:32.889558+09:00 |
| 2015-12-02 17:21:32.889558+09:00 |
+----------------------------------+

OracleでもCURRENT_TIMESTAMPは使えますが、私は慣れでsysdateをつい使ってしまいます。

Oracle現在時刻を取得
> select CURRENT_TIMESTAMP  from dual
  2  union all select sysdate from dual
  3  ;
CURRENT_TIMESTAMP
----------------------------------------
15-12-02 17:30:14.330000 +09:00
15-12-02 17:30:14.000000 +09:00

8. 時刻差分

PostgreSQLで日付データの差分を取得する方法です。
PostgreSQLではDATEDIFF関数はないので、timestampの差を求めて日付の差を取得します。

クエリは関数がネストしていて見にくいですが、以下の順序で処理しています。

  1. 時刻の文字列をto_timestamp関数で変換して差分を取る
  2. timestampの差分をto_char関数でmiの文字列に変換
  3. 上記で分の差分が出ているはずなので、castして数値に変換

11:45と11:00の差分取得を例にしています。

postgresql時刻差分1(実は正確に差がでないやりかた)
> select cast(to_char(to_timestamp('1145', 'hh24mi') - to_timestamp('1100', 'hh24mi'), 'mi') as double precision);
+-----------+
| to_char   |
|-----------|
| 45        |
+-----------+

ただ、上記だと11:45と10:00といった「1時間」以上の差分は上手く取得できません。
取得したい値は105分ですが、45分と誤って取得してしまってます。

postgresql時刻差分2(1時間以上だと上手く差分が取得できない…)
gis> select cast(to_char(to_timestamp('1145', 'hh24mi') - to_timestamp('1000', 'hh24mi'), 'mi') as double precision);
+-----------+
|   to_char |
|-----------|
|        45 |
+-----------+
SELECT 1
Command Time: 0.000s

これを上手く数値に変換するのがよくわからなかったのですが、extract epocを使うと良いらしいです。
利用すると正しく105分が取得できています。

postgresql時刻差分3(extract_epoc利用版)
> select EXTRACT(EPOCH FROM to_timestamp('1145', 'hh24mi') - to_timestamp('1000', 'hh24mi')) / 60;
+------------+
|   ?column? |
|------------|
|        105 |
+------------+
SELECT 1

OracleのDate型は整数部で日付、小数部で時刻を管理しているので、時間×分を書けてあげればよいです。
trunc関数で少数点を切り捨てるために利用しています。

oracle日付計算
> select trunc(to_number(to_date('1145', 'hh24mi') - to_date('1000', 'hh24mi')) * 24 * 60) as diff from dual;
      DIFF
----------
       105

9. Merge文が無い

PostgreSQLでは標準でMerge文のサポートが無いのですが、update 文にfrom/where句を設定することで対応できます。
ちなみにset句でテーブルエイリアスは付与できない仕様なようです。
もちろんwhere句には設定できるので、機能的には問題無いです。

postgresql版Merge文相当
update 
    table_nm1 a 
set
    column_nm1 = column_nm2  -- ここではテーブルエイリアスは付与できない
from
    table_nm2  b
where
    a.id = b.id;

10. 集約関数:カンマ区切りで集約

方言の差レベルですね。
string_aggはPostgreSQL 9.0からの機能なようです。
http://lets.postgresql.jp/documents/technical/9.0/2

PostgreSQL
select
    string_agg(inv.col, ',')
from (
    select '1'  as col
    union all select '2'    as col
    union all select '3'    as col
) inv
+--------------+
| string_agg   |
|--------------|
| 1,2,3        |
+--------------+

Oracleではlistagg関数を利用します。
ソート順を指定しない時もwithin group句が必須なのが多少面倒ですね。

Oracle版カンマ区切りで集約
> select
  2     listagg(inv.col, ',') within group(order by null)
  3  from (
  4     select '1'      as col from dual
  5     union all select '2' as col from dual
  6     union all select '3' as col from dual
  7  ) inv
  8  ;

LISTAGG(INV.COL,',')WITHINGROUP(ORDERBYNULL)
----------------------------------------------------------
1,2,3

11. last_valueの使い方

PostgreSQLではlast_valueに適切なオプションを入れないと直感的に動作しません。
以下のようなテーブルで試します。

+-------+-------+
|   age | grp   |
|-------+-------|
|    15 | man   |
|    20 | man   |
|    22 | man   |
|    17 | woman |
|    25 | woman |
+-------+-------+

これに対して以下のクエリを投げるとlast_val_1が欲しい値が取得できません。
last_valueにRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを指定する必要があるそうです。

理由はこちらで知りました。
http://postd.cc/window_functions_postgresql/#10

PostgreSQLのウィンドウフレームはデフォルトで”パーティションの先頭から現在の行の最後ピアまで”が含まれるとあります。これではlast_valueとnth_valueでは有用ではない結果が得られがちです。以下の例のようにデータ範囲を指定することでこれを回避できます。

PostgreSQLのlast_value使い方
select
   age
,  grp
,  last_value(age) over(partition by grp order by age) as last_val_1  -- 誤った値が取得
,  last_value(age) over(partition by grp order by age RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_val_2
from (
            select '15' as age, 'man' as grp
  union all select '20' as age, 'man' as grp
  union all select '22' as age, 'man' as grp
  union all select '17' as age, 'woman' as grp
  union all select '25' as age, 'woman' as grp
  union all select '30' as age, 'woman' as grp
)  inv
;
+-------+-------+--------------+--------------+
|   age | grp   |   last_val_1 |   last_val_2 |
|-------+-------+--------------+--------------|
|    15 | man   |           15 |           22 |
|    20 | man   |           20 |           22 |
|    22 | man   |           22 |           22 |
|    17 | woman |           17 |           30 |
|    25 | woman |           25 |           30 |
|    30 | woman |           30 |           30 |
+-------+-------+--------------+--------------+

dense_rankやrownumberなどの集約関数の挙動は、下記が参考になりました。
http://postd.cc/window_functions_postgresql/#10

12. CSV出力

copy句でCSV出力できます。
ファイルパスはPostgreSQLが参照できるパスである必要があります。

CSV出力
COPY (SELECT * FROM table_nm) TO '/tmp/output.csv' DELIMITER ','

13. バルクロード

copy句でバルクロード出来ます。
from句にファイルパスを指定します。
headerオプションをtrueにするとCSVの1行目はカラム行だと認識するのでスキップしてくれます。

バルクロード
COPY table_nm
       FROM
           '/var/temp/sample.csv'
       WITH
       (
           FORMAT csv,
           HEADER  true,    -- ヘッダあり
           DELIMITER  ',',
           QUOTE   '"',
           ENCODING  'UTF-8'
       );

データ定義(DDL)

14. カラム名の最大長に制限がない

oracleだと30文字制限があるためエラーになってしまいます。
この制約のため場合によっては母音を省略するといったテクニックを使っていたのですが、PostgreSQLではシステム的な制約は無いんですね。
60文字超えのカラム名でもOKです。

> create table table_nm (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 integer);
CREATE TABLE
Command Time: 0.000s
Format Time: 0.000s

メタ情報を検索するとちゃんと60文字超えでもOKです

select
column_name
from
information_schema.columns
where
table_name ='table_nm'
;
+----------------------------------------------------------------+-----------+
| column_name | data_type |
+----------------------------------------------------------------+-----------+
| abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz1234567890 | integer |
+----------------------------------------------------------------+-----------+

Oracleでは当然NG。

> create table table_nm (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 integer);
Error
[row:1,col:20] ORA-00972: 識別子が長すぎます。

15. 数値型の定義

numeric(4, 2)では10の2乗までしか格納できません。
numecic(4)では10の4乗までです。
精度をつけると格納できる最大値が変わるようで、地味に気が付きませんでした。

16. カラムの桁数変更

modify句が不要です。

postgresqlカラム型の変更
alter table table_nm1 alter column column_nm1 type varchar(15);

データ・ディクショナリ

17. テーブルのメタ情報取得方法

テーブル名、カラム名、型・桁、コメントなどの情報をデータベースのディショナリから取得する方法です。
実はPostgreSQLを触って一番困ったのは地味にこの手のテクニックじみたところでした。

検索用のテーブルを生成します。
PostgreSQL,Oracleのどちらでも生成できます。

テーブル生成
create table employee ( 
   id       char(5) 
,  name      varchar(64) 
,  age       numeric(3, 0) 
,  sex_type  char(1) 
,  tel       varchar(12) 
,  primary key(id) 
)
;
COMMENT ON TABLE employee IS '社員マスタテーブル';
COMMENT ON COLUMN employee.id IS '社員ID';
COMMENT ON COLUMN employee.name IS '氏名';
COMMENT ON COLUMN employee.age IS '年齢';
COMMENT ON COLUMN employee.sex_type IS '性別区分';
COMMENT ON COLUMN employee.tel IS '電話番号';

PostgreSQLのディクショナリはpg_catalogスキーマか、information_schemaから取得できるようです。
後者のinformation_schemaの方がSQL標準らしいのでそちらで完結させたかったのですが、テーブルコメントやカラムコメントが
pg_catalogスキーマからしか取得できなかったので、混在したクエリを作る必要があります。

postgresql版ディクショナリ検索
SELECT
    col.table_schema
,   col.table_name
,   tbl.description     as table_comment
,   col.ordinal_position
,   col.column_name
,   pgd.description     as column_comment
,   col.data_type
,   col.column_default
,   col.is_nullable
,   col.character_maximum_length
,   col.numeric_precision
,   col.numeric_precision_radix
,   col.numeric_scale
FROM
    pg_catalog.pg_statio_all_tables     sat
    INNER JOIN
        pg_catalog.pg_description       pgd
    ON  (
            pgd.objoid          = sat.relid
        )
    INNER JOIN
        pg_catalog.pg_description       tbl
    ON  (
            tbl.objoid          = pgd.objoid
        AND tbl.objsubid        = 0
    )
    INNER JOIN
        information_schema.columns  col
    ON  (
            col.ordinal_position= pgd.objsubid
        AND col.table_schema    = sat.schemaname
        AND col.table_name      = sat.relname
        )
WHERE 1 = 1
AND col.table_name          = 'employee'
ORDER BY
    col.table_schema
    ,col.table_name
    ,col.ordinal_position
;
+----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------+
| table_schema   | table_name   | table_comment      |   ordinal_position | column_name   | column_comment   | data_type         |   column_default | is_nullable   |   character_maximum_length |   numeric_precision |   numeric_precision_radix |   numeric_scale |
|----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------|
| public         | employee     | 社員マスタテーブル |                  1 | id            | 社員ID           | character         |           <null> | NO            |                          5 |              <null> |                    <null> |          <null> |
| public         | employee     | 社員マスタテーブル |                  2 | name          | 氏名             | character varying |           <null> | YES           |                         64 |              <null> |                    <null> |          <null> |
| public         | employee     | 社員マスタテーブル |                  3 | age           | 年齢             | numeric           |           <null> | YES           |                     <null> |                   3 |                        10 |               0 |
| public         | employee     | 社員マスタテーブル |                  4 | sex_type      | 性別区分         | character         |           <null> | YES           |                          1 |              <null> |                    <null> |          <null> |
| public         | employee     | 社員マスタテーブル |                  5 | tel           | 電話番号         | character varying |           <null> | YES           |                         12 |              <null> |                    <null> |          <null> |
+----------------+--------------+--------------------+--------------------+---------------+------------------+-------------------+------------------+---------------+----------------------------+---------------------+---------------------------+-----------------+
SELECT 5

Oracleの場合はログイン中のスキーマであればuser_から始まるテーブルから取得できます。
他のスキーマのテーブルにアクセスしたい場合はdba_で始まるテーブルから取得します。
今回はせっかくなのでスコープが広いdba_系のディクショナリにアクセスしました。

oracle版ディクショナリ検索
-- テーブルのメタ情報を検索
select
    col.TABLE_NAME
,   tct.COMMENTS        as table_comment
,   col.COLUMN_NAME
,   cct.COMMENTS        as column_comment
,   col.DATA_TYPE
,   col.DATA_LENGTH
,   col.NULLABLE
,   col.DATA_DEFAULT
from
    DBA_TAB_COLUMNS     col
,   DBA_TAB_COMMENTS    tct
,   DBA_COL_COMMENTS    cct
where 1 = 1
and col.OWNER               = tct.OWNER
and col.TABLE_NAME          = tct.TABLE_NAME
and tct.OWNER               = cct.OWNER
and tct.TABLE_NAME          = cct.TABLE_NAME
and col.COLUMN_NAME         = cct.COLUMN_NAME
and lower(col.OWNER)        = 'work_schema' -- ★スキーマ名を指定
and lower(col.TABLE_NAME)   = 'employee'    -- ★テーブル名を指定
order by
    col.OWNER
,   col.TABLE_NAME
,   col.COLUMN_ID
;

-- 実行結果(Sqlplusでは上手く表示されなかったのでフォーマットが少し異なります)
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| OWNER       | TABLE_NAME | TABLE_COMMENT      | COLUMN_NAME | COLUMN_COMMENT | DATA_TYPE | DATA_LENGTH | DATA_PRECISION | NULLABLE | DATA_DEFAULT |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE   | 社員マスタテーブル | ID          | 社員ID         | CHAR      | 20          |                | N        |              |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE   | 社員マスタテーブル | NAME        | 氏名           | VARCHAR2  | 256         |                | Y        |              |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE   | 社員マスタテーブル | AGE         | 年齢           | NUMBER    | 22          | 3              | Y        |              |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE   | 社員マスタテーブル | SEX_TYPE    | 性別区分       | CHAR      | 4           |                | Y        |              |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+
| work_schema | EMPLOYEE   | 社員マスタテーブル | TEL         | 電話番号       | VARCHAR2  | 48          |                | Y        |              |
+--------+------------+--------------------+-------------+----------------+-----------+-------------+----------------+----------+--------------+

JDBC

18. 大量件数のSELECTでOutOfMemory

ResultSetを逐次処理している(=一括でメモリに抱えたりしていない)のにOutOfMemoryになってしまいました。
executeQueryの時点で全件メモリにかかえていそうな挙動です。
setFetchSizeも設定しているのに有効になっていないので、最初は戸惑いました。

setAutoCommitを未設定
package example;

import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class App {

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection con = DriverManager.getConnection("jdbc:postgresql://<host>:<ip>/<sid>", "<user>", "<pass>");

        PreparedStatement stmt = con.prepareStatement("select * from large_size_table"); // 大量件数のクエリ
        stmt.setFetchSize(1000);

        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String value1 = rs.getString("column_nm1");
            String value2 = rs.getString("column_nm2");
            String value3 = rs.getString("column_nm3");
            System.out.println(value1 + "," + value2 + "," + value3);
        }

    }
}

実行するとOutOfMemoryエラーが発生してしまいます。

実行結果
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at java.lang.Class.getDeclaredFields0(Native Method)
    at java.lang.Class.privateGetDeclaredFields(Class.java:2583)
    at java.lang.Class.getDeclaredField(Class.java:2068)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl$1.run(AtomicReferenceFieldUpdater.java:316)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl$1.run(AtomicReferenceFieldUpdater.java:314)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl.<init>(AtomicReferenceFieldUpdater.java:313)
    at java.util.concurrent.atomic.AtomicReferenceFieldUpdater.newUpdater(AtomicReferenceFieldUpdater.java:109)
    at java.sql.SQLException.<clinit>(SQLException.java:372)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1817)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    at tig.exp.App.main(App.java:37)

こちらはConnection#setAutoCommitにfalseを設定することで回避できました。
やりたいことはDBの参照だけだったので未設定だったのですが、設定しないとStatement#setFetchSizeの設定が無効になるならまだしも、全件を保持する仕様みたいです。

setAutoCommitにfalseを設定し、OutOfMemoryを回避します
        Connection con = DriverManager.getConnection("jdbc:postgresql://<host>:<ip>/<sid>", "<user>", "<pass>");
        con.setAutoCommit(false);

Statement#setFetchSize自体の設定は、con.setAutoCommit(false)を設定し、かつstmt.setFetchSize(Integer.MAX_VALUE);などを設定すると予想通りOutOfMemoryになったので有効に働いてくれているようです。

最後に

3,4年位Oracleを使っていましたが、機能面ではPostgreSQLでも大ハマリすることなく利用できまました。
所詮SQLの枠組みで、手続き型言語ほど百花繚乱という感じでも無いので、あまり気にすること無く利用を続けたいと思います。

非機能面での差異はPgConさんなどの資料が面白そうですね。凄い資料です。
https://www.pgecons.org/