SQL

MySQL のJSON型の path書式

SQL

PostgreSQL とは違って、ルートを$文字で指定して、"->" による連結ではなく JSON列名 -> path あるいは、 JSON列名 ->> path で、文字列とての参照になる。 path 部分が、$から始まってJSONキーを '.' ピリオド区切り文字で指定する。例) jdataという列…

MySQL の JSON_SET関数

SQL

PostgreSQL の JSON_SET/JSONB_SET と同じ要領で使うと失敗する。 JSON_SET(json_doc, path, val[, path, val] ...) path の指定は、JSONルートの $ から、区切り文字 '.' ピリオドで指定する。 PostgreSQL のようにキーが存在しなかったらセットする/しな…

PostgreSQL JSONB 抽出時に出力したくないキー、値を指定する

SQL

例) 列名 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 のキーと他のキーの複合キー(by PostgreSQL)

SQL

先日書いた JSONB の primary key を作る ( by PosetgreSQL ) - Oboe吹きプログラマの黙示録 これでは、目的の主キーとして、任意の列とJSONB型のJSON内の値のキーとする場合の CONSTRAINT を2つ書けない制約でUPSERT文 (ON CONFLICT~DO UPDATE)を思うよ…

JSONB の primary key を作る ( by PosetgreSQL )

SQL

単純に 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: "->"またはそ…

json_set/jsonb_set で NULL をセットする時の注意

SQL

PostgreSQL でのことである。 あるテーブルの JSONB型に、以下のようなJSON値があるとする。 { "a":{ "a1": "Apple", "a2": "Lemon" } } このJSONの"a1" を null にする場合、シングルクォートで括った 文字列 ' null' 指定した以下なら成功する。 UPDATE jb…

PostgreSQL JSON演算子による JSON null の扱い

SQL

JSON型、JSONB型、の JSON の null の扱い まずは JSON演算子の基本から、 "b" 配列の1番目 SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":null}]}}'::jsonb->'a'->'b'->1->>'c' 結果は 文字型で 2 "b" 配列の2番目 SELECT '{"a":{"b":[{"c":1},{"c":2},{"c":n…

PostgreSQL 更新のRETURNING 結果に対して GROUP BY を実行する

SQL

UPDATE tablename SET column = value WHERE ~ RETURNING ~ に続けて GROUP BY を書いて利かせたいと思ってそのまま書いてはダメである。WITH 句を使う。例) WITH sampleupdate AS ( UPDATE samples SET delete_flg = true WHERE delete_flg = false RETURN…

Jacksonを使う代わりに PostgreSQL の JSON関数を使う

Java で JSON を編集するときによく使われるのが Jackson ライブラリであろう。 巨大で階層が深い中の値を変更するのは、Jackson の JsonNode としてJSONを読み込んで、コードを書くのも 面倒くさいばかりでなく、あまり汎用的なものは期待できない。 Postgr…

PostgreSQL JSON型、JSONB型の整形出力

SQL

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 ネストしたキーの値の更新

SQL

先日書いた PostgreSQL JSON の部分的更新 - Oboe吹きプログラマの黙示録 この方法では、ネストしたキーを指定して一部を更新することができない。 例) { "task": { "name": "A", "order" : 10, "limit" : "2023-12-09" } }このようなJSONに対して、'task'-…

PostgreSQL JSON の部分的更新

SQL

JSON型データの一部の key value を更新する。サンプルデータ、JSON列が、次のデータである時、 {"key": "E", "name": "orange", "ppp": "pst" } 追加や更新 || で、マージする。 UPDATE jbooks SET jdata = jdata::jsonb || json_build_object( 'key', 'D',…

PostgreSQL JSON 型の演算子

SQL

マニュアル参照:9.15. JSON関数と演算子JSON の値、数値として SELECT されるか? '{ "a": [1,2,3] }'::json->'a'->1 この場合、2 が数値として取り出せるか?というとそうではないらしい-> この演算子のまま、 以下のように CAST してはダメだ。 CAST('{ "…

PostgreSQL uuid → string

SQL

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 の RETURNING の特殊な使い方

SQL

過去、以下2つを投稿しましたが、 PostgreSQL で mySQL の last_insert_id() 相当をmybatis で実装したい - Oboe吹きプログラマの黙示録PostgresSQL アップサートのインクリメント - Oboe吹きプログラマの黙示録そもそも、PostgreSQL の RETURNING は、INSE…

PostgreSQL での GROUP_CONCAT

SQL

MySQL の GROUP_CONCAT と同様のことをやる。GROUP BY を指定したSELECT で、STRING_AGG を使う。書式は、 STRING_AGG ( expression, separator [order_by_clause] )order by で指定する列は、他の列でもいい

PostgresSQL アップサートのインクリメント

SQL

UPDATE 時のインクリメントがうまくいくからといって、 アップサートでのインクリメントが同じ構文では効かないのが PostgresSQLこんなテーブルがあった時、 CREATE TABLE ITEMS ( ID VARCHAR(6), NAME VARCHAR(20), U_COUNT INTEGER, PRIMARY KEY (id) ) こ…

Oracle の複数行INSERT

SQL

Oracle で一度に複数行のデータをINSERTするSQL文は、長くなってとても嫌だ。 INSERT ALL INTO tablename VALUES ( value1, value2, ... ) INTO tablename VALUES ( value1, value2, ... ) INTO tablename VALUES ( value1, value2, ... ) SELECT 1 FROM DUA…

SQL整形、オンラインWebで

オンラインWebでブラウザで操作できるサービスを公開してくれてるものがある。https://sqlformat.org/Python で処理されている。GitHub - andialbrecht/sqlparse: A non-validating SQL parser module for Pythonドキュメント https://sqlparse.readthedocs.…

MySQL で、PostgreSQL の generate_series関数と同じことをする。

SQL

MySQL で PostgreSQL の generate_series 関数と同じことをしようとすると、 SET @num:= 0; SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 4; でもこれでは SET 文と2つの文になってしまって mybatis での実行では都合が悪い。 SELECT a.VC…

PostgreSQL の generate_series

SQL

PostgreSQL で、連番や、日付の並びなどSQLクエリ結果として求めるのに便利なのが、 generate_series 関数1~4を ’ 1' ~ ' 4' で出力する SELECT TO_CHAR(GENERATE_SERIES(1, 4), '9999') AS UCD 1~4を ’0001' ~ '0004' で出力する SELECT TO_CHAR(GE…

PostgreSQL で mySQL の last_insert_id() 相当をmybatis で実装したい

PostgreSQL の CURVAL() や、LASTVAL() で、シーケンスから求められるものではなく、 UUID のような型で Primary Key を構成するテーブル挿入直後のキーを取得したい。RETURNING を使う。insert文の次の書式 INSERT INTO テーブル VALUES (...) RETURNING PK…

MySQL last_insert_id() , PostgreSQL

SQL

PostgreSQL で MySQL の last_insert_id() と同様の機能、LASTVAL() (例) CREATE TABLE items ( id serial not null primary key, name varchar(128) not null ); 実行 INSERT INTO items (name) VALUES (‘Lemon’); SELECT LASTVAL(); でも、 www.postgres…

PostgreSQL conflict upsert のトリック

SQL

SELECT して存在しない時だけ、INSERT を行うという操作は、 2回のSQL実行文を流す=コードすることになる。PostgreSQL の CONFLICT の DO UPDATE の条件 WHERE句を 結果 False にすれば、すでにレコードが存在すれば、UPDATEが走らないことになる。よくクエ…

PostgreSQL constraint を書かない時の UPSERT

SQL

かなり昔、 oboe2uran.hatenablog.comを書いた。 constraint を書かない時の UPSERT、Primary Key しか書いていない時の UPSERT の書き方は、 前回の投稿のテーブルで、id が Primary Keyである場合、、、 前回の投稿 INSERT INTO sample (id, `point`, pric…

PostgreSQL のVACUUM実行の指標

SQL

PostgreSQL の VACUUM は、いつやるべきか?不要になったタプルが増えた時に行うべき。 SELECT relname, n_live_tup, n_dead_tup, CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100/(n_live_tup+n_dead_tup) ,2) END AS ratio FROM pg_stat_user_ta…

MySQL のJSON 型を扱うSQL

SQL

サンプル、 DELIMITER // DROP TABLE IF EXISTS sakila.t_item // CREATE TABLE t_item( id INT NOT NULL AUTO_INCREMENT , name VARCHAR(40) NOT NULL , message JSON DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_…

MySQL 複数のTIMESTAMP DEFAULT CURRENT_TIMESTAMP

SQL

昔、MySQL で、複数のTIMESTAMP で DEFAULT CURRENT_TIMESTAMP を宣言しようとして、 DELIMITER // DROP TABLE IF EXISTS branch; // CREATE TABLE branch ( id INT NOT NULL AUTO_INCREMENT , branch_name VARCHAR(64) NOT NULL , created_at TIMESTAMP NOT…

MySQL AUTO_INCREMENTエラーの時のエラーコード

SQL

MySQL AUTO_INCREMENT がMAXを超える時、どんなエラーコードだっけ? と忘れていたので、簡単に用意して実行した結果は、INSERT の発生させる AUTO_INCREMENT のエラー Error Code: 1062. Duplicate entry '2147483647' for key 'PRIMARY' INTのMAX 21474836…

LAST_INSERT_ID() の 0埋め

SQL

随分前に、MySQL の UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT のメモを書いていた。 https://oboe2uran.hatenablog.com/entry/2018/02/01/160016 CREATE TABLE branches ( id INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT , branch_name VARCHAR(60…