Get next available auto_increment ID in PostgreSQL - A better approach?

Question

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

Answer 1

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

1) Regardless of the method, get the ID before inserting to include in my INSERT statement
2) INSERT, get the the ID (again, regardless of how (SELECT ... or from INSERT ... RETURNING id;)), update the record's text field that includes the ID

Many of the comments and answers assumed the OP was doing something wrong... which is... wrong. The OP clearly stated "Basically, I need the ID of an article before it is inserted into the database". It should not matter why the OP wants/needs to do this - just answer the question.

My solution opted to get the ID up front; so I do nextval() and setval() as necessary to achieve my needed result.

Answer 2

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