By Rad


2011-03-17 16:55:03 8 Comments

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

11 comments

@abby 2020-05-14 13:26:11

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

@Wiwwil 2019-03-06 13:46:13

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

@Ten 2019-07-17 09:47:09

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.

@BigRon 2020-01-31 23:00:20

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

@Charith Jayasanka 2020-05-25 21:00:59

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 ?

@Raja A 2019-07-03 04:31:58

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

@BrianB 2018-10-10 15:30:19

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

@ABS 2019-09-18 12:18:43

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

@Chaudhary 2018-03-28 09:00:18

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.

@Vinoth Shankar 2016-12-01 04:26:14

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;

@araqnid 2011-03-17 17:00:01

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.

@Liron Yahdav 2012-05-03 22:19:19

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

@kmort 2015-05-21 17:32:29

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

@Daniel L. VanDenBosch 2018-04-10 13:04:39

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

@Chris Huang-Leaver 2018-12-20 03:14:50

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.

@hughes 2019-03-21 21:58:58

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.

@Anwar 2015-11-10 13:17:23

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;

@Loolooii 2013-08-05 14:19:59

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;

@kibibu 2013-10-06 22:20:04

Beware - this will delete all of your data as well

@kibibu 2013-10-09 03:15:43

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

@user1 2016-10-03 08:17:53

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

@ihossain 2019-01-16 22:24:00

Any idea how I would do this with CASCADE?

@Vedran 2019-05-31 15:12:00

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

@Zeeshanef 2019-06-30 09:45:41

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

@RafaelB13 2020-01-26 01:09:06

Thank you very much for the answer, it worked perfectly

@matt snider 2013-02-07 19:53:21

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;

@Clodoaldo Neto 2011-03-17 19:01:58

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    | 

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Cannot simply use PostgreSQL table name ("relation does not exist")

5 Answered Questions

[SOLVED] auto increment ID in H2 database

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

7 Answered Questions

[SOLVED] postgres: upgrade a user to be a superuser?

  • 2012-05-25 15:43:18
  • flossfan
  • 316859 View
  • 643 Score
  • 7 Answer
  • Tags:   sql postgresql

7 Answered Questions

[SOLVED] How to set auto increment primary key in PostgreSQL?

  • 2011-10-10 20:56:40
  • mkn
  • 445919 View
  • 259 Score
  • 7 Answer
  • Tags:   sql postgresql

7 Answered Questions

[SOLVED] In plain English, what does "git reset" do?

  • 2010-03-27 16:44:39
  • e-satis
  • 205123 View
  • 674 Score
  • 7 Answer
  • Tags:   git reset

2 Answered Questions

[SOLVED] Reset auto increment counter in postgresql

5 Answered Questions

3 Answered Questions

[SOLVED] Reset auto increment column value in script mysql

1 Answered Questions

[SOLVED] Increment a value in Postgres

  • 2012-04-19 17:16:08
  • greatwitenorth
  • 99047 View
  • 105 Score
  • 1 Answer
  • Tags:   postgresql increment

Sponsored Content