Get next available auto_increment ID in PostgreSQL - A better approach?
postgresql
Solution
Disclaimer: Not sure about 7.2 as I have never used that.
Apparently your ID column is defined to get its default value from the sequence (probably because it's defined as `serial` although I don't know if that was available in 7.x).
If you remove the default but keep the sequence, then you can retrieve the next ID using `nextval()` before inserting the new row.
Removing the default value for the column will require you to always provide an ID during insert (by retrieving it from the sequence). If you are doing that anyway, then I don't see a problem. If you want to cater for both scenarios, create a before insert trigger (does 7.x have them?) that checks if the ID column has a value, if not retrieve a new value from the sequence otherwise leave it alone.
The real question though is: why do you need the ID before insert. You could simply send the row to the server and then get the generated id by calling `curval()`
But again: you should really (I mean really) talk to the customer to upgrade to a recent version of Postgres
Problem
I'm new to postgreSQL, so would really appreciate any pointers from the community. I am updating some functionality in the CMS of a pretty old site I've just inherited. Basically, I need the ID of an article before it is inserted into the database. Is there anyway anyway to check the next value that will be used by a sequence before a database session (insert) has begun? At first I thought I could use `SELECT max(id) from tbl_name`, however as the id is auto incremented from a sequence and articles are often deleted, it obviously won't return a correct id for the next value in the sequence. As the article isn't in the database yet, and a database session hasn't started, it seems I can't use the `currval()` functionality of postgreSQL. Furthermore if I use `nextval()` it auto increments the sequence before the data is inserted (the insert also auto-incrementing the sequence ending up with the sequence being doubly incremented). The way I am getting around it at the moment is as follows: ``` 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 ID in the sequence. As this increments the sequence I then immediately use `SELECT setval('table_id_seq',$id)` to set the sequence back to it's original value. That way when the user submits the data and the code finally hits the INSERT statement, it auto increments and the ID before the insert and after the insert are identical. While this works for me, I'm not too hot on postgreSQL and wonder if it could cause any problems down the line, or if their isn't a better method? Is there no way to check the next value of a sequence without auto-incrementing it? If it helps I'm using postgresql 7.2