Reset auto increment counter in postgres

Question

I would like to force the auto increment field of a table to some value  I tried with this   ALTER TABLE product AUTO INCREMENT   1453   AND   ALTER SEQUENCE product  RESTART WITH 1453  ERROR   relation  your sequence name  does not exist   I m new to postgres      I have a table product with Id and name field

User · Answer

Use this query to check what is the Sequence Key with Schema and Table  SELECT pg get serial sequence   quot SchemaName quot   quot TableName quot     KeyColumnName       output   quot SequenceKey quot   Use this query increase increment value one by one  SELECT nextval   quot SchemaName quot   quot SequenceKey quot    regclass      output 110  When inserting to table next incremented value will be used as the key  111   Use this query to set specific value as the incremented value SELECT setval   quot SchemaName quot   quot SequenceKey quot    120    When inserting to table next incremented value will be used as the key  121

User · Answer

To get sequence id use   SELECT pg get serial sequence  tableName    ColumnName      This will gives you sequesce id as tableName ColumnName seq   To Get Last seed number use  select currval pg get serial sequence  tableName    ColumnName       or if you know sequence id already use it directly   select currval tableName ColumnName seq     It will gives you last seed number  To Reset seed number use  ALTER SEQUENCE tableName ColumnName seq RESTART WITH 45

User · Answer

If you have a table with an IDENTITY column that you want to reset the next value for you can use the following command   ALTER TABLE  lt table name gt       ALTER COLUMN  lt column name gt           RESTART WITH  lt new value to restart with gt

User · Answer

If you created the table product with an id column  then the sequence is not simply called product  but rather product id seq  that is    table    column  seq    This is the ALTER SEQUENCE command you need   ALTER SEQUENCE product id seq RESTART WITH 1453   You can see the sequences in your database using the  ds command in psql  If you do  d product and look at the default constraint for your column  the nextval      call will specify the sequence name too

User · Answer

To reset the auto increment you have to get your sequence name by using following query   Syntax   SELECT pg get serial sequence    tablename         columnname        Example   SELECT pg get serial sequence  demo    autoid      The query will return the sequence name of autoid as  Demo autoid seq  Then use the following query to reset the autoid   Syntax   ALTER SEQUENCE sequenceName RESTART WITH value    Example   ALTER SEQUENCE  Demo autoid seq  RESTART WITH 1453

User · Answer

Converted from comment for the sake of visitor s convenience  It s not clear from this message what the correct syntax is  It is    ALTER SEQUENCE product id seq RESTART WITH 1453

User · Answer

Note that if you have table name with      it is removed in sequence name   For example  table name  user tokens column  id Sequence name  usertokens id seq

User · Answer

The following command does this automatically for you  This will also delete all the data in the table  So be careful    TRUNCATE TABLE someTable RESTART IDENTITY

User · Answer

-- Change the starting value of the sequence  ALTER SEQUENCE project id seq RESTART 3000   Same but dynamic   SELECT SETVAL  project id seq    SELECT MAX id    1 FROM project     I agree the use of a SELECT is disturbing but it works  Source  https   kylewbanks com blog Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment

User · Answer

To set the sequence counter   setval  product id seq   1453     If you don t know the sequence name use the pg get serial sequence function   select pg get serial sequence  product    id     pg get serial sequence  ------------------------  public product id seq   The parameters are the table name and the column name   Or just issue a  d product at the psql prompt     gt   d product                          Table  public product   Column    Type                          Modifiers                        -------- --------- ------------------------------------------------------  id       integer   not null default nextval  product id seq   regclass   name     text

User · Answer

Here is the command that you are looking for  assuming your sequence for the product table is product id seq      ALTER SEQUENCE product id seq RESTART WITH 1453

User · Answer

if you want to Reset auto increment from GUI  then follow this steps    Go to your Database Click on Public in the tables Listing page you can see TABS like  Tables    Views    Sequences  like that  Click on Sequences when you click on  Sequences  you can see all the Sequences Listing  click on any that you want to Reset After that you can see multiple choice like  Alter    Set Value    Restart    Reset  etc    then click on Reset  then add one New Row

[sql] Reset auto increment counter in postgres

Examples related to sql

Examples related to postgresql

Examples related to reset

Examples related to auto-increment