CODEWITHSUNDEEP
postgresqlauto-increment
DroidOS
DroidOS

Reputation: 8900

Determine next auto_increment value before an INSERT in Postgres

I am in the process of switching from MariaDB to Postgres and have run into a small issue. There are times when I need to establish the next AUTO_INCREMENT value prior to making an INSERT. This is because the INSERT has an impact on a few other tables that would be quite messy to repair if done post the INSERT itself. In mySQL/MariaDB this was easy. I simply did

"SELECT AUTO_INCREMENT 
 FROM information_schema.tables 
 WHERE table_name = 'users' 
       AND table_schema = DATABASE( ) ;";

and used the returned value to pre-correct the other tables prior to making the actual INSERT. I am aware that with pgSQL one can use RETURNINGwith SELECT,INSERT and UPDATE statements. However, this would require a post-INSERT correction to the other tables which in turn would involve breaking code that has been tested and proven to work. I imagine that there is a way to find the next AUTO_INCREMENT but I have been unable to find it. Amongst other things I tried nextval('users_id_seq') which did not do anything useful.

To port my original MariaDB schema over to Postgres I edited the SQL emitted by Adminer with the MariaDB version to ensure it works with Postgres. This mostly involved changing INT(11) to INTEGER, TINYINT(3) to SMALL INT, VARCHAR to CHARACTER VARYING etc. With the auto-increment columns I read up a bit and concluded that I needed to use SERIAL instead. So the typical SQL I fed to Postgres was like this

CREATE TABLE "users" 
(
 "id" SERIAL NOT NULL,
 "bid" INTEGER  NOT NULL DEFAULT 0,
 "gid" INTEGER  NOT NULL DEFAULT 0,
 "sid" INTEGER  NOT NULL DEFAULT 0,
 "s1" character varying(64)NOT NULL,
 "s2" character varying(64)NOT NULL,
 "name" character varying(64)NOT NULL,
 "apik" character varying(128)NOT NULL,
 "email" character varying(192)NOT NULL,
 "gsm" character varying(64)NOT NULL,
 "rights" character varying(64)NOT NULL,
 "managed" character varying(256)NOT NULL DEFAULT 
 'M_BepHJXALYpLyOjHxVGWJnlAMqxv0KNENmcYA,,',
  "senior" SMALLINT  NOT NULL DEFAULT 0,
  "refs" INTEGER  NOT NULL DEFAULT 0,
  "verified" SMALLINT  NOT NULL DEFAULT 0,
  "vkey" character varying(64)NOT NULL,
  "lang" SMALLINT  NOT NULL DEFAULT 0,
  "leader" INTEGER  NOT NULL
 );

This SQL run from Adminer works correctly. However, when I then try to get Adminer to export the new users table in Postgres it gives me

CREATE TABLE "public"."users" 
(
 "id" integer DEFAULT nextval('users_id_seq') NOT NULL,
 "bid" integer DEFAULT 0 NOT NULL,

It is perhaps possible that I have gone about things incorrectly when porting over the AUTO_INCREMENT columns - in which case there is still time to correct the error.

Upvotes: 8

Views: 30884

Answers (3)

user330315
user330315

Reputation:

As documented in the manual serial is not a "real" data type, it's just a shortcut for a column that takes its default value from a sequence.

If you need the generated value in your code before inserting, use nextval() then use the value you got in your insert statement:

In PL/pgSQL this would be something like the following. The exact syntax obviously depends on the programming language you use:

declare
  l_userid integer;
begin
  l_userid := nextval('users_id_seq');
  -- do something with that value
  insert into users (id, ...)
  values (l_userid, ...);
end;

It is important that you never pass a value to the insert statement that was not generated by the sequence. Postgres will not automagically sync the sequence values with "manually" provided values.

Upvotes: 4

fog
fog

Reputation: 3391

If you used serial in the column definition then you have a sequence named TABLE_COLUMN_seq in the same namespace of the table (where TABLE and COLUMN are, respectively, the names of the table and the column). You can just do:

SELECT nextval('TABLE_COLUMN_seq');

I see you have tried that, can you show your CREATE TABLE statement so that we can check all names are ok?

Upvotes: 10

Vao Tsun
Vao Tsun

Reputation: 51599

you can select last_value+1 from the sequence itself, eg:

t=# create table so109(i serial,n int);
CREATE TABLE
Time: 2.585 ms
t=# insert into so109(n) select i from generate_series(1,22,1) i;
INSERT 0 22
Time: 1.236 ms
t=# select * from so109_i_seq ;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 so109_i_seq   |         22 |           1 |            1 | 9223372036854775807 |         1 |           1 |      11 | f         | t
(1 row)

or use currval, eg:

t=# select currval('so109_i_seq')+1;
 ?column?
----------
       23
(1 row)

UPDATE

While this answer gives an idea on how to Determine next auto_increment value before an INSERT in Postgres (which is the title), proposed methods would not fit the needs of post itself. If you are looking for "replacement" for RETURNING directive in INSERT statement, the better way is actually "reserving" the value with nextval, just as @fog proposed. So concurrent transactions would not get the same value twice...

Upvotes: 4

Related Questions