Supabase Conditional Upsert with Flutter Client

Question

I want to insert data in case the 'id' doesn't exist, and update it if the 'id' does exist AND the existing 'timestamp' is less than the new 'timestamp'.

So far I haven't figured out how to add this condition to an upsert. Please let me know if you have any pointers.

I know I could do 2 separate queries, an 'insert try' and then an update, but I would much rather get it done in 1 query.

Also, I am aware that I could do this with a raw sql statement, but I don't know how to send this to supabase.

Thanks so much for the help!

Answer 1

this is the solution I came up with. Obviously not what I initially set out to do but works for now:

  Future<bool> sendChanges(String table, dynamic data) async {
    /// insert if row does not exist, update if it does and the local_timestamp
    /// is newer
    /// if the upsert is successful, return true
    try {
      /// add the keyword for the sync_timestamp so the server inserts it
      data['sync_timestamp'] = 'NOW()';

      await supabase.from(table).insert(data);

      return true;
    } catch (error) {
      if (error is PostgrestException && error.code == '23505') {
        /// if error code is 23505 it means the row already exists

        /// update the row in case the timestamp of the change is newer
        await supabase
            .from(table)
            .update(data)
            .eq(columnId, data[columnId])
            .lt(columnLocalTimestamp, data[columnLocalTimestamp]);

        return true;
      } else {
        log('caught error: $error');
        return false;
      }
    }
  }