alter_sequence(7) - BetterMan

—

NAME

ALTER_SEQUENCE - change the definition of a sequence generator

SYNOPSIS

bash

ALTER SEQUENCE [ IF EXISTS ] \nname\n
\n
    [ AS \ndata_type\n ]
\n
    [ INCREMENT [ BY ] \nincrement\n ]
\n
    [ MINVALUE \nminvalue\n | NO MINVALUE ] [ MAXVALUE \nmaxvalue\n | NO MAXVALUE ]
\n
    [ START [ WITH ] \nstart\n ]
\n
    [ RESTART [ [ WITH ] \nrestart\n ] ]
\n
    [ CACHE \ncache\n ] [ [ NO ] CYCLE ]
\n
    [ OWNED BY { \ntable_name\n.\ncolumn_name\n | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] \nname\n SET { LOGGED | UNLOGGED }
ALTER SEQUENCE [ IF EXISTS ] \nname\n OWNER TO { \nnew_owner\n | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] \nname\n RENAME TO \nnew_name\n
ALTER SEQUENCE [ IF EXISTS ] \nname\n SET SCHEMA \nnew_schema

DESCRIPTION

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.

You must own the sequence to use ALTER SEQUENCE. To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE 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.)

PARAMETERS

name

IF EXISTS

data_type

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 NO MINVALUE or NO MAXVALUE, 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.

increment

minvalue NO MINVALUE

maxvalue NO MAXVALUE

start

restart

In contrast to a setval call, a RESTART 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, setval should be used.

cache

CYCLE

NO CYCLE

SET { LOGGED | UNLOGGED }

OWNED BY table_name.column_name OWNED BY NONE

new_owner

new_name

new_schema

NOTES

ALTER SEQUENCE will not immediately affect nextval 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.

ALTER SEQUENCE does not affect the currval status for the sequence. (Before PostgreSQL 8.3, it sometimes did.)

ALTER SEQUENCE blocks concurrent nextval, currval, lastval, and setval calls.

For historical reasons, ALTER TABLE can be used with sequences too; but the only variants of ALTER TABLE that are allowed with sequences are equivalent to the forms shown above.

EXAMPLES

Restart a sequence called serial, at 105:

bash

ALTER SEQUENCE serial RESTART WITH 105;

COMPATIBILITY

ALTER SEQUENCE conforms to the SQL standard, except for the AS, START WITH, OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are PostgreSQL extensions.