Postgresql – Postgres: How to insert row with autoincrement id

auto-incrementinsertpostgresqlselect

There is a Table "context".
There is an autoincrement id "context_id".
I am using sequence to retrieve the next value.

SELECT nextval('context_context_id_seq')

The result is: 1, 2, 3,…20….

But there are 24780 rows in the "context" table

How can I get the next value (24781)?

I need to use it in the INSERT statement

Best Answer

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');

Related Solutions

MySQL – Using Autoincrement ID for Username

With MyISAM, there's a neat trick you can do:

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(20) NOT NULL,
  PRIMARY KEY (name, id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

With the above PRIMARY KEY, if all users have different name, they will be given id = 1. If another user comes and chooses name that already exists, he will get id=2, etc.

UPDATE

It seems I misread your question and you only need to have name = 'U243' for user with id=243. If that's the case, you don't have to store the name at all. Use this (myISAM or InnoDB, it will work for both):

CREATE TABLE IF NOT EXISTS test (
  id INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 
  AUTO_INCREMENT=1 ;

and then, to retrieve the id and name:

SELECT 
    id
  , 'U' || id  AS name
FROM
    test ;

Mysql – Autoincrement primary key in CREATE TABLE … AS SELECT

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as 
   `firstname`,
   `user`.`lastname` as `lastname`,
   `user`.`lang` as `lang`,
   `user`.`name` as `user_name`,
   `group`.`name` as `group_name`
from `user`
  inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`)
  left  join `group` on (`group`.`group_id`=`user_groups`.`group_id`)
where `user`.`lang`=`group`.`lang`;

As long as you don't have an 'id' column in your SELECT, this appears to do exactly what you want. The columns in the select will be appended to the right of the columns you declare.

http://dev.mysql.com/doc/refman/5.6/en/create-table-select.html (also in previous versions).

Best Answer

Related Solutions

MySQL – Using Autoincrement ID for Username

Mysql – Autoincrement primary key in CREATE TABLE … AS SELECT

Related Question