Quick Links

Re: Amazon Redshift

Lists:	pgsql-hackers

From:	"philip taylor" <philtay51(at)mail(dot)com>
To:	pgsql-hackers(at)postgresql(dot)org
Subject:	Amazon Redshift
Date:	2014-11-05 21:31:23
Message-ID:	trinity-482d08a0-5159-4ff8-9e81-ca489a66ccaf-1415223083403@3capp-mailcom-lxa15
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Hackers,<br/>
 <br/>
do you think we should implement some of the functions offered by Amazon Redshift?</div>

<div>http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html</div>

<div>Amazon Redshift is completely based on PostgreSQL, but they have implemented some features not currently available in PostgreSQL.<br/>
Given the fact that it's a successful customer-driven commercial project, one can easily guess that the functions they implemented are useful and requested by many users.</div>

Attachment	Content-Type	Size
unknown_filename	text/html	708 bytes

From:	Kevin Grittner <kgrittn(at)ymail(dot)com>
To:	philip taylor <philtay51(at)mail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Amazon Redshift
Date:	2014-11-05 21:42:15
Message-ID:	1415223735.17172.YahooMailNeo@web122301.mail.ne1.yahoo.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

philip taylor <philtay51(at)mail(dot)com> wrote:

> do you think we should implement some of the functions offered by
> Amazon Redshift?
>
> http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html

Well, obviously we already have many of the functions linked from
that page. I think that if you want to go through them and pick
out a few that you think are particularly promising it could start
a useful discussion. Pointing at a page that includes such things
as CURRENT_DATE, the avg() aggregate, and the lead() window
function is just going to waste the time of everyone who follows
the link. It's not like all of them would be committed in the same
patch anyway.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

From:	Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
To:	Kevin Grittner <kgrittn(at)ymail(dot)com>, philip taylor <philtay51(at)mail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Amazon Redshift
Date:	2014-11-05 22:05:34
Message-ID:	545A9F2E.90106@BlueTreble.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

On 11/5/14, 3:42 PM, Kevin Grittner wrote:
> philip taylor <philtay51(at)mail(dot)com> wrote:
>
>> do you think we should implement some of the functions offered by
>> Amazon Redshift?
>>
>> http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html
>
> Well, obviously we already have many of the functions linked from
> that page. I think that if you want to go through them and pick
> out a few that you think are particularly promising it could start
> a useful discussion. Pointing at a page that includes such things
> as CURRENT_DATE, the avg() aggregate, and the lead() window
> function is just going to waste the time of everyone who follows
> the link. It's not like all of them would be committed in the same
> patch anyway.

At the risk stating the obvious, before we look at implementing them we should see if Amazon would donate that effort.

But as Kevin said, first step is discussion on what we don't have and whether we want it.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

From:	"philip taylor" <philtay51(at)mail(dot)com>
To:	"Jim Nasby" <Jim(dot)Nasby(at)BlueTreble(dot)com>
Cc:	"Kevin Grittner" <kgrittn(at)ymail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Amazon Redshift
Date:	2014-11-05 22:36:03
Message-ID:	trinity-881d40f5-012b-4f56-a26a-9b5cec5e91c1-1415226962582@3capp-mailcom-lxa12
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

>>> do you think we should implement some of the functions offered by
>>> Amazon Redshift?
>>>
>>> http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html
>>
>> Well, obviously we already have many of the functions linked from
>> that page. I think that if you want to go through them and pick
>> out a few that you think are particularly promising it could start
>> a useful discussion. Pointing at a page that includes such things
>> as CURRENT_DATE, the avg() aggregate, and the lead() window
>> function is just going to waste the time of everyone who follows
>> the link. It's not like all of them would be committed in the same
>> patch anyway.
>
> At the risk stating the obvious, before we look at implementing them we should see if Amazon would donate that effort.
>
> But as Kevin said, first step is discussion on what we don't have and whether we want it.

Ok, this is a summary of what they have that we don't (of course, I could have missed something):

Date Functions

http://docs.aws.amazon.com/redshift/latest/dg/r_ADD_MONTHS.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html
http://docs.aws.amazon.com/redshift/latest/dg/r_MONTHS_BETWEEN_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html

String Functions

http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_COUNT.html
http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_INSTR.html
http://docs.aws.amazon.com/redshift/latest/dg/r_STRTOL.html
http://docs.aws.amazon.com/redshift/latest/dg/crc32-function.html
http://docs.aws.amazon.com/redshift/latest/dg/FUNC_SHA1.html

JSON Functions

http://docs.aws.amazon.com/redshift/latest/dg/JSON_ARRAY_LENGTH.html
http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_ARRAY_ELEMENT_TEXT.html
http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

Window functions

http://docs.aws.amazon.com/redshift/latest/dg/r_WF_RATIO_TO_REPORT.html
http://docs.aws.amazon.com/redshift/latest/dg/r_WF_MEDIAN.html

From:	Josh Berkus <josh(at)agliodbs(dot)com>
To:	pgsql-hackers(at)postgresql(dot)org
Subject:	Re: Amazon Redshift
Date:	2014-11-05 23:15:26
Message-ID:	545AAF8E.3020808@agliodbs.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

On 11/05/2014 02:36 PM, philip taylor wrote:

> Ok, this is a summary of what they have that we don't (of course, I could have missed something):
>

I can't see any functions on that list I'd want.

For example, DATEADD is there just to be compatible with MSSQL. It's
useless to us.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

From:	Jaime Casanova <jaime(at)2ndquadrant(dot)com>
To:	philip taylor <philtay51(at)mail(dot)com>
Cc:	Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Amazon Redshift
Date:	2014-11-06 05:38:05
Message-ID:	CAJKUy5ituKXGkwJk6Z39pOkA3zqe39VaxLmrkbD3apcM0mGPnA@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

On Wed, Nov 5, 2014 at 5:36 PM, philip taylor <philtay51(at)mail(dot)com> wrote:
>>>> do you think we should implement some of the functions offered by
>>>> Amazon Redshift?
>>>>
>>>> http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html
>>>
>
> JSON Functions
>
> http://docs.aws.amazon.com/redshift/latest/dg/JSON_ARRAY_LENGTH.html
> http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html
>

We actually have the two above:
http://www.postgresql.org/docs/current/static/functions-json.html
Actually, if you look at the examples you will see they only copy ours
and changed the values a little.

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

From:	Andrea Suisani <sickpig(at)opinioni(dot)net>
To:	philip taylor <philtay51(at)mail(dot)com>, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
Cc:	Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject:	Re: Amazon Redshift
Date:	2014-11-06 10:02:59
Message-ID:	545B4753.5040104@opinioni.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

On 11/05/2014 11:36 PM, philip taylor wrote:
> String Functions
>
> http://docs.aws.amazon.com/redshift/latest/dg/FUNC_SHA1.html

http://www.postgresql.org/docs/9.3/static/pgcrypto.html

(not really a string function imho)

From:	Bernd Helmle <mailings(at)oopsware(dot)de>
To:	philip taylor <philtay51(at)mail(dot)com>, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com>
Cc:	Kevin Grittner <kgrittn(at)ymail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject:	Re: Amazon Redshift
Date:	2014-11-06 15:26:28
Message-ID:	FB42E10D1AECB7CC612DD76C@eje.credativ.lan
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-hackers

--On 5. November 2014 23:36:03 +0100 philip taylor <philtay51(at)mail(dot)com>
wrote:

> Date Functions
>
> http://docs.aws.amazon.com/redshift/latest/dg/r_ADD_MONTHS.html
> http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html
> http://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html
> http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html
> http://docs.aws.amazon.com/redshift/latest/dg/r_MONTHS_BETWEEN_function.h
> tml http://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html

fyi, except DATE_DIFF() all of these functions can be installed by using
the orafce extension i believe.

--
Thanks

Bernd