create_sequence man page on IRIX

Man page or keyword search:  
man Server   31559 pages
apropos Keyword Search (all sections)
Output format
IRIX logo
[printable version]



     CREATE SEQUENCSQL)- Language Statements (2002-1CREATE SEQUENCE(l)

     NAME
	  CREATE SEQUENCE - define a new sequence generator

     SYNOPSIS
	  CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]
	      [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
	      [ START start ] [ CACHE cache ] [ CYCLE ]

	INPUTS
	  TEMPORARY or TEMP
	       If specified, the sequence object is created only for
	       this session, and is automatically dropped on session
	       exit.  Existing permanent sequences with the same name
	       are not visible (in this session) while the temporary
	       sequence exists, unless they are referenced with
	       schema-qualified names.

	  seqname
	       The name (optionally schema-qualified) of a sequence to
	       be created.

	  increment
	       The INCREMENT increment clause is optional. A positive
	       value will make an ascending sequence, a negative one a
	       descending sequence.  The default value is one (1).

	  minvalue
	       The optional clause MINVALUE minvalue determines the
	       minimum value a sequence can generate. The defaults are
	       1 and -2^63-1 for ascending and descending sequences,
	       respectively.

	  maxvalue
	       The optional clause MAXVALUE maxvalue determines the
	       maximum value for the sequence. The defaults are 2^63-1
	       and -1 for ascending and descending sequences,
	       respectively.

	  start
	       The optional START start clause enables the sequence to
	       begin anywhere.	The default starting value is minvalue
	       for ascending sequences and maxvalue for descending
	       ones.

	  cache
	       The CACHE cache option 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) and this is also the
	       default.

     Page 1					     (printed 3/24/03)

     CREATE SEQUENCSQL)- Language Statements (2002-1CREATE SEQUENCE(l)

	  CYCLE
	       The optional CYCLE keyword may be used to enable the
	       sequence to wrap around when the maxvalue or minvalue
	       has been reached by an ascending or descending sequence
	       respectively. If the limit is reached, the next number
	       generated will be the minvalue or maxvalue,
	       respectively.  Without CYCLE, after the limit is
	       reached nextval calls will return an error.

	OUTPUTS
	  CREATE SEQUENCE
	       Message returned if the command is successful.

	  ERROR: Relation 'seqname' already exists
	       If the sequence specified already exists.

     (max)
	  ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE
	       If the specified starting value is out of range.

     (min)
	  ERROR: DefineSequence: START value (start) can't be < MINVALUE
	       If the specified starting value is out of range.

	  ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max)
	       If the minimum and maximum values are inconsistent.

     DESCRIPTION
	  CREATE SEQUENCE will enter a new sequence number generator
	  into the current database. This involves creating and
	  initializing a new single-row table with the name seqname.
	  The generator will be owned by the user issuing the command.

	  If a schema name is given then the sequence is created in
	  the specified schema. Otherwise it is created in the current
	  schema (the one at the front of the search path; see
	  CURRENT_SCHEMA()).  TEMP sequences exist in a special
	  schema, so a schema name may not be given when creating a
	  TEMP sequence.  The sequence name must be distinct from the
	  name of any other sequence, table, index, or view in the
	  same schema.

	  After a sequence is created, you use the functions nextval,
	  currval and setval to operate on the sequence. These
	  functions are documented in the User's Guide.

	  Although you cannot update a sequence directly, you can use
	  a query like

	  SELECT * FROM seqname;

     Page 2					     (printed 3/24/03)

     CREATE SEQUENCSQL)- Language Statements (2002-1CREATE SEQUENCE(l)

	  to examine the parameters and current state of a sequence.
	  In particular, the last_value field of the sequence shows
	  the last value allocated by any backend process. (Of course,
	  this value may be obsolete by the time it's printed, if
	  other processes are actively doing nextval calls.)

	       Caution: Unexpected results may be obtained if a cache
	       setting greater than one is used for a sequence object
	       that will be used concurrently by multiple backends.
	       Each backend will allocate and cache successive
	       sequence values during one access to the sequence
	       object and increase the sequence object's last_value
	       accordingly. Then, the next cache-1 uses of nextval
	       within that backend simply return the preallocated
	       values without touching the shared object. So, any
	       numbers allocated but not used within a session will be
	       lost when that session ends. Furthermore, although
	       multiple backends are guaranteed to allocate distinct
	       sequence values, the values may be generated out of
	       sequence when all the backends are considered. (For
	       example, with a cache setting of 10, backend A might
	       reserve values 1..10 and return nextval=1, then backend
	       B might reserve values 11..20 and return nextval=11
	       before backend A has generated nextval=2.) Thus, with a
	       cache setting of one it is safe to assume that nextval
	       values are generated sequentially; with a cache setting
	       greater than one you should only assume that the
	       nextval values are all distinct, not that they are
	       generated purely sequentially.  Also, last_value will
	       reflect the latest value reserved by any backend,
	       whether or not it has yet been returned by nextval.
	       Another consideration is that a setval executed on such
	       a sequence will not be noticed by other backends until
	       they have used up any preallocated values they have
	       cached.

	NOTES
	  Use DROP SEQUENCE to remove a sequence.

	  Sequences are based on bigint arithmetic, so the range
	  cannot exceed the range of an eight-byte integer (-
	  9223372036854775808 to 9223372036854775807). On some older
	  platforms, there may be no compiler support for eight-byte
	  integers, in which case sequences use regular integer
	  arithmetic (range -2147483648 to +2147483647).

	  When cache is greater than one, each backend uses its own
	  cache to store preallocated numbers.	Numbers that are
	  cached but not used in the current session will be lost,
	  resulting in ``holes'' in the sequence.

     Page 3					     (printed 3/24/03)

     CREATE SEQUENCSQL)- Language Statements (2002-1CREATE SEQUENCE(l)

     USAGE
	  Create an ascending sequence called serial, starting at 101:

	  CREATE SEQUENCE serial START 101;

	  Select the next number from this sequence:

	  SELECT nextval('serial');

	  nextval
	  -------
	      114

	  Use this sequence in an INSERT:

	  INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

	  Update the sequence value after a COPY FROM:

	  BEGIN;
	      COPY distributors FROM 'input_file';
	      SELECT setval('serial', max(id)) FROM distributors;
	  END;

     COMPATIBILITY
	SQL92
	  CREATE SEQUENCE is a PostgreSQL language extension.  There
	  is no CREATE SEQUENCE statement in SQL92.

     Page 4					     (printed 3/24/03)

[top]

List of man pages available for IRIX

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net