Redshift Analyze command is used to collect the statistics on the tables that query planner uses to create optimal query execution plan using Redshift Explain command. Analyze command obtain sample records from the tables, calculate and store the statistics in STL_ANALYZE table. You can generate statistics on entire tables or on subset of columns. You can specify comma-separated column list for analyze command. How Statistics are collected? Redshift collects statistics in various ways. Statistics are automatically collected for certain database operations. Collect statistics for entire table or subset of columns…

Redshift VACUUM command is used to reclaim disk space and resorts the data within specified tables or within all tables in Redshift database. When you delete or update data from the table, Redshift logically deletes those records by marking it for delete. Vacuum command is used to reclaim disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. This command also sorts the data within the tables when specified.  Read: Redshift ANALYZE Command to Collect Statistics and Best Practices Commonly used Redshift Date Functions…

PostgreSQL (psql) is interactive terminal, you can type the queries and get output on terminal. After you create an Amazon Redshift cluster, you can access it using a terminal-based front end from PostgreSQL, psql, to query the data in your Redshift database. Steps to Connect to Redshift cluster using PostgreSQL - psql Below are the steps you can follow to connect to Redshift cluster using PostgreSQL - psql: Install PostgreSQL on Ubuntu – psql If you are using Ubuntu, you can use apt-get to install PostgreSQL. Below is the command:…

Redshift Unique key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create Unique key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. Read: Redshift Primary Key Constraint and SyntaxAmazon Redshift Foreign Key Constraint and Syntax Redshift Unique Key Constraint Syntax…

Redshift foreign key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create foreign key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. Read: Amazon Redshift Primary Key Constraint and Syntax Redshift Unique Key Constraint and Syntax Redshift Foreign Key Constraint…

Redshift Primary key constraint is informational only; they are not enforced by Amazon Redshift. Amazon data warehouse appliance supports referential integrity constraints such as Redshift primary key, foreign key, and unique keys as part of SQL-92 standard requirement. You can create primary key constraint while creating tables in Redshift database but it will not be enforced while loading Redshift tables. Redshift query planner uses these constraints to create better query execution plan. If Primary key is set at the column level, it must be on a single column. If PRIMARY…

Data type is an attribute that specifies type of data of any object that is going to be stored in that particular column. Each column, variable and expression has related data type in SQL. However, different database offers the different data types for columns. Redshift data types are almost similar to what the traditional RDBMS supports. Amazon Redshift data types are similar to IBM Netezza data types. When you issue Redshift create table command each column in a database tables must have name and a data type associated with it.…

Redshift WITH Clause is an optional clause that always precedes SELECT clause in the query statements. WITH clause has a subquery that is defined as a temporary tables similar to View definition. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a query expression that evaluates to a table (usually a SELECT statement). In SQL, WITH clause are commonly referred to as Common Table Expressions (CTE). A CTE or WITH clause is a syntactical sugar for a subquery. Where you can use…

Redshift is a data warehousing solution provided by Amazon Web Services (AWS). It supports a variety of date and time functions that can be used to perform operations on date and time data stored in Redshift tables. This article is about detailed descriptions and examples of the commonly used Amazon Redshift date functions that you can use to manipulate date data types in Resdshift SQL. In the real word scenarios many application manipulate the date and time data types. Date types are highly formatted and very complicated. Each date value…