August 24th, 2010 by depesz | Tags: , , , , , , | 10 comments »
Did it help? If yes - maybe you can help me?

On 24th of August, Takahiro Itagaki committed patch:

Log Message:
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
Pavel Stehule, reviewed by me.

What are these functions?

concat() is just functional counterpart to || operator, with the ability to handle many strings at the same time:

$ select concat( 'post', 'gres', 'ql' );
(1 row)

Pretty simple, but useful if you're doing a lot of concatenations.

concat_ws() is similar, but it treats its first argument as separator, allowing:

$ select concat_ws( ' ', 'hubert', 'depesz', 'lubaczewski' );
 hubert depesz lubaczewski
(1 row)

left() and right() have the same meaning, but are used on different parts of string:

$ select left( 'postgresql', 4 );
(1 row)

Generally it's like substring, but we have one nice feature:

$ select left( 'postgresql', -2 );
(1 row)

That is – if the length parameter is negative it will chop abs(length) characters from “the other side".

Right examples:

$ select right( 'postgresql', 3 );
(1 row)
$ select right( 'postgresql', -4 );
(1 row)

All of these were relatively simply available previously. But the last function is (in my not so humble opinion) the most important. reverse().

It's functionality is trivial:

$ select reverse( 'postgresql' );
(1 row)

But the fact that we have it now in core PostgreSQL, means we can have fast searches using “LIKE ‘%something'" – technique for this was described earlier, but we were missing fast reverse function. Now it's in core. Great.

  1. 10 comments

  2. # Thom Brown
    Aug 24, 2010

    I think that having these functions will make migration from MySQL that much less painless. 🙂 Although I believe MySQL’s version of LEFT() and RIGHT() don’t support negative lengths.

  3. Is that what these are for? MySQL migration? just wondering on the… purpose behind them…

  4. # mrova
    Aug 24, 2010

    It makes butterflies in our poor programmers life 🙂

  5. # Wen
    Aug 24, 2010

    Can “concat_ws” be used as an aggregate function?

  6. Aug 24, 2010

    i doubt, but there is no point:

  7. # Wen
    Aug 24, 2010

    Thanks. Missed that post, can’t wait for 9.0 to come out.

  8. # mpawlikowski
    Aug 25, 2010

    small formatting problem between section about concat_ws and left function 😉 Perhaps unclosed

  9. Aug 25, 2010

    thanks, fixed.

  10. # JSPATZ
    Aug 26, 2010

    What about concatenation with NULL?
    Normally ‘TEXT’ || NULL = NULL, what about the new functions, is that equal?

    If you have many strings it is very unhandy to write coalesce( Field1, ”) || coalesce( Field2, ”) || coalesce( Field1, ”) …and so on.

    In my projects I already made a concat-Function which is equal to the concat above and an concatN Function which does an coalesce( Param, ”) on every parameter.


  11. Aug 26, 2010


    # select concat('a', NULL, 'b');
    (1 row)
    # select concat_ws('-=>', 'a', NULL, 'b');
    (1 row)

Sorry, comments for this post are disabled.