PostgreSQLのJSONB型カラム内のキーにユニーク制約をつけてUPSERTする方法 - ほんじゃらねっと
Node.jsとPostgreSQLを使ってるなら、 もうデータは全部JSON形式で管理した方が楽なんじゃないの、 ということでPostgreSQLのJSON型カラムについて色々調べている。 JSON/JSONB型カラムの基本的な使い方についてはちょっと前に書いた。 blog.honjala.net 今回はJSONB型カラムに一意制約をつけたり、 それを利用してUPSERT処理を行う方法について調べたことを書く。 準備 前回書いたUPSERT記事と同じようなことがしたいので、 blog.honjala.net 下記のような商品データ用テーブルを用意する。 id列以外の情報はJSONB型カラムに入れちゃう想定。 drop table if exists upsert_products_js; create table upsert_products_js( id serial ,jsdoc
PostgreSQLでのUPSERT(INSERT or UPDATE)処理を検証する - ほんじゃらねっと
PostgreSQLでUPSERT（データがあればUPDATEしてなければINSERT）する 方法について調べたところ、いくつか書き方があるようだ。 UPSERT処理で気になるのは同時実行された際に 重複登録されたり重複エラーが発生しないかどうかなので、 その観点で下記の4パターンの方法を検証してみる。 UPDATEしてNOT FOUNDならINSERT CTEでUPDATEしてNOT EXISTSならINSERT INSERT ON CONFLICT DO UPDATE INSERTしてUNIQUE_VIOLATION EXCEPTIONならUPDATE 準備 下記の様な商品テーブルを用意する： drop table if exists upsert_products; create table upsert_products( id serial ,item_cd varchar(10
PostgreSQLでpg_bigmを使って中間一致like検索を高速化する - ほんじゃらねっと
昨日書いた下記記事の続き。 blog.honjala.net 全角半角大文字小文字ひらがなカタカナを区別せず高速検索できるようには なったのだけど、肝心のlike検索でインデックスが使えず 遅いままだったので、pg_bigmを導入してこれを解決する。 pg_bigmは簡単に言うと日本語の中間一致like検索でインデックスを 使えるようにしてくれるPostgreSQL用モジュール。 本家サイトはこちら： pg_bigm pg_bigmの導入方法と使い方についてはこちらのSlideShareを参考にした： PostgreSQLでpg_bigmを使って日本語全文検索 （MySQLとPostgreSQLの日本語全文検索勉強会 発表資料） from NTT DATA OSS Professional Services www.slideshare.net pg_bigmを導入する pg_bigmはR
PostgreSQLで全角半角大文字小文字ひらがなカタカナを区別せず検索したい！というよくあるわがままに応える - ほんじゃらねっと
したいしたい！絶対したい！と駄々をこねられたので調査してみた。 こういった区別なし検索を実装する方法としてパッと思いつくのは、 あらかじめ検索対象となるカラムの検索用カラムを用意して、 データ変更時にトリガーで 元カラムの内容を半角小文字英数字カタカナに変換したデータが入るようにしておき、 検索時はその検索用カラムを使用する、という方法。 これはめんどくさそうだ。 SQL Serverは照合順序の設定で制御できるらしい。 照合順序と Unicode のサポート PostgreSQLも同じことができないかと調べてみたけど、対応してなさそう。 第22章 多言語対応 他に方法がないか調べてみると、 「式インデックス」を使って、自作の変換用関数で変換したデータを インデックスに登録しておく方法を試しているページがあった。 PostgreSQLで全角半角を区別しない問い合わせ この方法なら少なくとも
PostgreSQLでテスト用組み合わせパターンデータを一括生成する - ほんじゃらねっと
先日書いたgenerate_series関数を使用したテストデータ生成の続き。 blog.honjala.net どうせテストデータを作成するなら、 連番だけでなく、必要なパターンの組み合わせデータを生成したい。 ちょっと前にPythonやClojureを使った組み合わせデータ作成のスクリプトを 作成した時は専用のライブラリを使用したのだけど、 blog.honjala.net PostgreSQLのgenerate_series関数とjoinを組み合わせたら ぐっと簡単に実現できた。 サンプル まずはシンプルに値の組み合わせデータを作成してみる。 select x ,y ,z from generate_series(1, 3) as x ,generate_series(0, 1) as y ,generate_series(1, 2) as z ; 結果はこうなる。 x | y |
JSONB型カラムでPostgreSQLをNoSQL風にスキーマレスに使う方法 - ほんじゃらねっと
PostgreSQL9.2以降のバージョンではjson型/jsonb型カラムが使える。 ちょっと特殊な記法を使うけど、 json/jsonbデータ内のキーをDBのカラムと同様に使えるようになるので、 スキーマレスDBのように同じテーブル内のレコードで 異なるデータ構成を持たせることができるようになる。 役に立ちそうなので触ってみた。 環境はPostgreSQL9.5。 公式ドキュメントのjson/jsonb型カラムに関する情報は下記のページに記載されている。 8.14. JSONデータ型 9.15. JSON関数と演算子 9.20. 集約関数 JSONB型カラムを持つテーブルを作成する サンプルとして商品情報を登録するテーブルを作成してみる。 JSON系のカラム型はJSON型とJSONB型カラムがあるけど、 公式ドキュメントによると、登録するJSONデータのマップキーの順序を どうしても守
PostgreSQLのgenerate_series関数でテスト用データを作成する - ほんじゃらねっと
これまでテスト用にたくさんデータを作成する時は、 PythonやらNode.jsやらでプログラムを書いてた。 blog.honjala.net PostgreSQLならgenerate_seriesという連続値生成関数を利用することで SQLだけで手軽にデータ生成できる、ということを発見したので、 今回は上記の記事と同じようなテストデータをSQLで作成してみる。 環境はPostgreSQL 9.5。 下記のようなテーブルを作成する。 drop table if exists bulk_test_items; create table bulk_test_items( id serial not null, item_cd varchar(100), name varchar(100), created timestamp default current_timestamp, primary
PostgresqlのSELECT句での関数呼び出し順序を確認した - ほんじゃらねっと
チーム内の雑談で、 Delete-Insert処理を1つのSQL文で行うとしたら、 SELECT文のSELECT句でDELETE用SPとINSERT用SPを呼び出す方法もあるよね、 SELECT句って記述順に実行してくれるのかな？ みたいな話になったので、検証してみた。 検証してみる 検証環境はCentOS環境上のPostgreSQL 9.5。 SPを使うのはちょっと面倒なので、 タイムスタンプを取得する関数で試してみる。 current_timestampやnow()は何度呼び出してもトランザクション開始時の時刻を 返してくれちゃうので、実際の現在時刻を返すclock_timestamp()を使う。 9.9. 日付/時刻関数と演算子 select clock_timestamp() as x, clock_timestamp() as y; 結果： x | y -------------
PostgreSQL9.5の強力なJSON関数で、ネストした構造を持つJSONを自在に組み立てる - FOOBAR's Blog
(この記事はmixi advent calendar5日目の投稿です。) 年内にリリース予定のPostgreSQL9.5ではjsonb型のデータを操作する関数が拡充され、より自由自在にJSONを扱うことが可能となります。この記事ではPostgreSQL9.5を用い、REST APIのJSON生成部分を SQLのみで 実現するサンプルコードを紹介することで、「なにこれ、こんなことできるならORM使う必要なくなるじゃん」みたいな感じで皆さんに驚いてもらうことを目的としています。[1] 以下、次に示すスキーマを持つブログサービスのREST APIを題材として話を進めます。 create table "User" ( id varchar(10), name varchar(20) ); create table "Post" ( id int, user_id varchar(10), cont
【9.5新機能チェック】BRINインデックス, Part 1: BRINインデックスとは何か、その仕組みを探る
【9.5新機能チェック】BRINインデックス, Part 1: BRINインデックスとは何か、その仕組みを探る 既に試してみている方もおられるかと思いますが、7月2日にPostgreSQL 9.5 alpha1 がリリースされました。 PostgreSQL: PostgreSQL 9.5 Alpha 1 Released PostgreSQL 9.5 Alpha 1登場 | マイナビニュース PostgreSQL 9.5にはいろいろと新しい機能が追加されていますが、その中に「BRINインデックス」という機能があります。 最近、人と話すと「BRINってどうなのよ？」と話題になることが増えており、また直近では情報系システムのプロジェクトに参加することが多く、個人的にいろいろと期待している機能の一つだったりします。 というわけで、今回から3回連続で、この「BRINインデックス」について、その仕組
PostgreSQLでテーブルの行数を速く取得する方法 - 雷鳴の日記
まず、 create table test (id serial primary key, data text); でテーブルを作成する。 そして、データをインポートする。 カウント関数を呼び出す lenovo=# select count(*) from test; count - 4194304 (1 row) Time: 1040.870 ms 遅いじゃないか ネットで調べて、いろんな方法がありますが、triggerを使うとか。 面倒くさいと思います。 ほかの方法、pg_classを利用する。試してみると、 lenovo=# select reltuples::integer from pg_class where relname='test'; reltuples - 4194304 (1 row) Time: 14.990 ms 正確じゃん。 でも… lenovo=# inser
Are PostgreSQL functions transactional?
Is a PostgreSQL function such as the following automatically transactional? CREATE OR REPLACE FUNCTION refresh_materialized_view(name) RETURNS integer AS $BODY$ DECLARE _table_name ALIAS FOR $1; _entry materialized_views%ROWTYPE; _result INT; BEGIN EXECUTE 'TRUNCATE TABLE ' || _table_name; UPDATE materialized_views SET last_refresh = CURRENT_TIMESTAMP WHERE table_name = _table_name; RETURN 1; END
インデックスを作成して，SQLの速度をチューニングする手順 （PostgreSQLで，EXPLAIN文とCREATE INDEX文によるパフォーマンス改善） - 主に言語とシステム開発に関して
以下の５ステップで，適切なインデックスを作成し，SQLを高速化できる。 （１） パフォーマンスを改善すべきSQL（もしくはカラム）を特定 （１−１） ログを閲覧し，実行秒数の大きいものを抽出する。 （１−２） 統計テーブルを閲覧し，よく利用されるテーブルを特定する。 （２） 該当SQLのプランやコストを確認 （３） 該当カラムに対してインデックスを作成 （４） インデックスが作成されたことを確認 （５） SQLのプランやコストが改善されたことを確認 補足 ※↑ もくじジェネレータ で自動生成 DBはPostgreSQLを想定。 （１）パフォーマンスを改善すべきSQL（もしくはカラム）を特定 まず，インデックスを作成すべきカラムを見極める。 その方法は２つある。 （１−１）ログを閲覧し，実行秒数の大きいものを抽出する。 SQLの実行ログを閲覧する。 たとえば，Ruby on Railsなら，
PostgreSQL の VACUUM をなんとなくでするのはやめよう
はじめての人もそうでない人もはじめまして。 河野と申します。 いきなりすいません･･･。 私の名前をさっそく覚えていただいた方には申し訳ないのですが、 弊社にはもう一人河野というものがおり、そっちとは違う方と覚えて頂けると溜飲が下がります…。 さて、今回 Fusic Advent Calendar の一番槍として最初に寄稿させて頂くことになりました。 本日はお題の通り PostgreSQL の VACUUM をなんとなくでするのはやめようという提案を、全国 4,000万の VACUUM ファンの皆様にしたいと思います。 尚、PostgreSQLの対応バージョンは 8.3 以降となります。 PostgreSQL のメンテナンスと VACUUM データベースに PostgreSQL を採用している会社はどのくらいあるのでしょう？ オープンソースのデータベースでは MySQL が多く採用されてい
PostgreSQL COALESCEでNULLを空白に変換 - 130単位
PostgreSQL NULL同士をイコールとして比較したい場合 - 130単位 この記事の続きです。 記事の最後で空白とNULLの比較について少し触れました。が、その方法はまだ調べてすらいなかったため、次回のネタにしようと考えていました。 そしたら、試すよりも前にトラックバック先で教えていただきました。ありがとうございます。 COALESCE関数 coalesce()を使ってNULLを空白に変換してから比較してみてはどうでしょうか。比較対象が文字列型であれば、 coalesce(a.pref, '') = coalesce(b.pref, '') のようになると思います。 IS NOT DISTINCT FROM - iakioの日記 - postgresqlグループ COALESCE関数は、引数の中でNULLでない最初の値を返すものみたいです。これを用いることによって、イコール演算子で
CentOS 5にPostgreSQLをインストールする - 祈れ、そして働け ～ Ora et labora
概要 CentOS 5にPostgreSQL 9.1.2をインストールします。 構成 CentOS release 5.7 (Final) PostgreSQL 9.1.2 インストール済みパッケージの確認 インストール済みのPostgreSQLがないか確認します。 # psql --version-bash: psql: command not foundインストール済みの関連パッケージも検索しておきます。 # rpm -qa | grep postgresqlpostgresql-libsが見つかりました。 postgresql-libs-8.1.23-1.el5_7.3 postgresql-libs-8.1.23-1.el5_7.3Name : postgresql-libs Arch : x86_64 Version : 8.1.23 Release : 1.el5_7.3 Si
CentOS で PostgreSQL を使ってみよう！(3) — Let's Postgres
Appendix A: PostgreSQL のログ デフォルトでは、データベースクラスタの「pg_log」というディレクトリに、起動した日の曜日名のついたファイルを作ってログを書き込んでいます。 [root @server1 ~]# ls /var/lib/pgsql/data/pg_log/ postgresql-Tue.log しかし、「そのログがいつ記録されたか」という必須情報が、デフォルトの設定では出力されません。デフォルトでは、たとえば存在しないテーブルを参照する "SELECT * FROM abc;" という SQL を実行すると、エラーになりますが、 ERROR: relation "abc" does not exist というような形で記録され、エラー発生日時を確認できません。これを、記録日時が確認できるように設定してみましょう。 ★ 例：ログ記録日時の出力を設定する
Ludia (PostgreSQL + Senna) で全文検索
PostgreSQL: CREATE TABLE ludia_test ( id serial primary key, name text, contents text ); CREATE INDEX fulltext_contents_index ON ludia_test USING fulltext(contents); MySQL: CREATE TABLE ludia_test ( id integer not null auto_increment primary key, name text, contents longtext ); CREATE FULLTEXT INDEX fulltext_contents_index ON ludia_test(contents); MySQLでは、text型ではおさまりきらない作品もあったので、contentsをlongtext型
リリース、障害情報などのサービスのお知らせ
最新の人気エントリーの配信
j次のブックマーク
k前のブックマーク
lあとで読む
eコメント一覧を開く
oページを開く
Generate Data For Testing With PostgreSQL
http://yaplog.jp/coconai/archive/51
