postgresql
  1. postgresql-sequence
PreviousNext

Sequence - (PostgreSQL Table)

In PostgreSQL, a sequence is a special kind of database object designed for use with auto-incrementing columns in a table. Sequences provide a way to automatically generate unique integer values for a column based on a predefined sequence. In this tutorial, we'll explore the syntax, examples, and uses of sequences in PostgreSQL.

Syntax

CREATE SEQUENCE sequence_name
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE min_value | NO MINVALUE ]
    [ MAXVALUE max_value | NO MAXVALUE ]
    [ START [ WITH ] start ]
    [ RESTART ]
    [ CACHE cache ]
    [ CYCLE | NO CYCLE ];
  • sequence_name: The name of the sequence you want to create.
  • INCREMENT: Specifies the increment value for the sequence. Defaults to 1.
  • MINVALUE: The minimum value of the sequence. Defaults to -9223372036854775808 or 1.
  • MAXVALUE: The maximum value of the sequence. Defaults to 9223372036854775807 or 263-1.
  • START WITH: The starting value of the sequence. Defaults to the min_value or 1.
  • RESTART: Resets the sequence to its starting value.
  • CACHE: The number of values to cache in memory for faster access. Defaults to 1.
  • CYCLE: If set, the sequence will start over again when it reaches max_value. Defaults to no cycle.

Example

Let's take a look at some examples of creating and using sequences in PostgreSQL.

To create a simple sequence:

CREATE SEQUENCE my_sequence;

To set the starting value to 10 and increment by 2:

CREATE SEQUENCE my_sequence
    START WITH 10
    INCREMENT BY 2;

To limit the sequence values between 10 and 20:

CREATE SEQUENCE my_sequence
    START WITH 10
    INCREMENT BY 1
    MAXVALUE 20;

To use a sequence in a table:

CREATE TABLE my_table (
    id INTEGER DEFAULT nextval('my_sequence'),
    name TEXT
);

Explanation

In the examples above, we created sequences with different configurations. The first example creates a simple sequence without any additional settings. The second example sets a starting value of 10 and increments by 2 with each new value generated from the sequence. The third example limits the sequence values between 10 and 20.

Finally, we used the sequence in a table definition. In this case, the INTEGER column "id" is defined with a DEFAULT value of nextval('my_sequence'), which means that each new row inserted into the table will automatically receive a unique integer value generated by the my_sequence sequence.

Use

Sequences are useful in scenarios where you need to generate unique integer values automatically, such as primary keys for tables. Sequences eliminate the need for manual management of unique ID values for each row inserted into a table.

Important Points

  • Sequences are not transaction-safe, so applications that have long transactions should use caution when using sequences.
  • The nextval() function in a query retrieves the next value from a sequence.
  • The currval() function in a query returns the current value of a sequence.
  • The setval() function can be used to set the current value of a sequence.

Summary

In this tutorial, we explored the syntax, examples, and uses of sequences in PostgreSQL. We saw how to create sequences with different configurations, use sequences in table definitions, and retrieve and set sequence values using various functions. With this knowledge, you can use sequences to automatically generate unique integer values without manual management for your PostgreSQL database tables.

Column AliasIdentity Column
Published on: