Question 174245 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search