Insert a record from a form. Id auto increment

[ggallese]

Goal: Insert a record with a form and that the id is added automatically by auto incrementing.

Steps:

• I simply created a GUI insert query, as I have done before, but with this table it is not working.
• I have identified that this table doesn't have the "default value" of the "id" column set to <table_name>_id_seq. (rates_id_seq in my case).
• I have tried to add it but an error that reads 'invalid input syntax for type integer: "rates_id_seq"' appears. If it was possible to add this, I suppose that it would be solved... right?

Screenshots:

[Harry_Doan]

@ggallese Hmm usually our table will be created with an "id" column that is already "auto-increment". Can I ask how you created this table. Was it through the "form" UI?

[Harry_Doan]

I can report this as a bug to the team, but in the mean time, you can add another "id" column to the table with type "Auto increment id"

[jg80]

In the DB UI it is a little tricky to play with the sequences in the Postgres behind the scenes, but as @Harry_Doan mentioned, I’ve sometimes found that deleting the PK column and then creating a new one works.

FWIW, I don't think its a bug so much as not having visibility into the sequences created in the DB from the web UI. Since switching over to using pgAdmin to access and manage the Retool DB (when prototyping), I haven't seen anything that doesn't work as expected.

[ggallese]

Hi Harry, thanks for answering. I did this, but I cannot find a way to make it the new pk. The original "id" is being used as fk for other tables so I can't delete it either.

What i want to do is just use the insert record query without having to declare an "id" value. What I am doing is adding this in the "insert record" query.

I don't think that it is the best practice, but it is the only solution that i could find.

[ggallese]

Thanks for contributing jg80.

I didn't understand the second paragraph. Can you help me rephrasing it please?

[jg80]

I’ve found that connecting to the Retool database from outside of the Retool database web UI using a database focused tool (I use pgAdmin 4) makes it simpler to manage some of the more complex tasks in the database, such as manipulating keys and sequences. If you are familiar with these types of tools and working with databases in them, it is pretty simple to set it up since the Retool DB UI gives you the connection strings.

[Harry_Doan]

I agree with @jg80 that it may be easiest to connect using a different client and the connection string and manually add an id_sequence to your table.

But regarding how to do it from Retool UI, I think you can remove the old column and it'll show this UI to let you add it back with auto increment.

[jg80]

Huh. Never saw that before. What were the steps to get to it? Remove key -> add key to make editable?

[Harry_Doan]

@jq80 Yeah it only shows when a table doesn't have a "Primary Key" column. You can trigger it by removing the current primary key column using "Delete Field"