I added some rows into the table manually and also I set up the ID (auto_increment) manually. Now when I try to add new row through my app into DB table, to DB table I am getting the error , that the created ID value already exist.
How can I set manually the next ID value (for example, in table I have to IDs, so how to tell to PostgreSQL, that the next ID should be counted since the number 3)?
MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
You can use ALTER TABLE to change the auto_increment value: ALTER TABLE my_table AUTO_INCREMENT = 66441; See the MySQL reference for more details.
Obtaining the value of column that uses AUTO_INCREMENT after an INSERT statement can be achieved in a number of different ways. To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function.
I think there is a simpler way:
ALTER SEQUENCE "seq_product_id" RESTART WITH 10
http://www.postgresql.org/docs/current/static/functions-sequence.html
select setval('sequence-name', <new-value>);
You can get the sequence name from the the table definition:
id | integer | not null default nextval('id_seq'::regclass)
In this case the sequence is named 'id_seq'
Edit (10x to @Glenn):
SELECT setval('id_seq', max(id)) FROM table;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With