devlog

PostgreSQL／(新)連番を前詰に振り直す

2013年10月19日

┌──────────────────────────────────────┐
│UPDATE 表                                                                   │
│    SET 列 = (SELECT COUNT(*)                                               │
│                  FROM 表 AS 別表                                           │
│                  WHERE 別表.列 <= 表.列);                                  │
├──────────────────────────────────────┤
│SELECT setval ('AutoNumber用列名', (SELECT COUNT(*) FROM 表), true);        │
└──────────────────────────────────────┘

以下、検証例。

テスト用データを作成する。
┌──────────────────────────────────────┐
│CREATE TABLE "test"                                                         │
│(                                                                           │
│    "id" bigserial NOT NULL,                                                │
│    "no" integer   NOT NULL,                                      ┌─┬─┐│
│    PRIMARY KEY ("id")                                            │id│no││
│);                                                                ├─┼─┤│
├─────────────────────────────────│ 1│ 1│┤
│INSERT INTO "test" ("no") VALUES (1);                             │ 2│ 2││
│INSERT INTO "test" ("no") VALUES (2);                             │ 3│ 3││
│INSERT INTO "test" ("no") VALUES (3);                             │ 4│ 4││
│INSERT INTO "test" ("no") VALUES (4);                             │ 5│ 5││
│INSERT INTO "test" ("no") VALUES (5);                             │ 6│ 6││
│INSERT INTO "test" ("no") VALUES (6);                             │ 7│ 7││
│INSERT INTO "test" ("no") VALUES (7);                             │ 8│ 8││
│INSERT INTO "test" ("no") VALUES (8);                             └─┴─┘│
└──────────────────────────────────────┘

欠番をつくる。
┌─────────────────────────────────┌─┬─┐┐
│DELETE FROM "test" WHERE "id" = 1;                                │id│no││
│DELETE FROM "test" WHERE "id" = 3;                                ├─┼─┤│
│DELETE FROM "test" WHERE "id" = 5;                                │ 2│ 2││
│DELETE FROM "test" WHERE "id" = 7;                                │ 4│ 4││
└─────────────────────────────────│ 6│ 6│┘
                                                                    │ 8│ 8│
                                                                    └─┴─┘

前詰に更新し直す。
┌─────────────────────────────────┌─┬─┐┐
│UPDATE "test"                                                     │id│no││
│    SET "id" = (SELECT COUNT(*)                                   ├─┼─┤│
│                         FROM "test" AS "as_test"                 │ 1│ 2││
│                         WHERE "as_test"."id" <= "test"."id");    │ 2│ 4││
└─────────────────────────────────│ 3│ 6│┘
                                                                    │ 4│ 8│
                                                                    └─┴─┘

最終連番が「8」のままなので、「4」に戻す。
┌──────────────────────────────────────┐
│SELECT setval ('test_id_seq', (SELECT COUNT(*) FROM "test"), true);         │
└──────────────────────────────────────┘

試しに新たなデータを追加すると、適切に末尾に追加されることが確認できる。
┌─────────────────────────────────┌─┬─┐┐
│INSERT INTO "test" ("no") VALUES (99);                            │id│no││
└─────────────────────────────────├─┼─┤┘
                                                                    │ 1│ 2│
                                                                    │ 2│ 4│
                                                                    │ 3│ 6│
                                                                    │ 4│ 8│
                                                                    │ 5│99│
                                                                    └─┴─┘

分類：PostgreSQL

PostgreSQL／分を時間に換算するSQL文

2013年08月26日

以下のような感じ。
┌──────────────────────────────────────┐
│to_char(to_timestamp(分||'','MI'),'HH24:MI')                                │
└──────────────────────────────────────┘

例えば、150分を2時間30分に換算する場合には以下となる。
┌──────────────────────────────────────┐
│SELECT to_char(to_timestamp(150||'','MI'),'HH24:MI');                →02:30│
└──────────────────────────────────────┘

分類：PostgreSQL

PostgreSQL／パスワードを調べる方法

2013年08月24日

以下のSQL文で。
┌──────────────────────────────────────┐
│SELECT "usename",                                                           │
│       "passwd"                                                             │
│    FROM "pg_shadow";                                                       │
└──────────────────────────────────────┘
但し、md5変換後の値なので(しかも単純に変換しているわけではない)、
元の値はわからない。

何かと同じパスワードに設定していたっけかなの確認ぐらいには使えそう。

分類：PostgreSQL

PostgreSQL／空白除去検索

2013年08月23日

「1 - 1」というデータを「1-1」で検索したい場合。
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表                                                                 │
│    WHERE replace(検索列, ' ', '') = '検索文字';                            │
├──────────────────────────────────────┤
│SELECT 列                                                                   │
│    FROM 表                                                                 │
│    WHERE replace(検索列, ' ', '') LIKE '%検索文字%';                       │
└──────────────────────────────────────┘
それに加えて、「1-1」というデータを「1 - 1」でも検索したい場合。
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表                                                                 │
│    WHERE replace(検索列, ' ', '') = replace('検索文字', ' ' ,'');          │
├──────────────────────────────────────┤
│SELECT 列                                                                   │
│    FROM 表                                                                 │
│    WHERE replace(検索列, ' ', '') LIKE replace('%検索文字%', ' ' ,'');     │
└──────────────────────────────────────┘

分類：PostgreSQL

PostgreSQL／前後のIDを取得

2013年08月14日

昇順の一意ID(列)を基準として、前後のIDを調べる場合のSQL文。

先頭および末尾においては、その先がNULLになるパターン。
┌──┬───────────────────────────────────┐
│前へ│SELECT 列                                                             │
│    │    FROM 表                                                           │
│    │    WHERE 列 < 値                                                     │
│    │    ORDER BY 列 DESC                                                  │
│    │    LIMIT 1;                                                          │
├──┼───────────────────────────────────┤
│次へ│SELECT 列                                                             │
│    │    FROM 表                                                           │
│    │    WHERE 列 > 値                                                     │
│    │    ORDER BY 列 ASC                                                   │
│    │    LIMIT 1;                                                          │
└──┴───────────────────────────────────┘

先頭および末尾においては、一周するパターン。
┌──┬───────────────────────────────────┐
│前へ│SELECT coalesce((SELECT 列                                            │
│    │                     FROM 表                                          │
│    │                     WHERE 列 < 値                                    │
│    │                     ORDER BY 列 DESC                                 │
│    │                     LIMIT 1),                                        │
│    │                (SELECT max(列)                                       │
│    │                     FROM 表));                                       │
├──┼───────────────────────────────────┤
│次へ│SELECT coalesce((SELECT 列                                            │
│    │                     FROM 表                                          │
│    │                     WHERE 列 > 値                                    │
│    │                     ORDER BY 列 ASC                                  │
│    │                     LIMIT 1),                                        │
│    │                (SELECT min(列)                                       │
│    │                     FROM 表));                                       │
└──┴───────────────────────────────────┘

分類：PostgreSQL

PostgreSQL／表結合のUSING句

2013年06月24日

┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表1                                                                │
│        LEFT JOIN 表2                                                       │
│          ON 表2.キー = 表1.キー                                            │
└──────────────────────────────────────┘
こんな場合…
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表1                                                                │
│        LEFT JOIN 表2                                                       │
│          USING (キー);                                                     │
└──────────────────────────────────────┘
こんな風に書ける。

列名が同じなら、USING句が使える(括弧も忘れずに)。

ちなみにこんな風に…
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表1                                                                │
│        LEFT JOIN 表2                                                       │
│          USING (キー1, キー2);                                             │
└──────────────────────────────────────┘
複数指定も可能。

これは、以下相当となる。
┌──────────────────────────────────────┐
│SELECT 列                                                                   │
│    FROM 表1                                                                │
│        LEFT JOIN 表2                                                       │
│          ON  表2.キー1 = 表1.キー1                                         │
│          AND 表2.キー2 = 表1.キー2;                                        │
└──────────────────────────────────────┘
条件が「AND」なのか「OR」なのか一瞬悩んでしまうが
「OR」で結合してもほとんど意味をなさないので(かなり無秩序な結果)、
おのずと「AND」相当だということがわかる(わかってくる)。

分類：PostgreSQL

PostgreSQL／SQL上でトランザクション処理

2013年06月17日

「pgAdmin」の「Query」などからトランザクション処理を再現するには、
以下のようなSQL文を順番に実行すればよい。
┌──────────────────────────────────────┐
│BEGIN;                                                                      │
├──────────────────────────────────────┤
│(SQL文;)                                                                    │
├──────────────────────────────────────┤
│COMMIT;                                                                     │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│BEGIN;                                                                      │
├──────────────────────────────────────┤
│(SQL文;)                                                                    │
├──────────────────────────────────────┤
│ROLLBACK;                                                                   │
└──────────────────────────────────────┘
「BEGIN」でトランザクションが開始して、「COMMIT」または「ROLLBACK」で終了。
もちろん、「COMMIT」はトランザクション中の処理を履行して、
「ROLLBACK」は取り消す。

終了せずに再度開始(「BEGIN」を二回続けて実行など)すると、
以下のようなワーニングメッセージが出るが、問題はないようだ。
┌──────────────────────────────────────┐
│WARNING:  there is already a transaction in progress                        │
└──────────────────────────────────────┘

分類：PostgreSQL

PostgreSQL／pg_dumpのパスワード

2013年06月16日

pg_dumpを実行した直後に以下のようにパスワード入力を促される。
┌──────────────────────────────────────┐
│>pg_dump -U ユーザID データベース名 > バックアップファイル.sql              │
│パスワード:                                                                 │
└──────────────────────────────────────┘

これをバッチで自動化する場合には、パスワード設定ファイルってのを
書き換えればいいらしい。
┌──────────────────────────────────────┐
│>ECHO localhost:5432:*:ユーザID:パスワード>                                 │
│                                          "%APPDATA%\postgresql\pgpass.conf"│
└──────────────────────────────────────┘

ただし、書き換えた後に設定を元に戻した方がいいので、
「pgpass.conf」の中身を控えておいた方がよい。
「%APPDATA%」の部分はWindowsの環境変数として定義してるので、
「echo」コマンドで確認すればよい。
┌──────────────────────────────────────┐
│>echo "%APPDATA%"                                                           │
│C:\Users\xxxx\AppData\Roaming                                               │
└──────────────────────────────────────┘

なお、これを自動化するならば、以下のように「pgpass.bak」(名称は任意)に
退避して戻すって感じか。
┌──────────────────────────────────────┐
│>COPY /Y "%APPDATA%\postgresql\pgpass.conf"                                 │
│                                           "%APPDATA%\postgresql\pgpass.bak"│
├──────────────────────────────────────┤
│>ECHO localhost:5432:*:ユーザID:パスワード>                                 │
│                                          "%APPDATA%\postgresql\pgpass.conf"│
├──────────────────────────────────────┤
│pg_dump -U ユーザID データベース名 > バックアップファイル.sql               │
├──────────────────────────────────────┤
│>MOVE /Y "%APPDATA%\postgresql\pgpass.bak"                                  │
│                                          "%APPDATA%\postgresql\pgpass.conf"│
└──────────────────────────────────────┘

分類：PostgreSQL

PostgreSQL／regexp_replace()の最短一致

2013年06月14日

「regexp_replace()」では基本最長一致で置換される。
「.*」や「.+」の場合、最短一致にするには末尾に「?」を付ければいいようだ。

例えば、LF改行で特定の行を置換するにはこんな感じとなる。
┌──────────────────────────────────────┐
│UPDATE 表 SET 列 = regexp_replace(列,                                       │
│                                  '見出：.*?' || chr(10),                   │
│                                  '見出：'    || chr(10));                  │
└──────────────────────────────────────┘

例えば、CRLF改行の場合はこう。
┌──────────────────────────────────────┐
│UPDATE 表 SET 列 = regexp_replace(列,                                       │
│                                  '見出：.*?' || chr(13) || chr(10),        │
│                                  '見出：'    || chr(13) || chr(10));       │
└──────────────────────────────────────┘

分類：PostgreSQL

前へ 1 2 3 4 5 6 7 8 9 10 11 次へ