3

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?

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

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

| improve this answer | |
  • 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 :) – TheCatWhisperer Apr 30 '18 at 20:03
  • 1
    @TheCatWhisperer "This is really easy" will be your new moddo. Welcome to PostgreSQL. – Evan Carroll Apr 30 '18 at 20:04
  • 1
    Along with, "How the heck do I make a clustered index?" :P :) – TheCatWhisperer Apr 30 '18 at 20:06
  • 1
    @TheCatWhisperer it ain't all roses, just 99.9999% of it. =) Objectively. – Evan Carroll Apr 30 '18 at 20:17
  • 3
    Clustered indexes in version 17. In 2024. Until then, everything is a heap ;) – ypercubeᵀᴹ Apr 30 '18 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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