What are Redshift Sequence Alternative? – Example

In a database management system, sequence is a named object in an individual database, that can provide the unique value when get next value method. Usually, sequences are used to generate unique numbers that can be used as surrogate key value, for example, you can use that surrogate key as a primary key values. Redshift does not support sequences. But, however, there are alternative methods. In this article, we will check Redshift sequence alternative methods with some examples.

Page Contents

• Introduction to Amazon Redshift
• What are Sequence in SQL?
• Benefits of Sequences in SQL
• Redshift Sequence Alternative
  • Number tables
  • Redshift IDENTITY Columns
  • RANK() or ROW_NUMBER() window functions
• Conclusion

Introduction to Amazon Redshift

Amazon Redshift is a fully-managed, petabyte-scale data warehousing service in the AWS cloud. It is designed to handle large-scale data analytics and data warehousing workloads. Amazon Redshift is based on a columnar storage architecture, which allows for fast query performance and efficient storage utilization. It also provides a variety of advanced features, including automatic compression, parallel query execution, and support for a range of data loading options. Amazon Redshift is widely used by organizations of all sizes and industries to store and analyze large amounts of data.

Amazon Redshift does not currently support sequences. While sequences are a feature supported by some relational database management systems, such as PostgreSQL, which is the underlying database technology used by Amazon Redshift, this feature is not currently available in Amazon Redshift.

What are Sequence in SQL?

Sequence in SQL is a database object used to generate unique numerical values in a sequence. Sequences are useful for generating primary key values for tables or generating unique identifiers for rows in a table.

To create a sequence in database like Snowflake, you use the CREATE SEQUENCE statement, which specifies the starting value, increment value, and maximum value for the sequence. You can also specify whether the sequence should cycle back to the beginning when it reaches the maximum value or stop generating values.

CREATE SEQUENCE sequence1 
START WITH 1 
INCREMENT BY 1 
COMMENT = 'Positive Sequence';

Once a sequence is created, you can use the NEXTVAL function to generate the next value in the sequence. You can also use the CURRVAL function to retrieve the current value in the sequence without generating a new value.

SELECT sequence1.NEXTVAL;

+---------+
| NEXTVAL |
|---------|
|       1 |
+---------+

Benefits of Sequences in SQL

Sequences in SQL provide several benefits, including:

• Generating unique values: Sequences are a simple and efficient way to generate unique numerical values, which are useful for primary keys, foreign keys, and other situations where unique values are required.
• Efficiency: Sequences are optimized for performance, allowing for quick generation of unique values with minimal impact on database resources.
• Portability: Sequences are a widely supported feature in SQL databases, making it easy to transfer between different database management systems.
• Flexibility: Sequences can be customized to suit specific needs, such as setting a starting value or specifying the increment value for generated values.

Overall, sequences are used SQL for generating unique identifiers and ensuring consistency and accuracy in database tables. They are widely used in a variety of applications and can provide benefits in terms of efficiency, scalability, and flexibility.

Redshift Sequence Alternative

Being a column oriented database, as of now, Redshift does not support sequences explicitly. There are few methods you can auto generate sequence values.

• Number tables. create a table that hold unique numbers.
• Columns defined as IDENTITY(seed, step). Note that, numbers generated using IDENTITY may not be in sequential order.
• RANK() or ROW_NUMBER() window functions over the whole set. Note that, this can have some negative performance implications if you have a multi-node cluster. This method should be used as a last resolution.

A sequence value is an integer that you can use wherever you would use numeric values.

Create Number Table as a Redshift Sequence Alternative

The good news is Redshift now supports stored procedures. You can use stored procedure to auto generate sequence number.

For example, create sequence number table to hold all unique sequence numbers. Here are the steps that you can follow to generate a sequence number using Amazon Redshift SP.

1. Create a sequence number table is it is not exists already. Let say, seq_table which holds single column seq_num of type bigint.
2. Get max( seq_num ) value and assign it to variable.
3. Increment the variable value by 1.
4. Insert updated value into seq_table. And finally,
5. Return updated value as a unique sequence number.

Below are the stored procedures that demonstrate auto sequence number generation and how to use it.

Procedure to Generate Auto Sequence

CREATE OR REPLACE PROCEDURE create_squence(inout return_seq_num bigint) as $$
DECLARE
  seq_value bigint;
  cnt bigint;
  return_cnt bigint;
BEGIN
  cnt := 0;
  return_cnt := 0;
  EXECUTE 'create table if not exists seq_table(seq_num bigint)';
  SELECT nvl(max(seq_num), 0) INTO cnt from seq_table;
  cnt := cnt + 1;
  EXECUTE 'INSERT INTO seq_table VALUES ('||cnt||')';
  SELECT max(seq_num) INTO return_seq_num from seq_table; 
END;
$$ LANGUAGE plpgsql;

Procedure to use Sequence Value

CREATE OR REPLACE PROCEDURE call_create_squence() as $$
DECLARE
  seq_value bigint := 0;
BEGIN
  call create_squence(seq_value);
  RAISE INFO 'Value is %', seq_value;
END;
$$ LANGUAGE plpgsql;

Output:

call call_create_squence();
INFO:  Relation "seq_table" already exists and will be skipped
INFO:  Value is 9

Another easy method is to Create table with IDENTITY column.

Create Redshift Table with IDENTITY column

An IDENTITY column in Redshift table contains unique auto-generated values. The data type for an IDENTITY column must be either INT or BIGINT.

For example, following example shows how to use identity column in Redshift database.

create table identity_test (id int identity(1,1), Name varchar(30));

Identity Clause that specifies that the column is an IDENTITY column.

Syntax:

IDENTITY(seed, step)

Where, seed specifies the starting value and step, a increment value.

You don’t have to specify the identity column in your insert statement as it is automatically inserted.

For example.

insert into identity_test(name) values('abc');
INSERT 0 1
insert into identity_test(name) values('bcd');
INSERT 0 1

select * from identity_test;
 id | name
----+------
  1 | abc
  2 | bcd
(2 rows)

Using RANK and ROW_NUMBER Analytical Function

You can use RANK() and ROW_NUMBER() analytical function to generate unique sequence number. But, not that this can have some negative performance implications if you have a multi-node cluster. This methods should be used as a last resolve.

You can more about analytical function in my other post – Redshift analytical Functions and Examples

Conclusion

In conclusion, while Amazon Redshift does not currently support sequences, there are several other features available for generating unique identifiers in tables. One such feature is the IDENTITY column, which automatically generates unique integer values for new rows inserted into a table. Additionally, the ROW_NUMBER analytic function can be used to generate unique row numbers within a result set. You can also create a sequence number table to keep track of unique numbers.

While sequences offer several benefits, such as consistency and efficiency, these other features can provide similar functionality in Amazon Redshift. Ultimately, the best option for generating unique identifiers will depend on the specific needs and requirements of the database and the number of tables involved.

Related Articles

• Redshift ROWNUM Pseudo Column Alternative

Hope this helps 🙂