Retool Database - Insert From Form - Primary Key Errors

[jkansur]

I've used retool with MySQL and haven't run into this issue.
But, on this project. I decided to try the retool database.
But, no matter what I try, I keep hitting the same issue. The update query wants me to supply an ID# for the id field, even though this field is the primary key and is set to auto-increment.

Retool Database Column Settings:
field name: id
field type: Number
Nullable: false
Unique: true
Default Value: devices_id_seq

Insert Query Settings:
GUI Mode: Run query only when manually triggered
Table: devices
Action type: Insert a record
Changeset: Key Value pairs

THINGS I HAVE TRIED...
1 - Not including the primary key in the field list.

2 - Using the primary key in the field list, but not providing a value.

3 - Using the NULL string

4 - Using JS Null

5 - Using the keyword from the column settings.

What is the proper way to auto-increment when inserting a record using key value pairs?

[ScottR]

Use upsert and remove the key value pair using ID

[jkansur]

I had to use the key-value pairs to set date fields to null when dates were not supplied in the form. BUT... I just figured out the problem.

Using the add row button on the retool database page, it gave me a duplicate key error for every device_id number all the way up to 54, which was the number of rows that I had imported via CSV.

So, apparently when I did the CSV import the sequence number wasn't updated.

After getting the duplicate key error 54 times, it is successfully auto-incrementing and everything is working now.

[Paulo]

If we are setting up RetoolDB and we don't need the existing ids, we can delete the id column and create a new one with a field type of 'Auto-incrementing Integer ID'

When adding records, we can then leave the id field empty and it will automatically be set.