はじめに
数百万件程度のデータをPostgreSQLにて集計・分析する作業を通して得た知見をまとめました。
ギャップを感じたりハマった順番に並べていきますので、自然と徐々にレベルが上がるような構成になっています:)
内容はPostgreSQLとOracleとの比較になります。
どちらも触ってみて思いました。PostgreSQLもOracleもどちらも良い！(ｷﾘｯ
2022.07.02 会社の同僚の岡部さんがシェアしているのを見て、レイアウトが崩れていたりコメントを貰っていることに気が付きました。一部修正しています。
データ操作(DML)
1. from句なしでクエリが実行できる
Oracleではfrom dualが必須ですね。
たったこれだけ書かなくて済むだけで非常に快適にクエリが書けます。
> select 1 as a, 2 as b ,3 as c
+-----+-----+-----+
| a | b | c |
|-----+-----+-----|
| 1 | 2 | 3 |
+-----+-----+-----+
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で値を取得します。
> select nextval('seq_test') as seq_no;
+----------+
| seq_no |
|----------|
| 166 |
+----------+
この差はあまり違和感ないです。
> select seq_test.nextval from dual;
NEXTVAL
----------
2
3. 暗黙的型変換
PostgreSQLは基本的に暗黙的型変換しないので、ちゃんとcastします。
下記のような'1.00' + 1のような演算は文字列側のキャストが必要です。
> select 1 + '1.0'
invalid input syntax for integer: "1.0"
LINE 1: select 1 + '1.0'
> 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です。
> select 1 + '1' + 1
+------------+
| ?column? |
|------------|
| 3 |
+------------+
Oracleは頑張って暗黙的に型変換してくれますので、
小数点が入ろうが精度が異なろうが演算してくれます。
> select 1 +'1.00' + 1 as result from dual;
RESULT
----------
3
4. to_single_byteがない
PostgreSqlではtranslateで置換するしかないようです。
一手間くらいなのでそこまで負荷ではないですが、標準であると嬉しかったかもしれません。
> select translate('１２３４５６７８９円です', '０１２３４５６７８９', '0123456789');
+-----------------+
| translate |
+-----------------+
| 123456789円です |
+-----------------+
そういえば、初めてto_single_byteの関数を先輩から教えてもらった時にあまりの便利さに魂が震えた記憶があります。
> select to_single_byte('１２３４５６７８９円です') from dual;
TO_SINGLE_BYTE('１２３４５６７８９円
------------------------------------
123456789円です
5. where句での正規表現
PostgreSQLでwhrere句に正規表現でフィルタリングを行いたい場合です。
Oracleでいうregexp_likeが無く、チルダで代用するようです。
よくあるスクリプト言語系の正規表現の演算子なので分かりやすいですし、並び順も良いですね。
select * from table_nm where title ~ '[a-zA-Z]';
Oracleのregexp_likeも違和感は無いですが、関数であることを一瞬忘れてよく間違います。
（regexp_likeという演算子があるのかと。笑）
select * from table_nm where regexp_like(column_nm, '[a-zA-Z]');
6. where正規表現で文字列結合するときは丸括弧()で囲む
上記の関連ですが、~演算子を用いて検索したい正規表現を文字列結合で生成したい場合は丸括弧()で囲む必要があります。
select * where table_nm where column_nm1 ~ (column_nm2 || '[0-9]+.*');
これだとERROR: argument of WHERE must be type boolean, not type textという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/
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をつい使ってしまいます。
> 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の差を求めて日付の差を取得します。
クエリは関数がネストしていて見にくいですが、以下の順序で処理しています。
- 時刻の文字列を
to_timestamp関数で変換して差分を取る - timestampの差分を
to_char関数でmiの文字列に変換 - 上記で分の差分が出ているはずなので、
castして数値に変換
11:45と11:00の差分取得を例にしています。
> 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分と誤って取得してしまってます。
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分が取得できています。
> select EXTRACT(EPOCH FROM to_timestamp('1145', 'hh24mi') - to_timestamp('1000', 'hh24mi')) / 60;
+------------+
| ?column? |
|------------|
| 105 |
+------------+
SELECT 1
OracleのDate型は整数部で日付、小数部で時刻を管理しているので、時間×分を書けてあげればよいです。
trunc関数で少数点を切り捨てるために利用しています。
> 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句には設定できるので、機能的には問題無いです。
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
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句が必須なのが多少面倒ですね。
> 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では有用ではない結果が得られがちです。以下の例のようにデータ範囲を指定することでこれを回避できます。
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が参照できるパスである必要があります。
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句が不要です。
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スキーマからしか取得できなかったので、混在したクエリを作る必要があります。
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_系のディクショナリにアクセスしました。
-- テーブルのメタ情報を検索
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も設定しているのに有効になっていないので、最初は戸惑いました。
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の設定が無効になるならまだしも、全件を保持する仕様みたいです。
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/