Zenn
😇

Exception: PostgrestException(message: there is no unique or exclusion

2024/02/12に公開
Flutter
PostgreSQL
Dart
Supabase
tech

💡Tips

最近業務で、Supabase使っているのですが、まだ知らない部分が多くエラーにハマるたびにどうすればいいのか調べながら解決しています。よくあるエラーは、ポリシーとかローレベルセキュリティの許可だったりしますが、今回はまた別問題が出てきました???

inseart文を使いたいのだけど、これだと追加するたびに新しいユーザーのデータが追加されていくので、upsertを使えばデータがあったら上書きしてくれるらしく試してみた。実際はならなかったけど💦

🐘SQLの知識がいるのか?

こんなエラーに遭遇した😱

例外が発生しました _Exception (Exception: PostgrestException(message: there is no unique or exclusion constraint matching the ON CONFLICT specification, code: 42P10, details: Bad Request, hint: null))

Github Copilotに聞いてみた!

このエラーは、upsertメソッドのonConflictパラメータで指定したフィールドに対して一意性制約（unique constraint）または排他制約（exclusion constraint）が設定されていない場合に発生します。onConflictパラメータは、データの競合が発生した場合にどのフィールドを基準にデータを更新するかを指定しますが、そのフィールドには一意性制約または排他制約が設定されている必要があります。

この問題を解決するためには、データベースのt_userテーブルでuser_nameフィールドに一意性制約を設定する必要があります。一意性制約を設定すると、そのフィールドの値はテーブル内で一意であることが保証されます。

一意性制約の設定方法は、使用しているデータベース管理システムによります。PostgreSQLを使用している場合、以下のSQLコマンドでuser_nameフィールドに一意性制約を設定できます：

ALTER TABLE t_user ADD CONSTRAINT user_name_unique UNIQUE (user_name);

SQL Editorで実行するクエリを実行する。

実行したらこのコードが使えた。分岐処理で、inseartとupadateにした方が良いのかもしれない？
upsertのコード:

Future<void> createUserOrUpdate(
    String userName,
    String dateText,
    String iconImagePath,
    String profile,
    String jobId,
  ) async {
    try {
      final jaUtc = DateTime.now().toUtc().toIso8601String();
      await SupabaseInstance().supabase.client.from('t_user').upsert(
        {
          'user_name': userName,
          'birthday': dateText,
          'iconImagePath': iconImagePath,
          'job_id': jobId,
          'profile': profile,
          'created_at': jaUtc,
          'updated_at': jaUtc,
          'is_delete': false,
        },
        onConflict: 'user_name',
      );
    } on Exception catch (e) {
      logger.d('😇createUserOrUpdate error: $e');
      rethrow;
    }
  }

inseartのコードはこちら:

Future<void> createUser(
    String userName,
    String dateText,
    String iconImagePath,
    String profile,
    String jobId,
  ) async {
    try {
      // 日本時間に対応するため、toUtc()を使用。９時間ずれないように対策
      final jaUtc = DateTime.now().toUtc().toIso8601String();
      await SupabaseInstance().supabase.client.from('t_user').insert({
        'user_name': userName,
        'birthday': dateText,
        'iconImagePath': iconImagePath,
        'job_id': jobId,
        'profile': profile,
        'created_at': jaUtc,
        'updated_at': jaUtc,
        'is_delete': false,
      });
    } on Exception catch (e) {
      logger.d('😇createUser error: $e');
      rethrow;
    }
  }

保存はできたが、追加されていく問題は解決できなかった🫠
他の方法を考えよう🤔

まとめ

Firestoreだと、.doc(uid).set()で簡単に特定のユーザーのデータを１個だけ追加できるが、Supabaseだと何個も追加されてしまう。解決策を今考えるところです。今回はlogに出てきたエラーの解決策を見つけただけで終わってしまいました。

PostgreSQLの公式にも参考になりそうな情報が記載されておりました。
https://www.techonthenet.com/postgresql/unique.php

一意の制約の作成 - ALTER TABLE ステートメントの使用

PostgreSQL で ALTER TABLE ステートメントを使用して一意制約を作成する構文は次のとおりです。

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Discussion