Postgres Retool DB auto increment ID

Not sure why when i add a row manually within the Retool Database the auto increment primary key jumps to a new random number? It's in sequence (1 through 4 as in screenshot) when those entries are added within my Retool app. But if i try to add manually within the GUI it jumps to a random number.

Hey @dru_nasty, I just tried this and the exact same thing happened to me, down to the new ID being 34. Hopefully someone at Retool picks this up and a bug gets squashed!

#queries-and-resources #bug

Hey you two! I'd expect this to be an issue with the id sequence being further along in which case you might try something like

ALTER SEQUENCE "your_sequence_name" RESTART WITH 5;

It seems like too much of a coincidence for you to both be seeing 34 as the next id though, would you mind sharing screenshots of how you have the column configured? I'm also curious to know how you inserted the first 4 rows.

Hey @Kabirdas, I've been able to reproduce this in several of my Retool Database tables, all with the manually added row been assigned an ID of 34, except in tables where the auto-incremented ID had already exceeded 34.

Hey! I did some additional digging and it looks like this may be a result of the way PostgreSQL handles sequences more generally :thinking: Is this causing a particular issue in either of your use cases?

Interesting! It's not really a problem for me, I tend to add dummy data into my Retool database manually on occasion so just something to be aware of. Thanks for your advice!

Here's a look at the settings of that column. The first 4 rows were inserted via a form within my app.

Also, not really a problem since this is only when i'm testing adding rows manually, in production that won't happen. Also noticed when a created my primary key columns, they were set to auto increment, that doesn't show after looking at the settings, just shows as type integer which is strange.

Thanks for sharing :slightly_smiling_face: the auto-incrementing is bound up in expenses_expense_id_seq (a Postgres sequence) being the default value for the field!

Any idea why I get this?

What's the name of your table @nroeder? That error can appear if the supporthoursbilled prefix doesn't match your table name correctly :thinking:

Yeah that was the issue. I got. Thanks.

Hey @Kabirdas! I'm actually having the same issue as @nroeder, but I have double checked the name of the table (many times) and the issues persists.

This wasn't an issue before I started using Retool Forms, actually. Not really sure what happened along the way, but this auto-incrementing column has been causing issues with submission.

1 Like

Same here

Hey Matei,
I believe I figured it out.
Head to the Query Library in retool.
then follow these steps:

  1. Create a sequence if one doesn't already exist (copy and past and run the query):

CREATE SEQUENCE candidates_entry_id_seq;

  1. 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');

  1. (Optional) Ensure the sequence starts with a value higher than the current maximum id in the table:

SELECT setval('candidates_entry_id_seq', (SELECT MAX(id) FROM candidates_entry));

I adjusted the query so it would work specifically for your case (candidates_entry_id_seq)
anyone else that finds this useful and would like to do the same just change as follow:
candidates_entry - change this part to your table name.
id - change this part to your column name.


Thanks @Matti! I had figured out the same solution (by asking ChatGPT of course) but forgot to post back, so thanks for doing so.

I believe the issue arises when you manually change anything about the column or the values. It should really be "left alone" but this is not obvious because the column settings change from "auto-increment ID" to what looks like a regular INT