Don't Use Serial

August 12, 2024

On one episode of Postgres.fm, the hosts discuss some "Don't Do This" lists, one of which is hosted by Postgres itself. I hadn't heard of this before, and so checked it out and discovered that using the serial type (as an auto-generated number, typically for primary keys) is not recommended, unless you're supporting a version older than Postgres 10. I didn't start using Postgres until version 12, so I'm definitely not supporting any database on version 10.

Since reading that page some time ago, I've had replacing my usage of serial in my veganbot database on my task list, and didn't get to it until today because I thought it was going to be a bit of a pain in the ass. Turns out, it was super simple! For example, turn this:

CREATE TABLE accounts (
    id serial PRIMARY KEY,
);

into this:

CREATE TABLE accounts (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
);

See the Postgres documentation here and here for more information.