An internal data types in Greenplum are associated with time span. The interval data type allows you to store and manipulate a period of time in years, months, days, hours, minutes, seconds, etc. The interval values are very useful when doing date or time arithmetic. The size of Interval data type in Greenplum are 12 bytes that can store a period with the allowed range is from -178,000,000 years to 178,000,000 years. In this article, we will check Greenplum Interval Data Type. Greenplum Interval Data Type Syntax Below is the syntax of interval data types in…

The WITH clause in SQL is sometimes referred to as a common table expression (CTE), is an optional clause that will always precedes SELECT statement of the query block. WITH clause in Greenplum contains a sub-query that is defined as a temporary table like view. Each sub-query in the WITH clause is associated with a table name, an optional list of column names that CTE returns, and a query expression that evaluates to a table (usually a SELECT statement). In this article, we will check how to use Greenplum WITH…

Variable substitution is one of the important options that is widely used when you are executing any SQL scripts from the command line or from any shell scripts. Like any other databases such as Netezza, Redshift and Vertica, Greenplum also supports variable substitution using PostgreSQL or psql -v option. In this article, we will check how to execute a SQL script file using Greenplum psql variable substitution. We will also see some working example that uses Greenplum psql variable substitution method. Execute SQL Script File using Greenplum psql Variable Substitution…

On Greenplum MPP data warehouse appliance, you will be executing multiple SQL statement in the single SQL file. This is required when you are refreshing fact or dimension table by creating intermediate stage tables. In such a refresh SQL file, there will be multiple create, delete, and drop statements. You have to take care of any errors that might occur in any of the SQL statements in file. In this article, we will discuss about the Greenplum SET ON_ERROR_STOP variable to stop the execution in case one of the SQL…

In a data warehouse environment, most of the tables are secured. The database administrator will allow only authorized used to access records from the tables. The decision makers analyze the flat files created from the database tables to come up with business-critical decision. Saving results of a query or a database table in any relational database is one of the basic and important work. The created flat files or CSV files then be transported using any mode of electronic transport such as email, FTP, SFTP, etc. in this article, we…

A query plan is a sequence of step-like statements containing nodes that the Greenplum cost-based query optimizer uses to execute queries. Based on the complexity of the supplied SQL query, Greenplum planner generates different plan. In this article, we will check Greenplum Explain Command and its usage with some examples. Greenplum Explain Command The Greenplum EXPLAIN displays the query plan that the Greenplum planner generates for the supplied SQL query statement. These query plans are usually a tree plan of nodes. Each node in the plan represents a single operation, such as table scan, join,…