How to avoid duplicate ID error when inserting

[Takako]

I’m having an issue when I try to insert a new row to a Supabase table from WeWeb workflows.

1. One of my Supabase tables has 1521 records, which are imported from a different database.
2. The id (primary key) of records are not completely consistent, some numbers are lost as some data were deleted before. (e.g., the first row starts with id 79, the last row has id 2032)
3. When I try to insert a new row to the table from WeWeb using “Insert” type action in the Workflow, it tries to add a new row with id “1”. As I also already added an entry with id 1 for testing, it generates an error.
   "duplicate key value violates unique constraint "[tableName]_pkey""

My question is, how can I add a new row with an autoincremented id based on my Supabase table?
I thought of getting the whole rows + checking the id of the last row, but since I added RLS policy on Supabase to limit users to get only the user’s own data, I can’t get the whole table data as a user.

Sorry if my question is confusing, please let me know if anything is unclear.
Hoping any help.

[Broberto]

Do you have the auto increment set up in your Supabase table? Or where does the ID, that is conflicting coming from? Could you show us the setup of your Supabase Action? Just to be sure we get the context.

[Takako]

Hi Broberto, thank you for the reply,

Supabase setting:

Supabase action on WeWeb:

I also run
SELECT setval('[tableName]_id_seq', (SELECT MAX(id) FROM "[tableName]")+1); on Supabase:

Tried to find the value of id it tries to insert, but couldn’t find in Supabase logs, browser console and the network tab

[Broberto]

I’m not sure about this one. But I did something similar with triggers for mixed values in Supabase upsert. What I did was, I set up a trigger before insert that would generate the id (if it was null).

[Takako]

Sorry, I was looking into again after your reply to try triggers and functions,
and found that it seems simply about my configuration.

I run

ALTER TABLE <table_name> 
    ALTER COLUMN id 
    RESTART WITH <id_value_you_want_to_start_from>;

and it solved the id issue.

Thanks regardless for checking my question