node-postgres(pg)メモ
デフォルトの状態はオートコミット。
client.query("BEGIN") (標準SQLに従うなら START TRANSACTION の方がいいかも)でトランザクション開始。client.query("COMMIT"), client.query("ROLLBACK") でトランザクションの終了。
特別なメソッドはなく、普通のSQL文として呼び出す。
COUNT(*) は bigint 型。
bigint 型は、JavaScript の Number 型として返ってくる。
bigint 型は、Number.MAX_INTEGER (9007199254740991) より大きな数は正しく返ってこない。
numeric 型は JavaScript の String 型として返ってくる。(要注意)
INSERT での重複(一意性違反)は例外を発生させる。例外は Error オブジェクトで、 code プロパティに '23505' (文字列)が設定される。
PostgreSQL 自体の性質として、トランザクション中にSQLエラーが発生すると、トランザクションは Abort される。ROLLBACK するまで何もできなくなる。
つまり、INSERT のSQLエラーを使って条件判断する方式はまともに使えない。
$番号で指定するプレースホルダは、同じ番号が何度も現れてもよい。その番号のパラメータが使われる。
Java や ecpg などで、?で必ず1つづつ必ず順番に指定するのとは違う。
値::型で型変換できるが、意味もなく型変換するべきではない。
検索条件で型変換するとなぜかフルスキャンになり、異様に遅くなることがある。
やむを得ず、SQL文に文字列変数を埋め込みたい場合、client.escapeLiteral, client.escapeIdentifier を使う。(ただし、undocumented)
client.escapeLiteral 文字列用。シングルクォーテーションでくくった文字列にする
client.escapeIdentifier 識別子用。ダブルクォーテーションでくくった文字列にする
数値は Number でくくればとりあえずなんとかなるはず。変換不能な数値は NaN になる。
x = ${Number( x )}
ほんとはもっと手前でバリデーションをやっておくべき。
セミコロンで区切れば複数のSQLを一度に発行できる。返り値は発行数分の配列として返ってくる。(バルク処理)
1件ごとに query を呼び出すより、まとめて実行するバルク処理の方が遙かに早い。(10倍くらい)
無理矢理1つのSQL文にまとめる必要性はなく、単純なSQLを複数行つないで送ってもほとんど速度は変わらない。
char 型の場合、後方ブランクは桁数にかかわらず同一判定される。(これは pg に関係なく SQL の仕様)
$数字のパラメータで渡した値はすべて text 型として取り扱われる。
必要に応じて、text 型からの自動変換がかかる。
$数字のパラメータで渡した値の型を pg_typeof で見ようとしても「error: パラメータ$1のデータ型が決定できません」となってしまう。
1次元配列であれば渡すことができる。
WHERE x = ANY ($1) のようにすれば配列の値のいずれかに合致するという指定ができる。
列名は、AS 別名を付けて差し替えることができるが、この時の別名は小文字化されてしまう。(PostgreSQL の仕様。標準SQLであるならば大文字化されるのが正しいはず。)
別名の大文字小文字を残したい場合は、ダブルクォーテーションで括ればよい。
関数を使った場合の列名は関数名の小文字化されたものになる。
SELECT MAX(x) FROM table; の場合、max が列名になる。
同じ列名(別名)が重複した場合、後の列だけが残る。(エラーにはならない。)
DDL も問題なく実行できる。(利用は控えめに。)
client.end() でデータベース切断だが、プールを使っている場合は client.release() として切断せずに解放する必要がある。
end と release をごちゃ混ぜで使うとまずい。
client.end() が一度も呼ばれないとプロセスがなかなか終了しない状況になる。ある程度の時間で終了してしまうので原因が分かりづらい。
query で得られた結果には様々な付加情報が付いている。このままクライアントには返すべきではない。
rows だけなら返しても問題ないが、レコードごとに列名が付いているのでやや重い。
圧縮するなら許されるがパフォーマンスは下がる。
以下の _types.types.builtins は query の結果に毎回付いてくる。内部データとしては有用だが通信では取り除きたい。
code:builtins
"builtins": {
"BOOL": 16,
"BYTEA": 17,
"CHAR": 18,
"INT8": 20,
"INT2": 21,
"INT4": 23,
"REGPROC": 24,
"TEXT": 25,
"OID": 26,
"TID": 27,
"XID": 28,
"CID": 29,
"JSON": 114,
"XML": 142,
"PG_NODE_TREE": 194,
"SMGR": 210,
"PATH": 602,
"POLYGON": 604,
"CIDR": 650,
"FLOAT4": 700,
"FLOAT8": 701,
"ABSTIME": 702,
"RELTIME": 703,
"TINTERVAL": 704,
"CIRCLE": 718,
"MACADDR8": 774,
"MONEY": 790,
"MACADDR": 829,
"INET": 869,
"ACLITEM": 1033,
"BPCHAR": 1042,
"VARCHAR": 1043,
"DATE": 1082,
"TIME": 1083,
"TIMESTAMP": 1114,
"TIMESTAMPTZ": 1184,
"INTERVAL": 1186,
"TIMETZ": 1266,
"BIT": 1560,
"VARBIT": 1562,
"NUMERIC": 1700,
"REFCURSOR": 1790,
"REGPROCEDURE": 2202,
"REGOPER": 2203,
"REGOPERATOR": 2204,
"REGCLASS": 2205,
"REGTYPE": 2206,
"UUID": 2950,
"TXID_SNAPSHOT": 2970,
"PG_LSN": 3220,
"PG_NDISTINCT": 3361,
"PG_DEPENDENCIES": 3402,
"TSVECTOR": 3614,
"TSQUERY": 3615,
"GTSVECTOR": 3642,
"REGCONFIG": 3734,
"REGDICTIONARY": 3769,
"JSONB": 3802,
"REGNAMESPACE": 4089,
"REGROLE": 4096
}