PostgreSQLクエリ結果が期待結果通りにならないようにするためのELSE構文の考慮点

お疲れ様です。中塚です。

SQLを扱う際には、クエリ結果がnullになるケースを適切に処理することが重要です。特に、条件のカラムが想定しないデータになる可能性がある場合、IF ELSE構文を使用する際に注意が必要です。この記事では、クエリの境界値的な考慮について説明します。

※　随時更新：2024/07/17

問題の背景

IF ELSE構文を使用する際、たとえば理論的には全ての条件をカバーしている場合、最後のELSEは不要に思えるかもしれません。しかし、条件のカラムに想定しないデータが含まれた場合、この仮定は成立しません。例えば、以下のようなクエリを考えてみます。

SELECT 
    CASE 
        WHEN sum >= 90 THEN 10
        WHEN sum >= 80 THEN 20
        WHEN sum < 80 THEN 30
    END AS score
FROM students;

このクエリでは、sumカラムの値に基づいてscoreカラムに値を割り当てています。
一件、境界値的にはすべてのパターンを網羅しているように見えます。
しかし、全ての可能な条件を網羅しているわけではありません。例えば、sumカラムの値がNULLの場合など実装者の意図せず条件に一致しない場合に、scoreカラムはNULLとなります。TypeScriptではscoreカラムに対してnumber型を期待している場合、NULLが返されると型エラーを引き起こす可能性があります。

解決方法

この問題を解決するためには、ELSE句を追加してデフォルト値を設定することが必要です。ELSE句を使用することで、どの条件にも一致しない場合のデフォルト値を明示的に指定することができます。以下に修正されたクエリの例を示します。

SELECT 
    CASE 
        WHEN sum >= 90 THEN 10
        WHEN sum >= 80 THEN 20
        WHEN sum < 80 THEN 30
        ELSE 0 -- デフォルト値を設定
    END AS score
FROM students;

この修正により、sumカラムの値がNULLの場合や他の条件に一致しない場合でも、scoreカラムにはデフォルト値として0が設定されます。

TypeScriptでは、クエリ結果がNULLである場合の処理を以下のように行います。

interface Student {
    score: number;
}

async function getStudentsScores(): Promise<Student[]> {
    const result = await client.query<Student>(`
        SELECT 
            CASE 
                WHEN sum >= 90 THEN 10
                WHEN sum >= 80 THEN 20
                WHEN sum < 80 THEN 30
                ELSE 0 -- デフォルト値を設定
            END AS score
        FROM students;
    `);

    return result.rows;
}

async function main() {
    try {
        const students = await getStudentsScores();
        students.forEach(student => {
            console.log(`Score: ${student.score}`);
        });
    } catch (error) {
        console.error('Error fetching student scores:', error);
    }
}

main();

この例では、PostgreSQLのクエリでNULL値を適切に処理し、TypeScriptでの型エラーを回避しています。

結論

PostgreSQLのCASE文を使用する際、ELSE句を追加してデフォルト値を設定することは重要です。これにより、条件に一致しないデータやNULL値が存在する場合でも、期待されるデータ型を保つことができます。特にTypeScriptのような静的型付け言語を使用する場合、NULL値による型エラーを防ぐために、このような対策を講じることが求められます。
また、ELSE文は今回のケースだけでなく必ず例外が存在するものとして仕様を検討しておくことが必要と感じました。

何卒宜しくお願い致します。