Determine next auto_increment value before an INSERT in Postgres

Question

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.

Answer 1

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?