Why does the id serial primary key keep changing?

Question

I have just started a full stack web developer course which includes PostgreSQL. I have been give some practice questions to do and when I clicked on run SQL it displays the id, first_name and last_name but when I entered in more lines of code to answer more questions and clicked on run SQL again, the id number changed to a completely different number and I don't understand why this is happening.

In the practice questions I was instructed to add more rows and then to update the entry with an id of 2 to something else but how can I update id 2 if the id numbers keep changing? id 2 wasn't even on the screen. What I understand of id serial primary key is that it auto increments the id when you add new rows but in this case the id keeps changing to random numbers, why does it do this? The screenshots are code from the course, not what I entered. http://sqlfiddle.com/#!17/a114f/2 this is the link but I am not sure if you anyone who has not signed up to the course can access it. Sorry if this is a really simple newbie question but I have spent a lot of time looking online and I really need to move forward.

Answer 1

Like a_horse_with_no_name, I too prefer db<>fiddle for SQL code sharing. But if you're restricted to sqlfiddle for whatever reason, you can add a setval() command to your code to force the seeding value.

select setval('drivers_id_seq',1);

INSERT INTO drivers (first_name, last_name) VALUES ('Amy', 'Hua');

SELECT * from drivers;

See example here (link). Note that drivers_id_seq is a system-generated name that you can guess pretty easily (should you need to reseed the serial you create on another object).

Answer 2

In SQL Fiddle every time you click the button on the right its going to "rerun" your code.

However SQL Fiddle doesn't guarantee the primary keys are going to be the same, and isolates your code in such a way that definitely is causing the pk to increment.

http://sqlfiddle.com/#!17/a114f/2 Here's the original fiddle, and if you just jam on that submit button you can see the value changing each time.

Nothing in your code prevents duplication from concurring if you submitted it multiple times, but that would always have more than one row in the discover table.

Answer 3

As far as I can tell this is a bug in SQLFiddle.

Apparently the table definition (or something else) is shared with other users. If you do the same e.g. using db<>fiddle you always get the same ID after dropping and re-creating the tables:

db<>fiddle demo

---

SQLFiddle has never worked reliably for me anyway. Plus it seems to be stuck on a really old Postgres version. So you might use something different to practice your SQL skills or do your homework.