SQL
OVER() の PARTITION BY でグルーピングした中で、ORDER BY した先頭を抽出する。DBは、Oracle 、PostgreSQL、MySQL も同じ例）テーブル JOB_RESULTS を JOB_CD 列でグルーピングして POINT列 の ORDER BY DESC の結果、先頭を抽出する。ROW_NUMBER() を OVE…
1~1000 の間の乱数を１００個作る。 対象：Oracle WITH generate(id, rnum) AS ( SELECT 1, TRUNC(dbms_random.value(1,1000 )) FROM dual UNION ALL SELECT id + 1, TRUNC(dbms_random.value(1,1000 )) FROM generate WHERE id + 1 <= 100 ) SELECT rnum FR…
WITH 句を複数記述する場合は、カンマで区切って WITH 宣言を書かずに次のWITH句を 記述する。PostgreSQL の例。１から８までの数値IDのテーブルに、乱数発生のテーブルのIDが奇数で外部結合させる。 WITH RECURSIVE generate1(id) AS ( SELECT 1 UNION ALL …
表題の件、では代わりにストアド FUNCTION でも代替案があるのだろうが、 ストアド FUNCTION 定義の権限がない時、 WITH句で対応する方法がある。数値の例 WITH generate_series(n) AS ( SELECT 1 FROM dual UNION ALL SELECT n + 1 FROM generate_series WH…
実行計画を調査して、ヒント句を付けるなどの調整も検討が必要であるが、 ROW_NUMBER() でページングでをする場合のSQLは、以下のようになる。１ページ目として先頭１行目～１０行目 SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY avrg DESC, point DE…
Oracle は、SQLバインド変数の上限を超えると、 ORA-01745: ホスト/バインド変数名が無効です。 が発生する Oracle のバインド変数の上限は、32768PostgreSQL は、32767MySQL はクエリの上限サイズで制限を見た方が良いみたい show variables like 'max_allo…
以下のようなテーブルがあるとき、 CREATE TABLE worktables ( id uuid DEFAULT gen_random_uuid() NOT NULL, wname varchar(32) NOT NULL, ktype NUMERIC NOT NULL, point NUMERIC DEFAULT 0 NOT NULL, at_create date NOT NULL, CONSTRAINT worktables_pke…
MySQL 、PostgreSQL についての UPSERT 文はこれまでに以下を書いてきたが、 Oracle の UPSERT （MERGE）を書いてなかったので、書き方を書いておくことにした。MySQL で UPSERT する時に考えること - Oboe吹きプログラマの黙示録PostgreSQL のアップサート…
PostgreSQL の場合現在の接続状況を見る。 SELECT * FROM pg_stat_activity 現在接続中のセッションは、 WHERE state = 'active' あるいは、 WHERE xact_start IS NOT NULL で、抽出する。SELECT pg_terminate_backend(PID) で強制的に切断する。 MySQLの場…
PostgreSQL 8.4 以上で使えるようになったウィンドウ関数 テーブルのレコード総数を SELECT句に含めたい場合 COUNT(1) OVER() を指定する。 SELECT *, COUNT(1) OVER() AS total FRON items これは、MySQL でも使える。同様に行番号を SELECT句に付与したい…
MySQL の GROUP_CONCAT あるいは、PostgreSQL の STRING_AGG から、CSV１行を作る。 作ってSELECTした文字列は、先日書いた原始的なメソッド CSV形式の１行分の文字列からList<String>への変換 - Oboe吹きプログラマの黙示録 を使って、List<String> を取得する。任意の列で</string></string>…
collate utf8_unicode_ciutfbm4 のキャラクタコードに対してこの collate を指定するとエラーになる、 Error Code: 1253. COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4' 以下でデーターベースがどうなっているか確認する SHOW VARI…
Java8 以降、java.time.LocalDate と java.time.LocalDateTime の最小値／最大値は、 LocalDate.MIN → -999999999-01-01 LocalDate.MAX → +999999999-12-31 LocalDateTime.MIN → -999999999-01-01T00:00 LocalDateTime.MIN → +999999999-12-31T23:59:59.9999…
PostgreSQL とは違って、ルートを＄文字で指定して、"->" による連結ではなく JSON列名 -> path あるいは、 JSON列名 ->> path で、文字列とての参照になる。 path 部分が、＄から始まってJSONキーを '.' ピリオド区切り文字で指定する。例） jdataという列…
PostgreSQL の JSON_SET／JSONB_SET と同じ要領で使うと失敗する。 JSON_SET(json_doc, path, val[, path, val] ...) path の指定は、JSONルートの $ から、区切り文字 '.' ピリオドで指定する。 PostgreSQL のようにキーが存在しなかったらセットする／しな…
例） 列名 jdata というJSONB列に以下の json データが入っているとする。 { "fr": 11, "key": "E", "pub": "ara", "item": { "name": "orange", "group": { "x01": "AX193", "x02": "AX248", "c923": "A_982" }, "price": 231, "oprion": " " }, "point": 1…
先日書いた JSONB の primary key を作る ( by PosetgreSQL ) - Oboe吹きプログラマの黙示録 これでは、目的の主キーとして、任意の列とJSONB型のJSON内の値のキーとする場合の CONSTRAINT を２つ書けない制約でUPSERT文 (ON CONFLICT～DO UPDATE）を思うよ…
単純に CREATE TABLE の PRIMARY KEY で指定してもエラーになる。 CREATE TABLE public.jsample ( id int4 NOT NULL, title varchar(60) NOT NULL, jdoc jsonb not NULL, PRIMARY KEY (id, jdoc->'item'->>'id') ); SQLエラー [42601]: ERROR: "->"またはそ…
PostgreSQL でのことである。 あるテーブルの JSONB型に、以下のようなJSON値があるとする。 { "a":{ "a1": "Apple", "a2": "Lemon" } } このJSONの"a1" を null にする場合、シングルクォートで括った 文字列 ' null' 指定した以下なら成功する。 UPDATE jb…
JSON型、JSONB型、の JSON の null の扱い まずは JSON演算子の基本から、 "b" 配列の１番目 SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c' 結果は 文字型で 2 "b" 配列の２番目 SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":n…
UPDATE tablename SET column = value WHERE ~ RETURNING ~ に続けて GROUP BY を書いて利かせたいと思ってそのまま書いてはダメである。WITH 句を使う。例） WITH sampleupdate AS ( UPDATE samples SET delete_flg = true WHERE delete_flg = false RETURN…
Java で JSON を編集するときによく使われるのが Jackson ライブラリであろう。 巨大で階層が深い中の値を変更するのは、Jackson の JsonNode としてJSONを読み込んで、コードを書くのも 面倒くさいばかりでなく、あまり汎用的なものは期待できない。 Postgr…
JSON型json_pretty( column ) JSONB型jsonb_pretty( column )JSONB型の方が使う機会が多そう。 SELECT jsonb_pretty('{"f1":1,"f2":null, "f3":[1,2,3]}') { "f1": 1, "f2": null, "f3": [ 1, 2, 3 ] }
先日書いた PostgreSQL JSON の部分的更新 - Oboe吹きプログラマの黙示録 この方法では、ネストしたキーを指定して一部を更新することができない。 例） { "task": { "name": "A", "order" : 10, "limit" : "2023-12-09" } }このようなJSONに対して、'task'-…
JSON型データの一部の key value を更新する。サンプルデータ、JSON列が、次のデータである時、 {"key": "E", "name": "orange", "ppp": "pst" } 追加や更新 || で、マージする。 UPDATE jbooks SET jdata = jdata::jsonb || json_build_object( 'key', 'D',…
マニュアル参照：9.15. JSON関数と演算子JSON の値、数値として SELECT されるか？ '{ "a": [1,2,3] }'::json->'a'->1 この場合、2 が数値として取り出せるか？というとそうではないらしい-> この演算子のまま、 以下のように CAST してはダメだ。 CAST('{ "…
SELECT 句等での uuid の 文字列への変換、 ::text もしくは、::varchar を付ける。GROUP BY なんかで集計した場合の例、 string_agg(id::text , ',' order by create_time) AS idjoin または、 string_agg(id::varchar , ',' order by create_time) AS idjo…
過去、以下２つを投稿しましたが、 PostgreSQL で mySQL の last_insert_id() 相当をmybatis で実装したい - Oboe吹きプログラマの黙示録PostgresSQL アップサートのインクリメント - Oboe吹きプログラマの黙示録そもそも、PostgreSQL の RETURNING は、INSE…
MySQL の GROUP_CONCAT と同様のことをやる。GROUP BY を指定したSELECT で、STRING_AGG を使う。書式は、 STRING_AGG ( expression, separator [order_by_clause] )order by で指定する列は、他の列でもいい
UPDATE 時のインクリメントがうまくいくからといって、 アップサートでのインクリメントが同じ構文では効かないのが PostgresSQLこんなテーブルがあった時、 CREATE TABLE ITEMS ( ID VARCHAR(6), NAME VARCHAR(20), U_COUNT INTEGER, PRIMARY KEY (id) ) こ…
