CODEWITHSUNDEEP
sqlpostgresqlresetauto-increment
Rad
Rad

Reputation: 4931

Reset auto increment counter in postgres

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 have a table product with Id and name field

Upvotes: 443

Views: 457221

Answers (18)

Emre Arslan
Emre Arslan

Reputation: 69

ALTER SEQUENCE public."Table_Id_seq"
RESTART 50;

this query worked for me. Postgresql version 14

Upvotes: 4

befabry
befabry

Reputation: 812

-- 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) FROM project));

The use of a SELECT is weird but it works.

Source: https://kylewbanks.com/blog/Adding-or-Modifying-a-PostgreSQL-Sequence-Auto-Increment

Edit: removed +1 as suggested in the comments

Upvotes: 63

Sufiyan Ansari
Sufiyan Ansari

Reputation: 1946

I am not sure about all of the above answers, What if I don't have a sequence name? What if I don't want to truncate my table?

Below query helped me to do that without affecting the existing data.

ALTER TABLE <<table_name>>
    ALTER COLUMN <<primary_key_column_name>> RESTART SET START 4044;

Upvotes: 5

Jeb50
Jeb50

Reputation: 7067

If table is like

bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 0)

After inserting some records in the range of 0-9, will cause conflict for next insert, so to reset the START:

ALTER TABLE ... ALTER COLUMN ... RESTART WITH 10;

Upvotes: 3

humble_wolf
humble_wolf

Reputation: 1668

Year 2021, Postgres 11.12

ALTER SEQUENCE did not worked for me, it resets it to null somehow. What worked for me is:

SELECT setval('<table>_<column>_seq', 5);

Upvotes: 14

matt snider
matt snider

Reputation: 4113

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;

Upvotes: 170

Ivan
Ivan

Reputation: 311

To set it to the next highest value you can use:

SELECT SETVAL(pg_get_serial_sequence('table_name', 'column_name'), (SELECT MAX(column_name) FROM table_name));

Upvotes: 30

GarryOne
GarryOne

Reputation: 1488

Node script: Fix all tables identity: auto-increment / nextval, based on last inserted it.

const pg = require('pg');
const { Client } = pg;

const updateTables = async () => {

  const client = new Client({
    user: 'postgres',
    host: 'localhost',
    database: 'my-database',
    password: 'postgres',
    port: 5432,
  });

  await client.connect();

  console.log('Connected');

  const execQuery = async (queryStr, params = []) => {
    return new Promise((resolve, reject) => {
      client.query(queryStr, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      })
    })
  }

  const tablesRes = await execQuery(`
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='public';
  `)

  const tables = tablesRes.rows.map(row => row.table_name);

  tables.map(async tableName => {
    let lastId;
    try {
      const res = await execQuery(`SELECT id from "${tableName}" ORDER BY id DESC LIMIT 1`);
      lastId = res.rows[0].id;
    } catch (e) {}

    if (lastId) {
      const nextId = lastId + 1;
      const queryStr = `ALTER SEQUENCE ${tableName}_id_seq RESTART WITH ${nextId}`;
      await execQuery(queryStr);
      console.log(tableName, queryStr);
    }
  })

};

updateTables();

Upvotes: 1

Thushara Buddhika
Thushara Buddhika

Reputation: 1820

Use this query to check what is the Sequence Key with Schema and Table,

SELECT pg_get_serial_sequence('"SchemaName"."TableName"', 'KeyColumnName'); // output: "SequenceKey"

Use this query increase increment value one by one,

SELECT nextval('"SchemaName"."SequenceKey"'::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('"SchemaName"."SequenceKey"', 120);

When inserting to table next incremented value will be used as the key (121).

Upvotes: 5

abby
abby

Reputation: 678

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

Upvotes: -1

Raja A
Raja A

Reputation: 99

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

Upvotes: 5

BrianB
BrianB

Reputation: 447

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 <table name> 
    ALTER COLUMN <column name> 
        RESTART WITH <new value to restart with>;

Upvotes: 19

Apurv Chaudhary
Apurv Chaudhary

Reputation: 1795

if you want to Reset auto increment from GUI, then follow this steps.

  1. Go to your Database
  2. Click on Public
  3. in the tables Listing page you can see TABS like 'Tables', 'Views', 'Sequences' like that.
  4. Click on Sequences
  5. when you click on 'Sequences' you can see all the Sequences Listing, click on any that you want to Reset
  6. After that you can see multiple choice like 'Alter', 'Set Value', 'Restart', 'Reset' etc...
  7. then click on Reset, then add one New Row.

Upvotes: 12

Terrible Coder
Terrible Coder

Reputation: 990

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;

Upvotes: 7

araqnid
araqnid

Reputation: 133752

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.

Upvotes: 586

Anwar
Anwar

Reputation: 1805

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;

Upvotes: 17

Loolooii
Loolooii

Reputation: 9190

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;

Upvotes: 208

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

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:

=> \d product
                         Table "public.product"
 Column |  Type   |                      Modifiers                       
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('product_id_seq'::regclass)
 name   | text    |

Upvotes: 70

Related Questions