9

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.

Improve this question
2
  • what you mean by did did not do anything useful?.. Commented Sep 12, 2017 at 9:48
  • In the sense it did not show me the next value Commented Sep 12, 2017 at 9:55

3 Answers 3

Reset to default
11

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

nextval will roll to the next val, so insert will obtain even different value - no?..isn't it currval+1 to just check?..
I have edited my question to show the way I created the table in the first place when I ported it over from MariaDB
You may well have answered my question here. I had just tried nextval('user_id_seq') instead of SELECT nextval(ùser_id_seq') - seems so blindingly obvious to me that I cannot see how i missed it :-*> .
@VaoTsun I would not trust curval+1 because when you issue the next statement the value of the sequence could have changed. I'd fetch nextval and use it directly in the next query.
@fog yes - I payed attention to the title - not to the post itself - of course you want to "reserve" the value
4

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.

Improve this answer

1 Comment

Thank you. I had suspected as much that if I somehow grab the nextval('sequence_id') in my code then I would need to use it in the actual insert statement and you have confirmed that.
4

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

Improve this answer

4 Comments

This will probably work for a (single transaction) conversion, but IMHO he should not trust on it in a running installation/application. (IIRC mysql has a previous value mechanism for fetching and using keys to be used as FK components in subsequent tables. This method can not be emulated in Postgres)
yes - you are right - I answered the subject, not the post. for using the sequence value as a FK you need to actually roll the sequence, so it would change for all transactions
Agreed. But the difference will probably not be clear to the OP.
@joop thank you. updated the question to give clear (hopefully) vision to what question I'm answering

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.