S
Supabase•3y ago
jkohlin

UPSERT and autogenerated ID (primary key)

I have an HTML form with 3 rows. I have 2 saved records in the table (user didn't fill in all) The next time the user logs in, he continues to fill out the form, which is pre filled with the previous 2 records from the db. - He edits one row - Fills in the last row, and submits. How can I: - save the new row, - update the edited one - ignore the unedited row ...without adding duplicates I tried this: 
await supabase
.from('notes')
.insert([
        {
            text: 'This is a test',
            user_id: '123',
            form_row: 1,
        },
        {
            text: 'This is another test',
            user_id: '123',
            form_row: 3,
        },
    ],
    { 
        upsert: true, 
        onConflict: 'id', 
        ignoreDuplicates: false 
    })
.select()
await supabase
.from('notes')
.insert([
        {
            text: 'This is a test',
            user_id: '123',
            form_row: 1,
        },
        {
            text: 'This is another test',
            user_id: '123',
            form_row: 3,
        },
    ],
    { 
        upsert: true, 
        onConflict: 'id', 
        ignoreDuplicates: false 
    })
.select()
but get this error : null value in column "id" of relation "notes" violates not-null constraint id is auto increment Can I use UPSERT this way?
2 Replies
garyaustin
garyaustin•3y ago
From v1 notes: For upsert, if set to true, primary key columns would need to be included in the data parameter in order for an update to properly happen. Also, primary keys used must be natural, not surrogate. There are however, workarounds for surrogate primary keys https://github.com/PostgREST/postgrest/issues/1118
GitHub
Bulk UPSERT Confusion · Issue #1118 · PostgREST/postgrest
Hi, I'm sorry if this is not the place to ask this question, I wouldn't have if there was a section about it in the documentation. I'm trying to use Bulk UPSERT with a simpl...
jkohlin
jkohlinOP•3y ago
Ok, thanks

Did you find this page helpful?