Title pretty much explains it. I'm using postgres as a database and I don't have any problem with it other than this question: why does postgres start auto-incrementing types: smallserial, serial, bigserial with 1.

Logically, it should start with 0 so it can be used with arrays, lists and such easier.

Is there any logic behind this? Is it same with other databases?

P.s. I'm using pgAdmin 4 to create tables, so I don't know if that's responsible for the situation. I mean, maybe it would start from 0 if I used pure sql code to create the tables. So please take that into account when answering.

CodePudding user response：

The SQL:2003 standard defined sequences to start at 1 by default (though you may change the default and define a sequence to start at 0 or another value if you prefer).

This is the same in Oracle, Microsoft SQL Server, PostgreSQL, MySQL, DB2, Informix, etc. All the popular SQL databases support some sort of sequence generator or identity column. Though specific implementations and syntax varies (not all conform to the SQL:2003 standard), they all start at 1 by default.

There are other cases in SQL of ordinal numbers starting at 1, for example ROW_NUMBER().

These numbers are not meant to be array indexes, so there is no need to align them to conventional zero-based arrays.

Sequences have even less need to be treated as array indexes, because the values are not guaranteed to be consecutive. They are not row numbers.