PostgeSQL 複数列のNULL値を一斉にデフォルト値に変更する表現について

Question

実現したいこと

例えば以下の表について、NULL値である箇所をすべて0に変換した文をSELECTしたいです。

<例>　成績テーブル

//学校名 | 生徒名 | 試験結果_国語 | 試験結果_数学 | 試験結果_英語
// -------+-------+----------------+----------------+----------------
// A校　|　田中　|　　　90　　　| 　　　　　　　|
// A校　|　鈴木　|　　　85　　　| 　　　65　　　|　　　30
// B校　|　佐藤　|　　　　　　　 | 　　　30　　　|　　　45

現在はこのように記述していますが、もっと簡潔な書き方があれば知りたいです。

SELECT 学校名, 生徒名, 
            COALESCE(試験結果_国語, 0),
            COALESCE(試験結果_数学, 0),
            COALESCE(試験結果_英語, 0)
FROM 成績

よろしくお願いいたします。

Comments

[hoshi-takanori]

そう書くしかないと思います。カラムが増えた時に面倒なのは正規化されてないからでしょうね…。

[inup_inup]

回答ありがとうございました。
参考にさせていただきます。

Answer 1

もっと簡潔な書き方があれば知りたいです。

一番簡潔な書き方だと思います。

ただ、Null値は試験を受けなかったという意味があるのではないですか？
Nullを0に置き換えると、0点の場合と区別がつかないですよね。
そうではなくて、0点がNullになっているという事なら、値を正しく補正するべきでしょうね。

Comments

[inup_inup]

ありがとうございました。
たとえありきの疑問ではなかったので表の意味をかんがえていませんでしたが、
確かにNULLと0の違いについては意識しなければなりませんね。

Answer 2

簡潔な書き方とは言えませんが、function を定義する方法もあるかと思います。

SQL Fiddle

CREATE TABLE 成績(
  学校名 VARCHAR(10),
  生徒名 VARCHAR(10),
  試験結果_国語 int,
  試験結果_数学 int,
  試験結果_英語 int
);

INSERT INTO 成績 VALUES('A校', '田中', 90, null, null);
INSERT INTO 成績 VALUES('A校', '鈴木', 85, 65, 30);
INSERT INTO 成績 VALUES('B校', '佐藤', null, 30, 45);

CREATE OR REPLACE FUNCTION null_to_zero()
RETURNS SETOF 成績 AS
$$
DECLARE sql_stmt TEXT;
BEGIN
  SELECT FORMAT('SELECT 学校名, 生徒名, %s FROM 成績',
                STRING_AGG(FORMAT('COALESCE(%s, 0)', COLUMN_NAME), ','))
  INTO sql_stmt
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'public' AND TABLE_NAME = '成績' AND
        COLUMN_NAME like '試験結果_%'; 

  RETURN QUERY EXECUTE sql_stmt;
END;
$$
LANGUAGE plpgsql VOLATILE;

-- query
SELECT * FROM null_to_zero();

Comments

[inup_inup]

回答ありがとうございました。
参考にさせていただきます！