codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.17. Sequence Manipulation Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-json.html" title="9.16. JSON Functions and Operators" /><link rel="next" href="functions-conditional.html" title="9.18. Conditional Expressions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.17. Sequence Manipulation Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-json.html" title="9.16. JSON Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-conditional.html" title="9.18. Conditional Expressions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-SEQUENCE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.17. Sequence Manipulation Functions <a href="#FUNCTIONS-SEQUENCE" class="id_link">#</a></h2></div></div></div><a id="id-1.5.8.23.2" class="indexterm"></a><p>3   This section describes functions for operating on <em class="firstterm">sequence4   objects</em>, also called sequence generators or just sequences.5   Sequence objects are special single-row tables created with <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>.6   Sequence objects are commonly used to generate unique identifiers7   for rows of a table.  The sequence functions, listed in <a class="xref" href="functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE" title="Table 9.52. Sequence Functions">Table 9.52</a>, provide simple, multiuser-safe8   methods for obtaining successive sequence values from sequence9   objects.10  </p><div class="table" id="FUNCTIONS-SEQUENCE-TABLE"><p class="title"><strong>Table 9.52. Sequence Functions</strong></p><div class="table-contents"><table class="table" summary="Sequence Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">11        Function12       </p>13       <p>14        Description15       </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">16        <a id="id-1.5.8.23.4.2.2.1.1.1.1" class="indexterm"></a>17        <code class="function">nextval</code> ( <code class="type">regclass</code> )18        → <code class="returnvalue">bigint</code>19       </p>20       <p>21        Advances the sequence object to its next value and returns that value.22        This is done atomically: even if multiple sessions23        execute <code class="function">nextval</code> concurrently, each will safely24        receive a distinct sequence value.25        If the sequence object has been created with default parameters,26        successive <code class="function">nextval</code> calls will return successive27        values beginning with 1.  Other behaviors can be obtained by using28        appropriate parameters in the <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>29        command.30      </p>31       <p>32        This function requires <code class="literal">USAGE</code>33        or <code class="literal">UPDATE</code> privilege on the sequence.34       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">35        <a id="id-1.5.8.23.4.2.2.2.1.1.1" class="indexterm"></a>36        <code class="function">setval</code> ( <code class="type">regclass</code>, <code class="type">bigint</code> [<span class="optional">, <code class="type">boolean</code> </span>] )37        → <code class="returnvalue">bigint</code>38       </p>39       <p>40        Sets the sequence object's current value, and optionally41        its <code class="literal">is_called</code> flag.  The two-parameter42        form sets the sequence's <code class="literal">last_value</code> field to the43        specified value and sets its <code class="literal">is_called</code> field to44        <code class="literal">true</code>, meaning that the next45        <code class="function">nextval</code> will advance the sequence before46        returning a value.  The value that will be reported47        by <code class="function">currval</code> is also set to the specified value.48        In the three-parameter form, <code class="literal">is_called</code> can be set49        to either <code class="literal">true</code>50        or <code class="literal">false</code>.  <code class="literal">true</code> has the same51        effect as the two-parameter form. If it is set52        to <code class="literal">false</code>, the next <code class="function">nextval</code>53        will return exactly the specified value, and sequence advancement54        commences with the following <code class="function">nextval</code>.55        Furthermore, the value reported by <code class="function">currval</code> is not56        changed in this case.  For example,57</p><pre class="programlisting">58SELECT setval('myseq', 42);           <em class="lineannotation"><span class="lineannotation">Next <code class="function">nextval</code> will return 43</span></em>59SELECT setval('myseq', 42, true);     <em class="lineannotation"><span class="lineannotation">Same as above</span></em>60SELECT setval('myseq', 42, false);    <em class="lineannotation"><span class="lineannotation">Next <code class="function">nextval</code> will return 42</span></em>61</pre><p>62        The result returned by <code class="function">setval</code> is just the value of its63        second argument.64       </p>65       <p>66        This function requires <code class="literal">UPDATE</code> privilege on the67        sequence.68       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">69        <a id="id-1.5.8.23.4.2.2.3.1.1.1" class="indexterm"></a>70        <code class="function">currval</code> ( <code class="type">regclass</code> )71        → <code class="returnvalue">bigint</code>72       </p>73       <p>74        Returns the value most recently obtained75        by <code class="function">nextval</code> for this sequence in the current76        session.  (An error is reported if <code class="function">nextval</code> has77        never been called for this sequence in this session.)  Because this is78        returning a session-local value, it gives a predictable answer whether79        or not other sessions have executed <code class="function">nextval</code> since80        the current session did.81       </p>82       <p>83        This function requires <code class="literal">USAGE</code>84        or <code class="literal">SELECT</code> privilege on the sequence.85       </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">86        <a id="id-1.5.8.23.4.2.2.4.1.1.1" class="indexterm"></a>87        <code class="function">lastval</code> ()88        → <code class="returnvalue">bigint</code>89       </p>90       <p>91        Returns the value most recently returned by92        <code class="function">nextval</code> in the current session. This function is93        identical to <code class="function">currval</code>, except that instead94        of taking the sequence name as an argument it refers to whichever95        sequence <code class="function">nextval</code> was most recently applied to96        in the current session. It is an error to call97        <code class="function">lastval</code> if <code class="function">nextval</code>98        has not yet been called in the current session.99       </p>100       <p>101        This function requires <code class="literal">USAGE</code>102        or <code class="literal">SELECT</code> privilege on the last used sequence.103       </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="caution"><h3 class="title">Caution</h3><p>104    To avoid blocking concurrent transactions that obtain numbers from105    the same sequence, the value obtained by <code class="function">nextval</code>106    is not reclaimed for re-use if the calling transaction later aborts.107    This means that transaction aborts or database crashes can result in108    gaps in the sequence of assigned values.  That can happen without a109    transaction abort, too.  For example an <code class="command">INSERT</code> with110    an <code class="literal">ON CONFLICT</code> clause will compute the to-be-inserted111    tuple, including doing any required <code class="function">nextval</code>112    calls, before detecting any conflict that would cause it to follow113    the <code class="literal">ON CONFLICT</code> rule instead.114    Thus, <span class="productname">PostgreSQL</span> sequence115    objects <span class="emphasis"><em>cannot be used to obtain <span class="quote">“<span class="quote">gapless</span>”</span>116    sequences</em></span>.117   </p><p>118    Likewise, sequence state changes made by <code class="function">setval</code>119    are immediately visible to other transactions, and are not undone if120    the calling transaction rolls back.121   </p><p>122    If the database cluster crashes before committing a transaction123    containing a <code class="function">nextval</code>124    or <code class="function">setval</code> call, the sequence state change might125    not have made its way to persistent storage, so that it is uncertain126    whether the sequence will have its original or updated state after the127    cluster restarts.  This is harmless for usage of the sequence within128    the database, since other effects of uncommitted transactions will not129    be visible either.  However, if you wish to use a sequence value for130    persistent outside-the-database purposes, make sure that the131    <code class="function">nextval</code> call has been committed before doing so.132   </p></div><p>133   The sequence to be operated on by a sequence function is specified by134   a <code class="type">regclass</code> argument, which is simply the OID of the sequence in the135   <code class="structname">pg_class</code> system catalog.  You do not have to look up the136   OID by hand, however, since the <code class="type">regclass</code> data type's input137   converter will do the work for you.  See <a class="xref" href="datatype-oid.html" title="8.19. Object Identifier Types">Section 8.19</a>138   for details.139  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-json.html" title="9.16. JSON Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-conditional.html" title="9.18. Conditional Expressions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.16. JSON Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.18. Conditional Expressions</td></tr></table></div></body></html>