4

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?

2
  • Have you tried the obvious? INSERT INTO tablename (t_id, ....) VALUES (0, ....) ; Apr 30, 2018 at 19:55
  • @ypercubeᵀᴹ no I did not try it, would not have worked in the provider I am accustomed to Apr 30, 2018 at 20:06

1 Answer 1

7

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

5
  • Thanks! I come from a MSSQL background where you have to disable the equivalent to serial if you want to insert a specified value. It is a bit involved. This is really easy :) Apr 30, 2018 at 20:03
  • 1
    @TheCatWhisperer "This is really easy" will be your new moddo. Welcome to PostgreSQL. Apr 30, 2018 at 20:04
  • 1
    Along with, "How the heck do I make a clustered index?" :P :) Apr 30, 2018 at 20:06
  • 1
    @TheCatWhisperer it ain't all roses, just 99.9999% of it. =) Objectively. Apr 30, 2018 at 20:17
  • 3
    Clustered indexes in version 17. In 2024. Until then, everything is a heap ;) Apr 30, 2018 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.