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 100

SQL Server - Temp Table

SELECT *
into #MyTempTable
FROM MyTable

Corresponding PostgreSQL - Create Temp Table as

create temp table my_temp_table as
SELECT *
FROM my_table


SQL Server - Concatenate String +

SELECT colA+colB
FROM MyTable

Corresponding PostgreSQL - Concatenate String || (vertical pipes)

SELECT col_a||col_b
FROM my_table

SQL Server - DATEDIFF

SELECT DATEDIFF(minute, colX, colY)
FROM MyTable

Corresponding PostgreSQL - DATEDIFF 🙁 FAIL

Turns out there isn't really a comparable function on the postgres side. I know. I couldn't believe it either. One easy option that will return an interval (e.g. ""122 days 02:17:18.839115") may work for your case:

SELECT col_y-col_x
FROM my_table

Otherwise you'll be stuck hacking together DATEPART functions. You can find a great cheat sheet on how to do this at sqllines.com

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *