Обсуждение: Different behaviour of concate() and concate operator ||

Hi ALL,

I need little help to understand, text concatenation.

concat function and operator || have different behaviour, if any input is NULL.

test=# select  'abc' || NULL;
 ?column?
----------

(1 row)

test=# select concat('abc', NULL);
 concat
--------
 abc
(1 row)


It has simple reason, concat operator || use textcat() function which is STRICT.

my question is 

1. Do we required textcat to be STRICT, why?
2. textcat() is used for concat operator ||, can't it possible using concat() function?


Thanks in advance.

Regards,
Amul Sul  

amul sul <sul_amul@yahoo.co.in> writes:
> concat function and operator || have different behaviour, if any input is NULL.

The behavior of || is specified by the SQL standard, and it says (SQL99
6.27 <string value expression> general rule 2a):
           a) If either S1 or S2 is the null value, then the result of the             <concatenation> is the null
value.

So no, we're not changing it to be more like concat().
        regards, tom lane

The missing bit of context is that concat() is there because early on
in Postgres's life there was an effort to have a full suite of Oracle
compatibility functions. If someone suggested it now they would be
pushed towards making it an extension or pointed at EDB. But things
like concat are the remnants of that.

Greg Stark <stark@mit.edu> writes:
> The missing bit of context is that concat() is there because early on
> in Postgres's life there was an effort to have a full suite of Oracle
> compatibility functions. If someone suggested it now they would be
> pushed towards making it an extension or pointed at EDB. But things
> like concat are the remnants of that.

Well, that's historical revisionism, because concat() was added in 9.1.
But if it was defined this way for Oracle compatibility, that makes
sense, because Oracle doesn't distinguish NULL from empty strings.
So they pretty much would have to make concat() treat NULL as empty.
        regards, tom lane

On Monday, 28 April 2014 10:56 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Greg Stark <stark@mit.edu> writes:

>So they pretty much would have to make concat() treat NULL as empty.


could it be problematic if I update pg_operator catalogue entry for ||, 
call to concat() function instead of texcat(), in my production environment ? 

for eg.
 oprname | oprleft | oprright | oprresult | oprcode
---------+---------+----------+-----------+---------
 ||      |    25   |   25     |     25    | concat
(1 row)


result would be 

test=# select  'abc' || NULL;                                                                            ;
 ?column?
----------
 abc
(1 row)

Thank for your help.

Regards,
Amul Sul