{"id":780,"date":"2014-11-24T07:00:06","date_gmt":"2014-11-24T14:00:06","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=780"},"modified":"2014-12-02T11:34:13","modified_gmt":"2014-12-02T18:34:13","slug":"sql-server-t-sql-to-greenplum-postgresql-syntax-cheat-sheet","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2014\/11\/sql-server-t-sql-to-greenplum-postgresql-syntax-cheat-sheet\/","title":{"rendered":"SQL Server T-SQL To GreenPlum PostgreSQL Syntax Cheat Sheet"},"content":{"rendered":"

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) .<\/p>\n

 <\/p>\n

SQL Server - TOP<\/h2>\n
SELECT TOP 100 *\r\nFROM MyTable<\/pre>\n
Corresponding PostgreSQL - LIMIT<\/h3>\n
SELECT *\r\nFROM my_table\r\nLIMIT 100<\/pre>\n
SQL Server - Temp Table<\/h2>\n
SELECT *\r\ninto #MyTempTable\r\nFROM MyTable<\/pre>\n
Corresponding PostgreSQL - Create Temp Table as<\/h3>\n
create temp table my_temp_table\u00a0as\r\nSELECT *\r\nFROM my_table\r\n\r\n\r\n<\/pre>\n
SQL Server -\u00a0Concatenate String +<\/h2>\n
SELECT colA+colB\r\nFROM MyTable<\/pre>\n
Corresponding PostgreSQL - Concatenate String || (vertical pipes)<\/h3>\n
SELECT col_a||col_b\r\nFROM my_table<\/pre>\n
SQL Server -\u00a0DATEDIFF<\/h2>\n
SELECT DATEDIFF(minute, colX, colY)\r\nFROM MyTable<\/pre>\n
Corresponding PostgreSQL - DATEDIFF \ud83d\ude41 FAIL<\/h3>\n
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:<\/p>\n
SELECT col_y-col_x\r\nFROM my_table<\/pre>\n
Otherwise you'll be stuck hacking together DATEPART functions. You can find a great cheat sheet on how to do this at sqllines.com<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,147,43,150,57],"tags":[10,162,151,181,9,173],"class_list":["post-780","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-greenplum","category-microsoft-sql-server","category-postgresql","category-t-sql","tag-ms-sql","tag-pivotal-greenplum","tag-postgres","tag-postgresql","tag-sql-server","tag-t-sql"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/780","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=780"}],"version-history":[{"count":6,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/780\/revisions"}],"predecessor-version":[{"id":787,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/780\/revisions\/787"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=780"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=780"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=780"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}