SQLの最近のブログ記事

大した話ではないし、PostgreSQL のドキュメントにも記載があることなので、わざわざここでメモるほどのことではないのだが、うっかりエラーを出してドキっとしたので書いておく。

PostgreSQL で、既存テーブルに列を追加し、NOT NULL にする手順。

例えば、hoge テーブルに、chin と man という列を追加するのなら、以下の SQL を実行する。

ALTER TABLE hoge ADD COLUMN chin VARCHAR(1);
ALTER TABLE hoge ADD COLUMN man VARCHAR(1);

これでテーブルの末尾に上記2つの列が追加されるが、NULL も許可する形で作られている。

ADD COLUMN では、

ALTER TABLE hoge ADD COLUMN chin VARCHAR(1) NOT NULL;

というように、CREATE TABLE する時の書式(NOT NULL)が使えない。
後で、SET NOT NULL してやらないといけない。

・・・が、ADD COLUMN した後で、そのまますぐに SET NOT NULL するとエラーになる。

hoge=# ALTER TABLE hoge ALTER COLUMN chin SET NOT NULL;
ERROR:  column "chin" contains null values

「既に NULL 値がセットされちゃってるから、NOT NULL 出来ねえよ。こんにゃろ!」と PostgreSQL に怒られてるわけだ。まあ、そりゃそうだよな。設定とデータの中身がいきなり不整合ってことになっちゃうからな(^^;

そこで、一旦、

UPDATE hoge SET chin='0', man='0';

という SQL を実行して、追加した列に値(例 '0')をセットする。
これで、

ALTER TABLE hoge ALTER COLUMN chin SET NOT NULL;
ALTER TABLE hoge ALTER COLUMN man SET NOT NULL;

を実行しても、エラーは出ず、ちゃんとテーブルに NOT NULL 項目として追加される。

ああ、面倒くさい(^^;

UNION で結合する表内で OREDER BY が使えない話なのだが。

ああ・・・今、ふと思ったが、単に ORDER BY したものをサブクエリにすればいいだけだ・・・

SELECT * FROM (SELECT * FROM table1 WHERE d1='B' ORDER BY d2) T1
UNION
SELECT * FROM (SELECT * FROM table1 WHERE d1='A' ORDER BY d2) T2
UNION
SELECT * FROM (SELECT * FROM table1 WHERE d1='C' ORDER BY d2) T3

ま、もう、プログラム内であれこれしてしまったので良いのだが。

ちなみに、UNION で結合する SELECT 文のサブクエリにはちゃんと名前をつけないと 'Every derived table must have its own alias' のエラーが出てしまうのでご注意を。

ありり?

UNION で結合する SELECT 文では ORDER BY って使えんのやったっけ?
エラーにはならないのだが、まったく無視されてるな・・・(^^;
もちろん、SELECT 文単体や、UNION した表全体に対する ORDER BY は問題なく使えるのだが。

UNION の場合、重複行を取るためのソートが表全体に対して行われるわけで、それで各表の ORDER BY は無視されるのかと思いきや、UNION ALL でも一緒やね。

はは。日頃 UNION 使わないもんで(^^;、細かい仕様知らなんだ。

A 003
A 001
A 002
B 102
B 101
C 310
C 320
...

みたいなテーブルから抽出したレコードを

B 101
B 102
A 001
A 002
A 003
C 310
C 320

みたいに並べたいんで(最初の項目の名前をd1、2番目の項目をd2とした場合)、

(SELECT * FROM table1 WHERE d1='B' ORDER BY d2)
UNION
(SELECT * FROM table1 WHERE d1='A' ORDER BY d2)
UNION
(SELECT * FROM table1 WHERE d1='C' ORDER BY d2)

としてたんだけど、

B 102
B 101
A 003
A 001
A 002
C 310
C 320

としか出んね。(テーブルに格納されている順番そのままに出てるだけ)

取りあえずソースが冗長化して格好悪いんだけど、来週にはテストをしたいんで、それぞれ個別に SELECT 実行してプログラム内で結合するようにするわ。

識者の方、良い SQL 文をご存じであればご教示ください。

画面から入力した顧客番号と担当者CDをもとに HIGEHOGE_TBL を検索し、レコードが存在していれば UPDATE し、存在していなければ INSERT をする SQL。

MERGE で書いてみた。

MERGE INTO
    HOGEHOGE_TBL A
    USING (
        SELECT
            KYAKU_NO,
            TANTOU_CD
        FROM
            HOGEHOGE_TBL
        WHERE
            KYAKU_TNO = '画面.顧客番号' AND
            TANTOU_CD = '画面.担当者CD'
        ) B
    ON (
        A.KYAKU_NO = B.KYAKU_NO AND
        A.TANTOU_CD = B.TANTOU_CD
        )
WHEN MATCHED THEN
    UPDATE SET
        MEMO = '画面.メモ',
        U_DATE = TRUNC(SYSDATE)
WHEN NOT MATCHED THEN
    INSERT
        (
        KYAKU_NO,
        TANTOU_CD,
        MEMO,
        I_DATE,
        U_DATE
        )
    VALUES
        (
        '画面.顧客番号',
        '画面.担当者CD',
        '画面.メモ',
        TRUNC(SYSDATE),
        TRUNC(SYSDATE)
        )

そしたら、一緒に仕事をしている若者から指摘された。

「これじゃあ、USING のところで検索結果が空のときに、'画面.顧客番号'と'画面.担当者CD' が ON 条件で使われないので駄目っすよ。必ず '画面.顧客番号'と'画面.担当者CD' が返るように組まないと駄目っす。」と。

なるほど。言われてみればそのとおり。

正解は、

USING (
    SELECT   
        '画面.顧客番号' KYAKU_NO,
        '画面.担当者CD' TANTOU_CD
    FROM   
        DUAL
    ) B
ON (
    A.KYAKU_NO = B.KYAKU_NO AND
    A.TANTOU_CD = B.TANTOU_CD
    )

または、いっそのこと、

USING
    DUAL
ON (
    A.KYAKU_TNO = '画面.顧客番号' AND
    A.TANTOU_CD = '画面.担当者CD'
    )

でもいいかな?

・・・こうして、世代交代が進んでいく。
(つか、MERGE 使うの初めてなんで許してくれ(^^;)

このアーカイブについて

このページには、過去に書かれたブログ記事のうちSQLカテゴリに属しているものが含まれています。

前のカテゴリはPostgreSQLです。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

2013年2月: 月別アーカイブ

月別 アーカイブ

電気ウナギ的○○ mobile ver.

携帯版「電気ウナギ的○○」はこちら