Can a primary key in postgres have zero value?

Question

There is one table at my database that have a row with ID equals to 0 (zero). The primary key is a serial column.

I'm used to see sequences starting with 1. So, is there a ploblem if i keep this ID as zero?

Answer 1

The Serial data type creates integer columns which happen to auto-increment. Hence you should be able to add any integer value to the column (including 0).

From the docs

The type names serial and serial4 are equivalent: both create integer columns.

....(more about Serial) we have created an integer column and arranged for its default values to be assigned from a sequence generator

http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

Answer 2

This is presented as an answer because it’s too long for a comment.

You’re actually talking about two things here.

A primary key is a column designated to be the unique identifier for the table. There may be other unique columns, but the primary key is the one you have settled on, possibly because it’s the most stable value. (For example a customer’s email address is unique, but it’s subject to change, and it’s harder to manage).

The primary key can be any common data type, as long as it is guaranteed to be unique. In some cases, the primary key is a natural property of the row data, in which case it is a natural primary key.

In (most?) other cases, the primary key is an arbitrary value with no inherent meaning. In that case it is called a surrogate key.

The simplest surrogate key, the one which I like to call the lazy surrogate key, is a serial number. Technically, it’s not truly surrogate in that there is an inherent meaning in the sequence, but it is otherwise arbitrary.

For PostgreSQL, the data type typically associated with a serial number is integer, and this is implied in the SERIAL type. If you were doing this in MySQL/MariaDB, you might use unsigned integer, which doesn’t have negative values. PostgreSQL doesn’t have unsigned, so the data can indeed be negative.

The point about serial numbers is that they normally start at 1 and increment by 1. In PostgreSQL, you could have set up your own sequence manually (SERIAL is just a shortcut for that), in which case you can start with any value you like, such as 100, 0 or even -100 etc.

To actually give an answer:

• A primary key can have any compatible value you like, as long as it’s unique.
• A serial number can also have any compatible value, but it is standard practice to start as 1, because that’s how we humans count.

Reasons to override the start-at-one principle include:

• I sometimes use 0 as a sort of default if a valid row hasn’t been selected.
• You might use negative ids to indicate non-standard data, such as for testing or for virtual values; for example a customer with a negative id might indicate an internal allocation.
• You might start your real sequence from a higher number and use lower ids for something similar to the point above.

Note that modern versions of PostgreSQL have a preferred standard alternative in the form of GENERATED BY DEFAULT AS IDENTITY. In line with modern SQL trends, it is much more verbose, but it is much more manageable than the old SERIAL.