I made a form in Retool webapp to create new records in de Retool database. The action query is set to 'Insert a record' and the only key value pair is title with {{ inputTitle.value }}. This is for test purposes.
When I run the event I get the error: insert into "table" ("title") values ($1) returning * - null value in column "id" of relation "table" violates not-null constraint
Indeed the id value - which is the primary key and auto-increments in de database - is empty. It is a new record. Why does this error shows up and how to fix this?
It looks like this error is happening because the id column in your table is set as NOT NULL, but Retool's insert query isn’t letting the database auto-generate the ID. A couple of things to check:
1. Ensure id is Auto-Incrementing
If your id column is supposed to auto-increment, double-check that it’s set up correctly. In PostgreSQL, for example, it should use SERIAL or BIGSERIAL. You can manually set it like this:
ALTER TABLE "table" ALTER COLUMN "id" SET DEFAULT nextval('table_id_seq');
2. Check Table Constraints
To make sure everything is configured properly, run this query:
SELECT column_name, column_default, is_nullable
FROM information_schema.columns
WHERE table_name = 'table';
This will show whether the id column has the right defaults and constraints.
Hope this helps! Let me know if you need any further clarification.
Thank you for your reply. Good to know that auto-incrementing is not a standard set up.
I tried to use the auto-incrementing query but it couldn't find the location of table_id_seq. The table name and column name are correct. I found out sometimes the schema must be provided. I use the Retool Database and don't know what the schema name is if only that is where the problem lies.
The second query works fine. The title column is nullable, the id column isn't. Is that the proper configuration?
Can you share screenshots of the error that appears after inserting a record? Also, please share the database schema along with the primary key configuration, similar to this:
@torbos,
You need to change that from "No Default" to "Default Value" like the screenshot below. (Note that "attendees" is the name of my table. so yours would be "spellen_id_seq" I believe.
I've had quite a few issues with this as well -- specially when the PK of a table is been set by me and not auto-generated. (Given that I had a difference sequence shape or anything upon import/upload)
what @WidleStudioLLP says is correct. Make sure that the PK is set to auto-increment and the default value is the sequence itself.
Make sure this runs well. (change any relevant values)
ALTER TABLE "table" ALTER COLUMN "id" SET DEFAULT nextval('table_id_seq');
If it runs, but nothing changes or happens, try modifying the default value via the GUI and setting the default as the sequence as per @lindakwoo 's comment. If it throws an error saying that you can't do that to an identity column, you'll need to drop that identity and set the default value again (ideally via an alter table)
I was offline for a bit. In the mean time I found the problem and had a fix for it.
New created tables work just fine with auto-increment id's. I had a few imported tables with foreign keys to other imported tables. The existing id's must not change because of that. In the Retool tutorial is an example of creating an id at the client side with a query (look up the highest id and create this id + 1 and send this new id with the rest of the form using the key-value setup). This does the trick and this is robust enough for my app goal.
