SERIAL datatype in Postgres

Question

The use of SERIAL datatype is to auto increment the value, so there is no need to specify the value during insertion of values. If we create a table and insert value to that table, by default values starts from 1 for SERIAL column datatype. But Instead of 1 is there any way to start the value from 100 and increment by 10 as default value?

Answer 1

Serial is just syntactic sugaring on top of an int column that takes its value from a sequence. While you can't control a serial column's definitions directly, you could use an explicit sequence definition instead:

CREATE SEQUENCE tablename_colname_seq INCREMENT BY 10 START WITH 100; -- Here!
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Answer 2

You can alter your existing sequence(not matter whether its serial or what) like below

ALTER SEQUENCE mytbl_id_seq INCREMENT 10 RESTART with 100

---

When creating a table

create table mytbl (id serial,val int)

a sequence will automatically creates i.e

 CREATE SEQUENCE mytbl_id_seq
  INCREMENT 1
  START 1

so you can alter this with your desired values i.e

  ALTER SEQUENCE mytbl_id_seq 
    INCREMENT 10 
    RESTART with 100

---

sqlfiddle-demo