Parse pg_dump Functions and Views Into Their Own Files (GreenPlum / Postgres)

Here's what we wanted: Each function from our GreenPlum (aka Postgres) database scripted out into it's own file. I think after much hacking I've got something working that only requires a small amount of manual intervention. For whatever reason PostgreSQL/GreenPlum doesn't seem to have a native way to split objects into separate files. This functionality is… Continue reading Parse pg_dump Functions and Views Into Their Own Files (GreenPlum / Postgres)

GreenPlum and DBFit

Just a quick little stub of a post to track progress using the DBFit Test Driven Database Development Tool with our GreenPlum installation. DBFit seemed like a great option since it will theoretically support both GreenPlum and SQL Server.   I ran through the getting started documentation on the DBFit site fairly quickly and had the initial… Continue reading GreenPlum and DBFit

Postgres/Greenplum Get The Last Two Values In A Delimited String

Yuck. This works and I'm recording it here in case I need it again but yuck. Here's the scenario: there is a column in a database with a delimited string. The number of delimited values can vary. SOmetimes there will be one delimited value, sometimes there will be twenty. For this specific case I needed… Continue reading Postgres/Greenplum Get The Last Two Values In A Delimited String

SQL Server T-SQL To GreenPlum PostgreSQL Syntax Cheat Sheet

Here's a quick translation from SQL Server's T-SQL to Pivotal's MPP GreenPlum PostgreSQL for some of the most commonly used syntax (i.e. here's the stuff I've tripped over while moving from SQL to GreenPlum/Postgres) .   SQL Server - TOP SELECT TOP 100 * FROM MyTable Corresponding PostgreSQL - LIMIT SELECT * FROM my_table LIMIT… Continue reading SQL Server T-SQL To GreenPlum PostgreSQL Syntax Cheat Sheet

Convert CamelCase to Underscores using SED and tr: GreenPlum MPP Refactor

All of our SQL Server databases use CamelCase as the naming convention. Unfortunately, GreenPlum MPP does not support CamelCase (without the use of quotes that is). Since I was transitioning a table from SQL Server to GreenPlum MPP and because I'm becoming a bash wizard (ha!) I wondered if there was a simple way to… Continue reading Convert CamelCase to Underscores using SED and tr: GreenPlum MPP Refactor

GreenPlum MPP Tool Review: Navicat For Postgres

I've spent a few hours so far working with Navicat for Postgres connected to our GreenPlum MPP (Massively Parallel Processing Database). (pgAdmin continues to iritate me but it'll be hard for a competitor to supplant the free incumbent). There is definitely some solid functionality here. Pros: Performance - The UI seems to be balzingly fast… Continue reading GreenPlum MPP Tool Review: Navicat For Postgres

SQL Server To GreenPlum MPP/Postgres Transition

I'm working with a team building a data warehouse using Pivotal's GreenPlum MPP (Massively Parallel Processing) database which is based on PostgreSQL. Although, all the data modeling basics transition easily from RDBMS to RDBMS there's a bit of a learning curve switching tools and syntax. Listed below are some of the items I've noted during the… Continue reading SQL Server To GreenPlum MPP/Postgres Transition

SSIS Export To UTF-8 or UNIX (or how to set your code page to output a flat file for unix)

As more and more companies are moving to heterogenous environments with both windows and unix systems the life of the Microsoft SQL Server developer is becoming much more interesting. For our recent data warehouse project I was asked to create a SQL Server 2012 Integration Services (SSIS) ETL package to output a text file for… Continue reading SSIS Export To UTF-8 or UNIX (or how to set your code page to output a flat file for unix)

SQL Query To Find Your Version of GreenPlum or Postgres

Ummm, what version of GreenPlum am I running? (Which is the same as asking: What version of Postgres am I running?) There are a few easy ways to figure this out but I'll stick with the SQL since that's the most universal: SELECT VERSION() That's it. I wish I could make it more complicated. 🙂