Supabase Conditional Upsert with Flutter Client

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!

I ran into the same problem. I guess there is no elegant way achieve this. I also thought of raw SQL but it isn't supported by supabase_flutter. What you're trying to do is conditional chaining. In the supabase docs I found this example regarding the JavaScript API:

> Filters can be built up one step at a time and then executed. For
> example:
>
> const filterByName = null
> const filterPopLow = 1000
> const filterPopHigh = 10000
>
> let query = supabase
> .from('cities')
> .select('name, country_id')
>
> if (filterByName) { query = query.eq('name', filterByName) }
> if (filterPopLow) { query = query.gte('population', filterPopLow) }
> if (filterPopHigh) { query = query.lt('population', filterPopHigh) }
>
> const { data, error } = await query

You could do that similarly in Dart.
I hope this helps.

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;
      }
    }
  }