Stop using SERIAL in Postgres
TL;DR — Just stop. Don’t use serial. Postgres has supported identity columns since version 10, released in 2017. Use them instead of serial and move on.
Internally, Postgres generates a sequence for both serial and identity columns, using the convention <table_name>_<column_name>_seq. But the way it manages these sequences is where things get interesting. We’ll uncover these differences as we explore the issues with serial and how identity columns solve them.
This time you get a friendly error explaining why the insert failed. You also get a dangerous hint about overriding the system value. Ignore that hint.
This is probably not something you want on a primary key. Worse, you cannot revert the id column back to serial. Instead, you will need to create a new sequence and manually alter the column with alter table ... set default. It is quite a hassle!
Postgres helpfully explains why you can’t drop the sequence associated with the identity column. Just like before, you get a dangerous hint about dropping the id column. Pretend you didn’t see it.
