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

Tabber

If new why not use pgAdmin and inspect the commands it will generate? – Oregano

Usually tables are named like 'products" and not like a "product". In this case your sequence will be named like "products_id_seq". Be sure that you are looking for a correct sequence. – Cogen

Answer 1

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.

Laufer

It's not clear from this message what the correct syntax is. It is: ALTER SEQUENCE product_id_seq RESTART WITH 1453; – Acidforming

Just because I parsed the above poorly, here's my way of restating the exact same thing. The syntax is ALTER SEQUENCE yourTableName_yourColumnName_seq RESTART WITH #, where "seq" is the literal text, and you put in a number for #. Do not neglect the underscores. :-) – Confabulate

Please note that if not using the public schema it is needed to prefix with my_schema. ALTER SEQUENCE my_schema.product_id_seq RESTART WITH 1453 – Cagliari

Does anyone know why ALTER SEQUENCE product_id_seq RESTART WITH (SELECT MAX(id) from product); Doesn't work? The only way I found is to use two separate queries. – Plenish

Note that the value you restart with is the next value you want to use. So if you already have a record with id 1453, you should RESTART WITH 1454. – Alcinia

yea but how do you know what table that is? multiple tables might have that. Or if you named the id product_id on table product then what? – Luciennelucier

with IDENTITY column constraints, you do ALTER TABLE tbl ALTER COLUMN id RESTART SET START 1453 – Ultrastructure

If you want to reset your auto increment to the beginning reset it to 1 (not 0) and certainly not 1453. – Corporeity

This worked for me: select setval('"Products_id_seq"', 1453); (My own usecase) – Fridafriday

It doesn't work without double quotes for me ALTER SEQUENCE "product_id_seq" RESTART WITH 1453. – Riane

Usually you would need quotes if your sequence name has upper-case characters, because postgresql will downcase unquoted identifiers – Laufer

ALTER SEQUENCE "Product_id_seq" RESTART WITH 1453 use Quotation marks if your table name has uppercase – Lusatian

Answer 2

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;

Plywood

Beware - this will delete all of your data as well – Duty

@Loolooii, Just flagging it; if somebody unfamiliar to SQL is searching here because they manually added a row to a table with an autoincrement field (through an ORM, for example), then this solution is probably not what they expect. – Duty

The TABLE keyword is redundant. TRUNCATE someTable RESTART IDENTITY; is enough. – Unfruitful

@ihossain have you tried TRUNCATE someTable RESTART IDENTITY CASCADE; ? – Spicy

For referenced tables you can do TRUNCATE table2, table1 RESTART IDENTITY; – Rickart

This is very dangerous in combination with CASCADE, I found out the hard way. I thought the CASCADE would remove the rows in the foreign table related to the parent table, but instead the foreign table was truncated in its entirety. This makes sense, when you consider it's the TRUNCATE that gets cascaded, but pretty dramatic effect if the foreign table is something like a domain object table. – Otherwise

We should have alike a RED BACKGROUND for some kind of answers... Also TRUNCATE doesn't work everywhere so there is also that. – Seeger

Answer 3

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;

Hoke

Answer 4

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    | 

Albuminate

SELECT setval('product_id_seq', 1453); worked for me – Photocopy

I'm trying to select pg_get_serial_sequence('Domain.products', 'id'); but complains that schema does not exists. How can I run this query? I'm new with psql. – Sensitive

Answer 5

-- 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

Cheerful

If I'm not mistaken, PG represents their sequences with last_value and is_called, starts at (1, false), then (1, true), (2, true)... so the MAX(id) + 1 should be MAX(id) instead to not skip an id. – Chavis

I also had to restart my postgres instance for this to work. brew services restart postgresql – Sepia

SELECT SETVAL('project_id_seq', (SELECT MAX(id) + 1 FROM project)); Works perfectly But is there a way to reset the increment value to 0. So the new entries begin with a 0 index ? – Antinucleon

No need to add +1, just use MAX(column_name). If you add +1, it will skip a value in the sequence. – Cindycine

Answer 6

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

Defrock

Most useful answer. Thanks! – Hellbox

This fit me the best. Make sure to add a +1 to the max() though. That is: SELECT SETVAL(pg_get_serial_sequence('table_name', 'column_name'), (SELECT MAX(column_name) + 1 FROM table_name)); – Albite

No need to add +1, just use MAX(column_name). If you add +1, it will skip a value in the sequence. – Cindycine

Answer 7

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>;

Showpiece

One + for usability in case of there's no sequence or you can NOT truncate the table. I think it's best answer – Caracalla

Answer 8

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;

Hemipode

Answer 9

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

Trample

it worked for me as well with Postgres 12.8, thanks – Bornite

Answer 10

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.

Cacodyl

This worked with DBeaver. Many thanks I don't get why devs just have to complicate things! – Message

Answer 11

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;

Hindenburg

Answer 12

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

Bullion

Answer 13

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).

Tilda

Thanks for pointing out how to get seqName for different schema :) – Alleneallentown

Answer 14

ALTER SEQUENCE public."Table_Id_seq"
RESTART 50;

this query worked for me. Postgresql version 14

Fanchie

I needed to use quotes ", only this way it worked Postgresql version 16. I didn't need to use public. and also be careful it is case sensitive, so in this case table_Id_seq or Table_id_seq will not work. If you use pgAdmin you can see all your sequence names in Schemas > public > Sequences. – Letdown

Answer 15

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;

Pointillism

Answer 16

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;

Graycegrayheaded

Answer 17

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();

Threw

Answer 18

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

Niue

That's not the case for me. I have the same condition but the underscore is preserved in the name of the sequence name – Hellbox