We've been using the Retool database alot lately. However, in examining the schema we noticed that Retool creates the primary key as SERIAL and there doesn't seem to be any way to change this. Using SERIAL is specifically mentioned in the postgres docs as a bad approach. See here: Don't Do This - PostgreSQL wiki
You are supposed to use Generated by Default, I.e.: INT GENERATED BY DEFAULT AS IDENTITY
How can we create primary keys using the Generated by Default? Is there a way to change this for existing tables? Is there a way to import our own schema when creating Tables in Retool? I see your an export a schema, but can't import one?
Thank you for this interesting catch! I did not realize the primary key was SERIAL.
Unfortunately there are not any ways for users to change the primary key in Retool DB if you are on the cloud
But if you are self hosted, under the hood you are running a Postgres DB. Where hypothetically you could have much more control over the schema just asked our team about modifying schema for self hosted Retool DB instances.
Just heard from our core data team, their suggestion is "You can manually migrate to drop the serial and move to identity themselves, but I am not sure if the Retool DB UI will support auto generating anymore. Encourage them to try this".
So you would likely need to externalize the data tables from Retool DB to your own self-hosted Postgres(or other) database where you can have greater control over the primary key and its generation options.