diff options
Diffstat (limited to 'doc/src/sgml/html/sql-altersequence.html')
| -rw-r--r-- | doc/src/sgml/html/sql-altersequence.html | 165 |
1 files changed, 165 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-altersequence.html b/doc/src/sgml/html/sql-altersequence.html new file mode 100644 index 0000000..7bed788 --- /dev/null +++ b/doc/src/sgml/html/sql-altersequence.html @@ -0,0 +1,165 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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>ALTER SEQUENCE</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="sql-alterschema.html" title="ALTER SCHEMA" /><link rel="next" href="sql-alterserver.html" title="ALTER SERVER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER SEQUENCE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterschema.html" title="ALTER SCHEMA">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterserver.html" title="ALTER SERVER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERSEQUENCE"><div class="titlepage"></div><a id="id-1.9.3.30.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER SEQUENCE</span></h2><p>ALTER SEQUENCE — + change the definition of a sequence generator + </p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> + [ AS <em class="replaceable"><code>data_type</code></em> ] + [ INCREMENT [ BY ] <em class="replaceable"><code>increment</code></em> ] + [ MINVALUE <em class="replaceable"><code>minvalue</code></em> | NO MINVALUE ] [ MAXVALUE <em class="replaceable"><code>maxvalue</code></em> | NO MAXVALUE ] + [ START [ WITH ] <em class="replaceable"><code>start</code></em> ] + [ RESTART [ [ WITH ] <em class="replaceable"><code>restart</code></em> ] ] + [ CACHE <em class="replaceable"><code>cache</code></em> ] [ [ NO ] CYCLE ] + [ OWNED BY { <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em> | NONE } ] +ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET { LOGGED | UNLOGGED } +ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } +ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em> +ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET SCHEMA <em class="replaceable"><code>new_schema</code></em> +</pre></div><div class="refsect1" id="id-1.9.3.30.5"><h2>Description</h2><p> + <code class="command">ALTER SEQUENCE</code> changes the parameters of an existing + sequence generator. Any parameters not specifically set in the + <code class="command">ALTER SEQUENCE</code> command retain their prior settings. + </p><p> + You must own the sequence to use <code class="command">ALTER SEQUENCE</code>. + To change a sequence's schema, you must also have <code class="literal">CREATE</code> + privilege on the new schema. + To alter the owner, you must also be a direct or indirect member of the new + owning role, and that role must have <code class="literal">CREATE</code> privilege on + the sequence's schema. (These restrictions enforce that altering the owner + doesn't do anything you couldn't do by dropping and recreating the sequence. + However, a superuser can alter ownership of any sequence anyway.) + </p></div><div class="refsect1" id="id-1.9.3.30.6"><h2>Parameters</h2><p> + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a sequence to be altered. + </p></dd><dt><span class="term"><code class="literal">IF EXISTS</code></span></dt><dd><p> + Do not throw an error if the sequence does not exist. A notice is issued + in this case. + </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p> + The optional + clause <code class="literal">AS <em class="replaceable"><code>data_type</code></em></code> + changes the data type of the sequence. Valid types are + <code class="literal">smallint</code>, <code class="literal">integer</code>, + and <code class="literal">bigint</code>. + </p><p> + Changing the data type automatically changes the minimum and maximum + values of the sequence if and only if the previous minimum and maximum + values were the minimum or maximum value of the old data type (in + other words, if the sequence had been created using <code class="literal">NO + MINVALUE</code> or <code class="literal">NO MAXVALUE</code>, implicitly or + explicitly). Otherwise, the minimum and maximum values are preserved, + unless new values are given as part of the same command. If the + minimum and maximum values do not fit into the new data type, an error + will be generated. + </p></dd><dt><span class="term"><em class="replaceable"><code>increment</code></em></span></dt><dd><p> + The clause <code class="literal">INCREMENT BY <em class="replaceable"><code>increment</code></em></code> is + optional. A positive value will make an ascending sequence, a + negative one a descending sequence. If unspecified, the old + increment value will be maintained. + </p></dd><dt><span class="term"><em class="replaceable"><code>minvalue</code></em><br /></span><span class="term"><code class="literal">NO MINVALUE</code></span></dt><dd><p> + The optional clause <code class="literal">MINVALUE <em class="replaceable"><code>minvalue</code></em></code> determines + the minimum value a sequence can generate. If <code class="literal">NO + MINVALUE</code> is specified, the defaults of 1 and + the minimum value of the data type for ascending and descending sequences, + respectively, will be used. If neither option is specified, + the current minimum value will be maintained. + </p></dd><dt><span class="term"><em class="replaceable"><code>maxvalue</code></em><br /></span><span class="term"><code class="literal">NO MAXVALUE</code></span></dt><dd><p> + The optional clause <code class="literal">MAXVALUE <em class="replaceable"><code>maxvalue</code></em></code> determines + the maximum value for the sequence. If <code class="literal">NO + MAXVALUE</code> is specified, the defaults of + the maximum value of the data type and -1 for ascending and descending + sequences, respectively, will be used. If neither option is + specified, the current maximum value will be maintained. + </p></dd><dt><span class="term"><em class="replaceable"><code>start</code></em></span></dt><dd><p> + The optional clause <code class="literal">START WITH <em class="replaceable"><code>start</code></em></code> changes the + recorded start value of the sequence. This has no effect on the + <span class="emphasis"><em>current</em></span> sequence value; it simply sets the value + that future <code class="command">ALTER SEQUENCE RESTART</code> commands will use. + </p></dd><dt><span class="term"><em class="replaceable"><code>restart</code></em></span></dt><dd><p> + The optional clause <code class="literal">RESTART [ WITH <em class="replaceable"><code>restart</code></em> ]</code> changes the + current value of the sequence. This is similar to calling the + <code class="function">setval</code> function with <code class="literal">is_called</code> = + <code class="literal">false</code>: the specified value will be returned by the + <span class="emphasis"><em>next</em></span> call of <code class="function">nextval</code>. + Writing <code class="literal">RESTART</code> with no <em class="replaceable"><code>restart</code></em> value is equivalent to supplying + the start value that was recorded by <code class="command">CREATE SEQUENCE</code> + or last set by <code class="command">ALTER SEQUENCE START WITH</code>. + </p><p> + In contrast to a <code class="function">setval</code> call, + a <code class="literal">RESTART</code> operation on a sequence is transactional + and blocks concurrent transactions from obtaining numbers from the + same sequence. If that's not the desired mode of + operation, <code class="function">setval</code> should be used. + </p></dd><dt><span class="term"><em class="replaceable"><code>cache</code></em></span></dt><dd><p> + The clause <code class="literal">CACHE <em class="replaceable"><code>cache</code></em></code> enables + sequence numbers to be preallocated and stored in memory for + faster access. The minimum value is 1 (only one value can be + generated at a time, i.e., no cache). If unspecified, the old + cache value will be maintained. + </p></dd><dt><span class="term"><code class="literal">CYCLE</code></span></dt><dd><p> + The optional <code class="literal">CYCLE</code> key word can be used to enable + the sequence to wrap around when the + <em class="replaceable"><code>maxvalue</code></em> or + <em class="replaceable"><code>minvalue</code></em> has been + reached by + an ascending or descending sequence respectively. If the limit is + reached, the next number generated will be the + <em class="replaceable"><code>minvalue</code></em> or + <em class="replaceable"><code>maxvalue</code></em>, + respectively. + </p></dd><dt><span class="term"><code class="literal">NO CYCLE</code></span></dt><dd><p> + If the optional <code class="literal">NO CYCLE</code> key word is + specified, any calls to <code class="function">nextval</code> after the + sequence has reached its maximum value will return an error. + If neither <code class="literal">CYCLE</code> or <code class="literal">NO + CYCLE</code> are specified, the old cycle behavior will be + maintained. + </p></dd><dt><span class="term"><code class="literal">SET { LOGGED | UNLOGGED }</code></span></dt><dd><p> + This form changes the sequence from unlogged to logged or vice-versa + (see <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>). It cannot be applied to a + temporary sequence. + </p></dd><dt><span class="term"><code class="literal">OWNED BY</code> <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em><br /></span><span class="term"><code class="literal">OWNED BY NONE</code></span></dt><dd><p> + The <code class="literal">OWNED BY</code> option causes the sequence to be + associated with a specific table column, such that if that column + (or its whole table) is dropped, the sequence will be automatically + dropped as well. If specified, this association replaces any + previously specified association for the sequence. The specified + table must have the same owner and be in the same schema as the + sequence. + Specifying <code class="literal">OWNED BY NONE</code> removes any existing + association, making the sequence <span class="quote">“<span class="quote">free-standing</span>”</span>. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p> + The user name of the new owner of the sequence. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p> + The new name for the sequence. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p> + The new schema for the sequence. + </p></dd></dl></div><p> + </p></div><div class="refsect1" id="id-1.9.3.30.7"><h2>Notes</h2><p> + <code class="command">ALTER SEQUENCE</code> will not immediately affect + <code class="function">nextval</code> results in backends, + other than the current one, that have preallocated (cached) sequence + values. They will use up all cached values prior to noticing the changed + sequence generation parameters. The current backend will be affected + immediately. + </p><p> + <code class="command">ALTER SEQUENCE</code> does not affect the <code class="function">currval</code> + status for the sequence. (Before <span class="productname">PostgreSQL</span> + 8.3, it sometimes did.) + </p><p> + <code class="command">ALTER SEQUENCE</code> blocks + concurrent <code class="function">nextval</code>, <code class="function">currval</code>, + <code class="function">lastval</code>, and <code class="command">setval</code> calls. + </p><p> + For historical reasons, <code class="command">ALTER TABLE</code> can be used with + sequences too; but the only variants of <code class="command">ALTER TABLE</code> + that are allowed with sequences are equivalent to the forms shown above. + </p></div><div class="refsect1" id="id-1.9.3.30.8"><h2>Examples</h2><p> + Restart a sequence called <code class="literal">serial</code>, at 105: +</p><pre class="programlisting"> +ALTER SEQUENCE serial RESTART WITH 105; +</pre></div><div class="refsect1" id="id-1.9.3.30.9"><h2>Compatibility</h2><p> + <code class="command">ALTER SEQUENCE</code> conforms to the <acronym class="acronym">SQL</acronym> + standard, except for the <code class="literal">AS</code>, <code class="literal">START WITH</code>, + <code class="literal">OWNED BY</code>, <code class="literal">OWNER TO</code>, <code class="literal">RENAME TO</code>, and + <code class="literal">SET SCHEMA</code> clauses, which are + <span class="productname">PostgreSQL</span> extensions. + </p></div><div class="refsect1" id="id-1.9.3.30.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>, <a class="xref" href="sql-dropsequence.html" title="DROP SEQUENCE"><span class="refentrytitle">DROP SEQUENCE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterschema.html" title="ALTER SCHEMA">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterserver.html" title="ALTER SERVER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER SCHEMA </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER SERVER</td></tr></table></div></body></html>
\ No newline at end of file |