I just noticed that auto generated id columns use serial instead of bigserial or identity.
While considering whether I should move to bigserial before I start using my tables I found this post
Almost 2 years on I’d like to know if anybody knows whether identity is now supported or whether it is “safe” to exchange serial for bigserial.
Two years ago retool wasn’t sure whether the DB UI would be able to auto generate the ids.
Since I don’t know what else I should keep an eye on I was hoping to get some insights here.
Converting from serial to bigserial is safe. It's just changing the underlying column and sequence types from int to bigint, and PostgreSQL handles that implicitly with no data loss. The SQL is something like this:
ALTER TABLE your_table ALTER COLUMN id TYPE bigint; ALTER SEQUENCE your_table_id_seq AS bigint;
Just watch out for any foreign key columns referencing that id in other tables - those will need to be updated to bigint too.
As for identity columns, I haven't seen Retool add support for those in the database interface. If you're comfortable running raw SQL on your Retool DB, you can migrate existing serial columns manually and they'll work fine - Retool's UI won't break. The auto-generated id behavior on new rows continues to work since the sequence is still there.
If you're just starting out and your tables are still empty, now is the ideal time to make the switch before you accumulate data or foreign key dependencies.
