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?
asked Apr 30, 2018 at 19:52
1 Answer
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 forIDENTITY COLUMNS)CREATE SEQUENCE(which has the options for the columns)
answered Apr 30, 2018 at 19:55
-
Thanks! I come from a MSSQL background where you have to disable the equivalent to
serialif you want to insert a specified value. It is a bit involved. This is really easy :) Commented Apr 30, 2018 at 20:03 -
1@TheCatWhisperer "This is really easy" will be your new moddo. Welcome to PostgreSQL. Commented Apr 30, 2018 at 20:04
-
1Along with, "How the heck do I make a clustered index?" :P :) Commented Apr 30, 2018 at 20:06
-
1@TheCatWhisperer it ain't all roses, just 99.9999% of it. =) Objectively. Commented Apr 30, 2018 at 20:17
-
3Clustered indexes in version 17. In 2024. Until then, everything is a heap ;) Commented Apr 30, 2018 at 20:37
INSERT INTO tablename (t_id, ....) VALUES (0, ....) ;