Greenplum Sequence and its Usage

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Greenplum
  • Reading time:4 mins read

Like any other data warehouse appliances, Greenplum has sequences. Greenplum sequence is an auto number generator. These sequence then can be used in any SQL statements.

Greenplum Sequence

Greenplum Sequence Overview

CREATE SEQUENCE creates a new sequence number generator. This command willalso creates the special single-row table and initialize it. Sequence will be owned by the user creating it.

Syntax:

CREATE SEQUENCE name [Options]

Following are the options associated with Greenplum sequence.

[INCREMENT [BY] value] 
[MINVALUE minvalue | NO MINVALUE] 
[MAXVALUE maxvalue | NO MAXVALUE] 
[START [ WITH ] start] 
[CACHE cache] 
[[NO] CYCLE] 
[OWNED BY { table.column | NONE }]

Meaning

  • The default minvalue is no minvalue, which is 1.
  • Default maxvalue is no maxvalue and is the largest value by particular data type that the sequence can hold.
  • Default start value is the minvalue for an increasing sequence, and the maxvalue for a decreasing sequence.
  • The Start value has to be within the range of the minvalue and maxvalue.
  • The default increment is 1.
  • Sequence can be associated with any tables or none.
  • By default, sequences do not cycle

Greenplum Sequence Example

Below is the example of Greenplum Sequence

CREATE SEQUENCE sequence1 
START WITH 1 
INCREMENT BY 1
MINVALUE 1 
NO MAXVALUE
NO CYCLE;

Getting Value from the Greenplum Sequence

You can use the NEXTVAL(‘name’) to get the value from Greenplum sequence.

For example,

select nextval('sequence1');

and

INSERT INTO table VALUES(nextval('sequence1'),'abc');

Check Current Status of Sequence

Examine the parameters and current state of a sequence by using query as follows;

SELECT * FROM sequence_name;

System will display all options values avaialable. In that, the last_value field of the sequence shows the last value allocated by any session.

Alter Sequence

You can alter a user sequence by resetting any sequence options, including the name and owner of the sequence.

To alter a sequence, use the ALTER SEQUENCE statement and specify the options in any order.

ALTER SEQUENCE sequence_name [options];

Where options can be;

[INCREMENT [ BY ] increment]
[MINVALUE minvalue | NO MINVALUE] 
[MAXVALUE maxvalue | NO MAXVALUE] 
[RESTART [ WITH ] start]
[CACHE cache]
[[ NO ] CYCLE] 
[OWNED BY {table.column| NONE}]

For Example, resets increment value, use alter statement as below;

 ALTER SEQUENCE sequence1 INCREMENT BY 2;

Limitations on Greenplum Sequences

There are some limitation on the Greenplum sequence.

  • lastvaland currval functions are not supported.
  • setvalcan only be used to set the value of the sequence generator on the master, it cannot be used in subqueries to update records on distributed table data.
  • nextvalsometimes grabs a block of values from the master for a segment to use, depending on the query. So the sequence values may not be in sequencial order. This issue exists in other PostgreSQL database such as Netezza, Redshift

Restriction on use of sequence

You cannot use NEXTVAL in the following statements:

  • CASE expressions
  • WHERE clauses
  • ORDER BY clauses
  • aggregate functions
  • window functions
  • grouped queries
  • SELECT distinct

Dropping a Sequence

To drop a sequence, use the DROP SEQUENCE statement and specify the sequence name which you want to drop.

DROP SEQUENCE <sequence name>;

System will lock if you want to drop sequence that is in use.

Tags: , ,