Search code examples
postgresqlauto-increment

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.

Solution

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