PostgreSQL Autoincrement

Question

I m switching from MySQL to PostgreSQL and was wondering how I can do autoincrement values  I saw in the PostgreSQL docs a datatype  serial   but I get syntax errors when using it  in v8 0

User · Answer

You have to be careful not to insert directly into your SERIAL or sequence field  otherwise your write will fail when the sequence reaches the inserted value   -- Table   test   -- DROP TABLE test   CREATE TABLE test      ID  SERIAL     Rank  integer NOT NULL     GermanHeadword   text     NOT NULL     PartOfSpeech   text  NOT NULL     ExampleSentence   text  NOT NULL     EnglishGloss   text    NOT NULL    CONSTRAINT  PKey  PRIMARY KEY   ID    Rank     WITH     OIDS FALSE    -- ALTER TABLE test OWNER TO postgres   INSERT INTO test  Rank    GermanHeadword    PartOfSpeech    ExampleSentence    EnglishGloss              VALUES  1     der    die    das    den    dem    des      art    Der Mann k  sst die Frau und das Kind schaut zu      the    of the          INSERT INTO test  ID    Rank    GermanHeadword    PartOfSpeech    ExampleSentence    EnglishGloss              VALUES  2  1     der    die    das      pron    Das ist mein Fahrrad      that    those        INSERT INTO test  Rank    GermanHeadword    PartOfSpeech    ExampleSentence    EnglishGloss              VALUES  1     der    die    das      pron    Die Frau  die nebenen wohnt  hei  t Renate      that    who       SELECT   from test

User · Answer

Yes  SERIAL is the equivalent function   CREATE TABLE foo   id SERIAL  bar varchar    INSERT INTO foo  bar  values   blah    INSERT INTO foo  bar  values   blah     SELECT   FROM foo   1 blah 2 blah   SERIAL is just a create table time macro around sequences   You can not alter SERIAL onto an existing column

User · Answer

Create Sequence  CREATE SEQUENCE user role id seq   INCREMENT 1   MINVALUE 1   MAXVALUE 9223372036854775807   START 3   CACHE 1  ALTER TABLE user role id seq   OWNER TO postgres   and alter table ALTER TABLE user roles ALTER COLUMN user role id SET DEFAULT nextval  user role id seq   regclass

User · Answer

Sorry  to rehash an old question  but this was the first Stack Overflow question answer that popped up on Google   This post  which came up first on Google  talks about using the more updated syntax for PostgreSQL 10  https   blog 2ndquadrant com postgresql-10-identity-columns   which happens to be   CREATE TABLE test new       id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY       Hope that helps

User · Answer

Whilst it looks like sequences are the equivalent to MySQL auto increment  there are some subtle but important differences   1  Failed Queries Increment The Sequence Serial  The serial column gets incremented on failed queries  This leads to fragmentation from failed queries  not just row deletions  For example  run the following queries on your PostgreSQL database   CREATE TABLE table1     uid serial NOT NULL PRIMARY KEY    col b integer NOT NULL    CHECK  col b gt  0      INSERT INTO table1  col b  VALUES 1   INSERT INTO table1  col b  VALUES -1   INSERT INTO table1  col b  VALUES 2    SELECT   FROM table1    You should get the following output    uid   col b  ----- -------    1       1    3       2  2 rows    Notice how uid goes from 1 to 3 instead of 1 to 2   This still occurs if you were to manually create your own sequence with   CREATE SEQUENCE table1 seq  CREATE TABLE table1       col a smallint NOT NULL DEFAULT nextval  table1 seq        col b integer NOT NULL      CHECK  col b gt  0     ALTER SEQUENCE table1 seq OWNED BY table1 col a    If you wish to test how MySQL is different  run the following on a MySQL database   CREATE TABLE table1     uid int unsigned NOT NULL AUTO INCREMENT PRIMARY KEY    col b int unsigned NOT NULL     INSERT INTO table1  col b  VALUES 1   INSERT INTO table1  col b  VALUES -1   INSERT INTO table1  col b  VALUES 2     You should get the following with no fragementation    ----- -------    uid   col b    ----- -------      1       1       2       2    ----- -------  2 rows in set  0 00 sec    2  Manually Setting the Serial Column Value Can Cause Future Queries to Fail   This was pointed out by  trev in a previous answer   To simulate this manually set the uid to 4 which will  clash  later   INSERT INTO table1  uid  col b  VALUES 5  5     Table data    uid   col b  ----- -------    1       1    3       2    5       5  3 rows    Run another insert    INSERT INTO table1  col b  VALUES 6     Table data    uid   col b  ----- -------    1       1    3       2    5       5    4       6   Now if you run another insert   INSERT INTO table1  col b  VALUES 7     It will fail with the following error message      ERROR   duplicate key value violates unique constraint  table1 pkey    DETAIL   Key  uid   5  already exists    In contrast  MySQL will handle this gracefully as shown below   INSERT INTO table1  uid  col b  VALUES 4  4     Now insert another row without setting uid  INSERT INTO table1  col b  VALUES 3     The query doesn t fail  uid just jumps to 5    ----- -------    uid   col b    ----- -------      1       1       2       2       4       4       5       3    ----- -------    Testing was performed on MySQL 5 6 33  for Linux  x86 64  and PostgreSQL 9 4 9

User · Answer

If you want to add sequence to id in the table which already exist you can use     CREATE SEQUENCE user id seq  ALTER TABLE user ALTER user id SET DEFAULT NEXTVAL  user id seq

User · Answer

In the context of the asked question and in reply to the comment by  sereja1c  creating SERIAL implicitly creates sequences  so for the above example-  CREATE TABLE foo  id SERIAL bar varchar     CREATE TABLE would implicitly create sequence foo id seq for serial column foo id  Hence  SERIAL  4 Bytes  is good for its ease of use unless you need a specific datatype for your id

User · Answer

You can use any other integer data type  such as smallint   Example    CREATE SEQUENCE user id seq  CREATE TABLE user       user id smallint NOT NULL DEFAULT nextval  user id seq      ALTER SEQUENCE user id seq OWNED BY user user id    Better to use your own data type  rather than user serial data type

User · Answer

This way will work for sure  I hope it helps   CREATE TABLE fruits     id SERIAL PRIMARY KEY     name VARCHAR NOT NULL     INSERT INTO fruits id name  VALUES DEFAULT  apple     or  INSERT INTO fruits VALUES DEFAULT  apple      You can check this the details in the next link  http   www postgresqltutorial com postgresql-serial

User · Answer

Starting with Postgres 10  identity columns as defined by the SQL standard are also supported   create table foo      id integer generated always as identity      creates an identity column that can t be overridden unless explicitly asked for  The following insert will fail with a column defined as generated always    insert into foo  id   values  1     This can however be overruled   insert into foo  id  overriding system value  values  1     When using the option generated by default this is essentially the same behaviour as the existing serial implementation   create table foo      id integer generated by default as identity      When a value is supplied manually  the underlying sequence needs to be adjusted manually as well - the same as with a serial column     An identity column is not a primary key by default  just like a serial column   If it should be one  a primary key constraint needs to be defined manually

User · Answer

Since PostgreSQL 10  CREATE TABLE test new       id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY      payload text

[postgresql] PostgreSQL Autoincrement

Examples related to postgresql

Examples related to auto-increment