Hashing in pgsql

Events happening in the community are now at Drupal community events on www.drupal.org.

Posted by forngren on January 28, 2007 at 2:54pm

Hi,

Could someone please look at http://drupal.org/node/29706 and see if it's pgsql compliant? How do the CONCAT, MD5 and SUBSTRING functions behave on PostgreSQL?

Thanks,
Johan Forngren

Categories: PostgreSQL

Comments

CONCAT, MD5, SUBSTRING in PostgreSQL : not very much different

Posted by awele on February 26, 2007 at 3:59pm

awele's picture

Hello,

All string functions in PostgreSQL are documented in http://www.postgresql.org/docs/8.2/interactive/functions-string.html

There is no CONCAT in Postgresql. To concatenate strings you use || as in : SELECT 'start' || 'end'
It would be however simple to create a function (procedure) in postgresql called CONCAT that does the concatenation with || so that MySQL syntax works as is. However the SQL99 standard defines || as the concatenation operator, and based on my SQL book this is what Postgresql and Oracle do, where MS SQL uses + instead of || , and where MySQL uses CONCAT
md5() seems to be there, but I'm not sure beginning with which Postgresql version (7.3 does not seem to have it, 7.4 definitely has it) ; further than that there are contributed modules that add various crypto stuff in Postgresql, such as hash functions.
SUBSTRING is supported, in both ways: SUBSTRING('a string' FROM 1 FOR 2) and SUBSTRING('a string',1,2)

Patrick.