title: Get next available auto_increment ID in PostgreSQL - A better approach?
body: <p>I'm new to postgreSQL, so would really appreciate any pointers from the community.</p>
  
  <p>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?</p>
  
  <p>At first I thought I could use <code>SELECT max(id) from tbl_name</code>, 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.</p>
  
  <p>As the article isn't in the database yet, and a database session hasn't started, it seems I can't use the <code>currval()</code> functionality of postgreSQL. Furthermore if I use <code>nextval()</code> 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). </p>
  
  <p>The way I am getting around it at the moment is as follows:</p>
  
  <pre><code>function get_next_id()
  {
      $SQL = "select nextval('table_id_seq')";
      $response = $this-&gt;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-&gt;db_query($SQL);
  
      return $id;
  }
  </code></pre>
  
  <p>I use <code>SELECT nextval('table_id_seq')</code> to get the next ID in the sequence. As this increments the sequence I then immediately use <code>SELECT setval('table_id_seq',$id)</code> 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.</p>
  
  <p>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?</p>
  
  <p>If it helps I'm using postgresql 7.2</p>