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
