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…