Getting the next available auto_increment id in PostgreSQL is the best approach?

Question

I am new to postgreSQL, so I really appreciate any pointers from the community.

I am updating some functions in the CMS of a rather old site that I just inherited. Basically, I need an article id before it is inserted into the database. In any case, one way or another, to check the next value that the sequence will use before the start of the database session (insert)?

At first I thought I could use SELECT max(id) from tbl_name , however, since the identifier is automatically incremented by the sequence, and the articles are often deleted, it obviously will not return the correct identifier for the next value in the sequence.

Since the article is not already in the database and the database session is not running yet, it seems I can not use the currval() postgreSQL functionality. Also, if I use nextval() , it automatically increments the sequence before the data is inserted (pasting will also automatically increase the sequence, ending up with the sequence being double increased).

I am currently dealing with this:

 function get_next_id() { $SQL = "select nextval('table_id_seq')"; $response = $this->db_query($SQL); $arr = pg_fetch_array($query_response, NULL, PGSQL_ASSOC); $id = (empty($arr['nextval'])) ? 'NULL' : intval($arr['nextval']); $new_id = $id-1; $SQL = "select setval('table_id_seq', {$new_id})"; $this->db_query($SQL); return $id; } 

I use SELECT nextval('table_id_seq') to get the next identifier in the sequence. As this extends the sequence I, immediately use the SELECT setval('table_id_seq',$id) to return the sequence to its original value. Thus, when the user sends data, and the code finally ends up in the INSERT statement, it automatically increments, and the identifier before and after insertion is identical.

While this works for me, I'm not too hot on postgreSQL and wondering if this might cause any problems on the line, or if this is not the best method? Is there no way to check the next value of a sequence without automatically increasing it?

If this helps, I use postgresql 7.2

Answer 1

Disclaimer: not sure of 7.2, as I have never used this.

Obviously, your identifier column is defined to get the default value from a sequence (probably because it is defined as serial , although I don't know if it was available in 7.x).

If you delete the default value but save the sequence, you can get the next identifier using nextval() before inserting a new line.

Removing the default value for a column will require that you always specify the identifier during insertion (by extracting it from the sequence). If you do it anyway, I don't see a problem. If you want to serve both scenarios, create a trigger before inserting (do they have 7?), Which checks if the identifier column has a value if it does not extract a new value from the sequence, otherwise leave it alone.

The real question is: why do you need an identifier before inserting. You can just send the string to the server and then get the generated identifier by calling curval()

But then again: you really have to (I mean really ) talk to the client in order to upgrade to the latest version of Postgres

Answer 2

People - there are reasons to get an identifier before inserting a record. For example, I have an application that stores an identifier as part of text pasted into another field. There are only two ways to do this.

1) Regardless of the method, get the identifier before inserting to be included in my INSERT statement
2) INSERT, get the identifier (again, no matter how (SELECT ... or INSERT ... RETURNING id;)), update the text field of the record that includes the identifier

Many of the comments and answers suggested that the OP is doing something wrong ... what ... is wrong. The OP clearly stated: "In principle, I need an article identifier before it is inserted into the database." It doesn't matter why the OP wants / needs to do this - just answer the question.

My decision decided to get the identifier in front; so I do nextval () and setval () as needed to achieve my desired result.