トリガーエラー「On Insert: 列参照「score」が曖昧です」を解決する
PostgreSQL で発生する "On Insert: column reference "score" is ambiguous" エラーとその解決策
"On Insert: column reference "score" is ambiguous" エラーは、PostgreSQL で INSERT トリガーを作成する際に発生するエラーです。このエラーは、トリガー内で "score" という列を参照しようとしているときに、データベース内に複数の "score" という名前の列が存在する場合に発生します。
エラーの解決策:
このエラーを解決するには、以下のいずれかの方法を実行する必要があります。
列名を修飾する:
トリガー内で "score" という列を参照する場合は、その列がどのテーブルに属しているのかを明確にするために、列名を修飾する必要があります。修飾には、. 演算子を使用します。
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
UPDATE my_table
SET score2 = NEW.score1;
END;
上記の例では、my_table テーブルに score1 と score2 という 2 つの列が存在します。トリガー内で score という列を参照する場合は、my_table.score1 のように修飾する必要があります。
トリガー内で使用する列に別名を設定することもできます。
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
UPDATE my_table
SET new_score = NEW.score;
END;
上記の例では、my_table.score 列に new_score という別名を設定しています。トリガー内で score という列を参照する代わりに、new_score を使用することができます。
トリガーの条件を変更する:
トリガーが実行される条件を変更することで、問題を回避することもできます。
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
WHERE NEW.score IS NOT NULL
BEGIN
UPDATE my_table
SET score2 = NEW.score1;
END;
上記の例では、score 列が NULL ではない場合のみトリガーを実行するように条件を変更しています。
補足:
- 上記の解決策は、PostgreSQL バージョン 9.6 以降で利用可能です。
- PostgreSQL バージョン 9.5 以前では、
ON INSERTトリガー内で列名を修飾する必要があります。
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
exam_type VARCHAR(20) NOT NULL,
score INTEGER NOT NULL
);
CREATE TRIGGER update_score
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.exam_type = 'math' THEN
UPDATE students
SET score = NEW.score * 1.1
WHERE id = NEW.id;
ELSIF NEW.exam_type = 'science' THEN
UPDATE students
SET score = NEW.score * 1.2
WHERE id = NEW.id;
END IF;
END;
This trigger will update the score column of the newly inserted row by 10% if the exam_type is math and by 20% if the exam_type is science.
Here is an example of how to use the trigger:
INSERT INTO students (name, exam_type, score)
VALUES ('Alice', 'math', 100);
INSERT INTO students (name, exam_type, score)
VALUES ('Bob', 'science', 90);
After running these two INSERT statements, the following data will be in the students table:
id | name | exam_type | score
---+-------+-----------+-------
1 | Alice | math | 110
2 | Bob | science | 108
As you can see, the score column for Alice was updated by 10% because her exam_type was math, and the score column for Bob was updated by 20% because his exam_type was science.
I hope this helps!
PostgreSQL で INSERT トリガーを作成するその他の方法
INSERT RETURNING 句を使用すると、INSERT ステートメントによって挿入された行を取得できます。この機能を使用して、トリガー内で挿入された行の値を変更することができます。
CREATE TRIGGER update_score
BEFORE INSERT ON students
FOR EACH ROW
RETURN NEW RETURNING score INTO new_score;
UPDATE students
SET score = new_score * 1.1
WHERE id = NEW.id;
上記の例では、INSERT RETURNING 句を使用して、挿入された行の score 列の値を取得しています。その後、この値を使用して、score 列を 10% 更新します。
INSERT トリガー関数を別に作成して、トリガー内で呼び出すこともできます。この方法は、複雑なロジックをトリガー内に含める必要がある場合に役立ちます。
CREATE FUNCTION update_score_function(score INTEGER, exam_type VARCHAR(20))
RETURNS INTEGER
AS $$
BEGIN
IF exam_type = 'math' THEN
RETURN score * 1.1;
ELSIF exam_type = 'science' THEN
RETURN score * 1.2;
ELSE
RETURN score;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_score
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
UPDATE students
SET score = update_score_function(NEW.score, NEW.exam_type)
WHERE id = NEW.id;
END;
上記の例では、update_score_function という名前のトリガー関数を別途作成しています。この関数は、score 列と exam_type 列の値を受け取り、更新された score 列の値を返します。トリガーは、この関数を呼び出して score 列を更新します。
INSERT トリガーで PL/pgSQL コードを使用することもできます。PL/pgSQL は、PostgreSQL に組み込まれた強力なプログラミング言語です。
CREATE TRIGGER update_score
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
IF NEW.exam_type = 'math' THEN
UPDATE students
SET score = NEW.score * 1.1
WHERE id = NEW.id;
ELSIF NEW.exam_type = 'science' THEN
UPDATE students
SET score = NEW.score * 1.2
WHERE id = NEW.id;
END IF;
END;
上記の例では、PL/pgSQL コードを使用して、exam_type 列の値に基づいて score 列を更新しています。
使用する方法は、要件によって異なります。単純なタスクの場合は、INSERT RETURNING 句を使用するのが最も簡単です。複雑なロジックを必要とする場合は、INSERT トリガー関数を別に作成するか、INSERT トリガーで PL/pgSQL を使用する必要があります。
postgresql
