できない.dev

PostgreSQL で「relation does not exist」になる（テーブルはあるのに見つからない）

PostgreSQL は引用符で囲まない識別子を小文字へ畳み込むため、大文字を含む名前で作ったテーブルは同じ綴りでは引けない。
もう一つの典型は search_path に対象スキーマが入っていないケースである。

#postgresql#relation#search-path#schema#identifier

公開:

要約

作ったはずのテーブルが引けない。

CREATE TABLE "userProfile" (id int);
SELECT * FROM userProfile;
ERROR:  relation "userprofile" does not exist
LINE 1: SELECT * FROM userProfile;
                      ^

エラーメッセージをよく見ると、書いた名前は userProfile なのに、探しに行っているのは userprofile である。
PostgreSQL は 引用符で囲まれていない識別子をすべて小文字へ畳み込むためにこうなる。
作成時にダブルクオートで囲んだ名前だけが大文字を保持し、参照時に引用符を外すと別の名前として扱われる。

もう一つの典型が search_path である。
テーブルは実在するのに、現在のセッションが探しに行くスキーマの一覧に入っていない、という形で同じエラーが出る。

よくある原因

  1. 大文字を含む識別子を引用符付きで作った: Lexical Structure（新しいタブで開く） が定義しているとおり、引用符の無い識別子は小文字化される。
    ORM やスキーマ管理ツールが自動的にダブルクオートを付けて CREATE TABLE "UserProfile" を発行していると、手で書いた SQL から引けない状態が生まれる。
  2. 対象スキーマが search_path に無い: app.users のように public 以外へ置いた場合、修飾なしの users は解決されない。search_path の既定は "$user", public なので、独自スキーマは明示的に足す必要がある。
  3. 接続先が違う: psql -d mydb のつもりが既定のデータベースに繋がっている、あるいはアプリの接続文字列が別環境を向いている。
    テーブル名ではなく接続を疑うべきケースである。
  4. コミットされていない／一時テーブル: 別セッションで CREATE TABLE したままコミットしていなければ、他のセッションからは見えない。CREATE TEMP TABLE はセッション固有のスキーマに作られるため、接続を張り直した時点で消える。
  5. マイグレーション未適用: 開発環境でだけ動いていて本番で落ちる場合はこれが多い。
    アプリのコードだけが先に進み、スキーマ変更が反映されていない。

解決策

1. 識別子は小文字スネークケースに統一する

そもそも引用符を使わない設計にするのが根本解決である。
すべて小文字なら、書き方に関係なく同じ名前へ解決される。

CREATE TABLE user_profile (id int);
SELECT * FROM USER_PROFILE;  -- 小文字化されるので同じテーブルに当たる

ORM を使っているなら、テーブル名・カラム名の命名規約を snake_case に寄せる設定があるか確認する。

2. 既に大文字で作った場合は参照側も囲む

作り直せない場合は、参照するすべての箇所でダブルクオートを付ける。

SELECT * FROM "userProfile";

囲み忘れが一箇所でもあると同じエラーが再発するため、長期的には次のように改名しておくほうが安全である。

ALTER TABLE "userProfile" RENAME TO user_profile;

3. search_path を通すか、スキーマ修飾して書く

Schemas（新しいタブで開く） の説明どおり、修飾なしの名前は search_path の順に探索される。
まず現在値を確認する。

SHOW search_path;

セッション限りで足すなら SET、そのロールの既定にするなら ALTER ROLE を使う。

SET search_path TO app, public;
ALTER ROLE myapp SET search_path TO app, public;

search_path を変えると他のクエリの解決先も変わる。
影響範囲を狭く保ちたい場合は、SELECT * FROM app.users; のように毎回修飾して書くほうがよい。

4. 実体の在り処を確認する

推測で直す前に、どのスキーマに何があるかを見る。

\dt *.*

psql を使えないなら、カタログを直接引く。

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE '%user%';

ここで 1 行も返らなければ、原因は名前ではなく「そもそも存在しない」（原因 3 から 5）である。
行が返るのに引けないなら、スキーマか大文字小文字の問題（原因 1 か 2）に絞り込める。

この記事は役立ちましたか？