A lot of charts, tables and dashboards that are developed using series values such as time series. Amazon Redshift prior to the PostgreSQL 8.4 was not supported generate series function. In this article, we will check how to use Redshift generate_series function, its usage and example. Page Content Introduction Redshift generate_series() Function Redshift generate_series Function Syntax Redshift generate_series Function Example Generate Series in Reverse Order Generate Date Series in Redshift Generate Series Function with INSERT Statement Conclusion Introduction Redshift generate_series function is a powerful tool that is widely used in…

It is common to store values in the form of array in the database. Many databases like Netezza, PostgreSQL supports array functions to manipulate the array types. However, Amazon Redshift does not support array types. In this article, we will check alternative method that you can use. Amazon Redshift Array Support As mentioned in the previous section, Amazon Redshift does not support array types or functions. Though, Redshift uses PostgreSQL, but they yet to provide support to arrays. You can check unsupported features in the official documentation. Best part is,…

Just like using a right distribution style, selecting right sort key is important to boost your Redshift database performance. How data is stored onto the disk blocks defines the performance of the queries that you are running against those tables. It is not that easy to change the sort key once defined. In this article, we will check how to change Redshift table sort key with an example. Page Content Introduction Understanding the Redshift Table Sort Key Why to Change Redshift Table Sort Key? How to Change Redshift Table Sort…

Choosing right distribution style is one of the important factors to improve the performance of Redshift Database. Distribution style will directly affect the performance of your query. Table with wrong distribution style might hamper your Redshift cluster because of workload. In this article, we will check how to change Redshift table distribution style with an example. Why to Change Redshift Table Distribution style? There are three types of distribution style available in Redshift: EVEN distributionKEY distributionALL distribution You can choose any of the style based on your data, size and…

When you work on relatively big Enterprise data warehouse (EDW), you will have a large number of tables with different structures. The table structure includes, column name, type of data type, distribution style or sort key use. Redshift does not provide show (list) or describe SQL command. Though, you can use psql command line options. In this article, we will check what are Redshift show and describe table command alternative with an examples. Redshift Show and Describe Table Command Alternative As mentioned earlier, the Redshift SQL reference does not provide…

Redshift do not have a primary or unique key. You can define primary, Foreign or unique key constraints, but Redshift will not enforce them. You can insert the duplicate records in the Redshift table. There are no constraints to ensure uniqueness or primary key, but if you have a table and have loaded data twice, then you can de-duplicate in several ways. Below methods explain you how to identify and Remove duplicate records from Redshift table. Remove Duplicate Records from Redshift Table There are many methods that you can use…

The cumulative sum or running total is one of the interesting problems where you have to calculate the sum or average using current result and previous row value. Most of the modern analytical database line Netezza, Teradata, Oracle, Vertica provides supports to analytical functions. You can make use of those analytical functions along with window specification to calculate the cumulative sum and average. In this article, we will check how to calculate Redshift Cumulative Sum (running total) or cumulative average with some examples. Redshift Cumulative Sum As explained earlier, cumulative…

Redshift lateral column alias sometimes called derived columns are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports lateral column alias. Recently Redshift also started supporting lateral column alias reference. In this post, lets us explore that new feature. Page Content Introduction Why Use Lateral Column Alias in Redshift? How…