Greenplum Create, Rename, Drop Database and Examples

You can create, drop or rename the database in Greenplum using respective commands. These are some important commands you should know if you are working as a Greenplum database administrator. In this article, we will check Greenplum create, drop, rename database commands and some of the examples. Greenplum Create, Rename, Drop Database Creating, altering or dropping database would be your daily job if you are a database administrator. Below are some of the important commands that might help you. Greenplum Create Database CREATE DATABASE creates a new database. To create a…

Continue ReadingGreenplum Create, Rename, Drop Database and Examples
Comments Off on Greenplum Create, Rename, Drop Database and Examples

Greenplum Create Database Error and Resolution

Pivotal Greenplum is one of widely used MPP relational databases. You can handle huge amount of data with Greenplum. Sometimes, it is very difficult to create a database in the Greenplum server because of connection issue. In this article, we will check how to resolve Greenplum create database error and steps required to resolve. Greenplum Create Database Error Below are the most common database errors that you may face while creating a database on Greenplum server. psql: FATAL: database "gpadmin" does not existERROR: source database "template1" is being accessed by…

Continue ReadingGreenplum Create Database Error and Resolution
Comments Off on Greenplum Create Database Error and Resolution

Greenplum Interval Data Type and Conversion Examples

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…

Continue ReadingGreenplum Interval Data Type and Conversion Examples
Comments Off on Greenplum Interval Data Type and Conversion Examples

Greenplum Different Joins and Examples

Joins in SQL are used to retrieve or combine specific columns from two or more tables based on common data columns available in the tables. Columns from multiple tables are integrated using different joins. In this article, we will check Greenplum different joins and demonstrate using live examples. These joins are same as PostgreSQL joins. Test Data Below are the tables and associated data that we will be using to demonstrate different joins available in Greenplum database. Table1: template1=# select * from table1; id | name | city ----+-------+----------- 1…

Continue ReadingGreenplum Different Joins and Examples
Comments Off on Greenplum Different Joins and Examples

Greenplum WITH Clause Syntax, Usage and Examples

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…

Continue ReadingGreenplum WITH Clause Syntax, Usage and Examples
Comments Off on Greenplum WITH Clause Syntax, Usage and Examples

Execute SQL Script File using Greenplum psql Variable Substitution

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…

Continue ReadingExecute SQL Script File using Greenplum psql Variable Substitution
Comments Off on Execute SQL Script File using Greenplum psql Variable Substitution

Greenplum SET ON_ERROR_STOP using psql and Examples

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…

Continue ReadingGreenplum SET ON_ERROR_STOP using psql and Examples
Comments Off on Greenplum SET ON_ERROR_STOP using psql and Examples

How to Export Greenplum Query Results into CSV?

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…

Continue ReadingHow to Export Greenplum Query Results into CSV?
Comments Off on How to Export Greenplum Query Results into CSV?

Greenplum Explain Command and its Usage

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,…

Continue ReadingGreenplum Explain Command and its Usage
Comments Off on Greenplum Explain Command and its Usage

How to Connect Greenplum using JDBC Driver from Python?

The Greenplum database is one of the widely used MPP machine. There are many methods that you can use to connect to Greenplum, for example, you can use JDBC, ODBC, etc. The Greenplum analytics database support connection from any programming language that supports JDBC driver. In this article, we will check how to connect Greenplum using JDBC driver from Python programming language. Connection string will remain same if you are using any programming language other than Python. Greenplum JDBC Driver The Greenplum database provides support to JDBC driver. You can…

Continue ReadingHow to Connect Greenplum using JDBC Driver from Python?
Comments Off on How to Connect Greenplum using JDBC Driver from Python?