CODEWITHSUNDEEP
sqlpostgresqlauto-increment
Cherry
Cherry

Reputation: 33608

Does postgres support to set start value at serial definition?

Here is a question about changing serial value with alter key word. Can this be done in create table defintion? Like that:

CREATE TABLE tablename (
    colname SERIAL stars 1000
);

Upvotes: 6

Views: 6170

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

Identity Columns

There is no currently no way to do this in a CREATE TABLE command in PostgreSQL 9.x using the PostgreSQL-specific serial-type syntax. However, it's coming in PostgreSQL 10 using the standardized Identity Columns syntax, see my answer on it here

Here is how it will look

CREATE TABLE foo (
    id int GENERATED { ALWAYS | BY DEFAULT }
      AS IDENTITY [ ( sequence_options ) ]
);

Where (sequence_options) is START WITH 1000, so something like this.

CREATE TABLE foo (
  foo_id   int   GENERATED ALWAYS AS IDENTITY (START WITH 1000)
);

Upvotes: 5

JRA
JRA

Reputation: 487

You can try this query.Its worked for me.

CREATE SEQUENCE tablename_colname_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1 ---> here you can mention startup nummber as you need
  CACHE 1;

CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);

Upvotes: 6

Related Questions