Postgres, how insert "0" value into serial?

Question

I have a serial id in my application table. However, I would like to insert a single 0 id record to mean global.

Is there a way I can insert this into my table without affecting the counter?

Answer 1

Sure, just do it. The counter only gets incremented when you call a Sequence Manipulation Function. In the case when you don't explicitly specify a value, the DEFAULT triggers a call to nextval(sequence_name)

CREATE TABLE foo ( id serial );
INSERT INTO foo (id) VALUES (0);

PostgreSQL 10+

It should be noted, that serial is the old and antiquated method of doing this. You should be using the standardized IDENTITY COLUMNS

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
INSERT INTO foo (id) VALUES (0);

See also

• What are Identity Columns?
• CREATE TABLE (syntax for IDENTITY COLUMNS)
• CREATE SEQUENCE (which has the options for the columns)