I'm working with supabase postgresql. I've seeded my database with records across various tables and am now trying to create an admin portal to create new records in my tables.
For my table in question, I want to automatically create records on it without providing an ID during insertion; to that end, I have set my pkey to serial. Unfortunately, when I try to create a new record, it keeps starting at 1, then 2, then 3, as I repeatedly try to submit my new record. Eventually, it reaches beyond my (10) seeded records and then successfully adds a new 11th record and so on. I don't understand why it's doing this, I would have assumed it would have seen that there are already 10 records in the table and simply just started at 11. Can someone with more knowledge on this provide me some insights?
Here is the supabase insert that I'm using, pretty much the same template as provided in the supabase docs:
const { data, error } = await supabase
.from('my_table')
.insert([
{someField: 'abc'}
])
.select()
the only other context i think i can provide are some snippets from my seeding operation. I am inserting tables like so:
INSERT INTO tableA (id, field, created_at, table_b_fkey)
VALUES
(1, 'hello world', '2024-03-16 14:00:00', 1),
...
INSERT INTO tableB (id, field, created_at)
VALUES
(1, 'hola mundo', '2024-03-16 14:00:00'),
...
In these cases, I do actually provide an explicit primary_key as id into my tables because I need to seed it with relationships between tables. Maybe that's the reason behind it creating issues when trying to add new records with serial?
There are two options:
Before you start inserting rows, set the sequence that you see in the DEFAULT clause of the id column to a value higher than the id of all the rows you have sown:
SELECT setval('tablea_id_seq', 100000);
(My recommendation would be to use a bigint identity column instead of serial, but the procedure would remain the same: you have set the sequence value appropriately.)
(My second recommendation, if you don't want my first one, is to use bigserial rather than serial.)
Use uuid for the primary key column, then you don't have to worry about collisions.