Record Create failed - Ley ID already exists (but I was creating a new key

[jhubs]

• Goal: I'm trying to add rows to a Retool database,

• Steps: I was able to create two rows successfully, now am blocked form creating any more. Tried log out and in, refresh.
  

  

[MiguelOrtiz]

Hi @jhubs,

I believe this may be because of your db's sequence. Here's a solution.

Postgres Retool DB auto increment ID

Hey Matei, I believe I figured it out. Head to the Query Library in retool. then follow these steps: Create a sequence if one doesn't already exist (copy and past and run the query): CREATE SEQUENCE candidates_entry_id_seq; Alter the id column to use the sequence (copy and past and run the query): ALTER TABLE candidates_entry ALTER COLUMN id SET DEFAULT nextval('candidates_entry_id_seq'); (Optional) Ensure the sequence starts with a value higher than the current maximum id in the tab…

Or in alternative, a non conventional solution is to keep clicking until you will reach the sequence that the database will accept.

[jhubs]

If this were the case, why would the sequence have worked fine for 181 rows prior to this one?

Also note that this is a retool database, not an external.

Thanks, all.

[MiguelOrtiz]

Yes, I've had the same issue with retool's database. It may be a bug, something related to the sequence not being synced between manual creation of rows on the database interface, and creation via app queries.

Whenever I faced the issue (have moved away from retool's database due to performance concerns) I would click until the manual submission reached the current sequence number

[Jack_T]

Hello @jhubs!

This is a weird bug with SQL and how its 'sequencing' procedure runs. The process was new to me until I found this post as well as the one that @MiguelOrtiz linked above. More tech heavy background info on this here.

From what i understand, most likely the first 181 were made during sequence runs. When the DB is creating several rows via an automatic process it takes over and does things as intended.

Manually adding rows to a DB is something which is rarely done (as crazy as that sounds) other than with small scale experimenting/testing.

There is likely a mismatch between the counters used by the code for setting the ID on a manually added a row and the ID counter which is currently of the last row in the table which is saying "hay 182 is already here you can't make a duplicate".

If you can find how the first 181 rows were made I would guess replicating that process would work. And for adding single rows, using a form to INSERT to the SQL able might work better, although from the post Miguel shared there are some bugs that the DB table can get confused on

Hope this helps!

[jhubs]

This does help, though we thought the intention of the retool database was easy access to edit manually in retool instead of writing a formal query. We had an automatic process but I needed to create several rows with a different JSON format than that process created, and this broke on row 3 of such additions. I agree I would not normally be doing this manually.

Interestingly, 1 row here and there manually hadn't created the sequence run issue - it came up with 3 in a row.

In any event, if manual use breaks it, it defeats the purpose of using a retool database over a good ol postgres table, and we've already switched over to that and thus resolved the issue. Thanks all.

[Jack_T]

Thank you for the info!

That is odd the table broke on the third row. Do you have/remember the error message you got?

Highly recommend good old postgres tables. Happy to hear the issue was resolved!