Thread: || operator

Hello,

The behavior of || operator is different in Oracle and PostgreSQL when the
arguments are CHAR(n) data type.
Example:
create table hoge1(col1 char(10), col2 char(10));
insert into hoge1 values('abc', 'def');
select col1 || col2 from hoge1;
abcdef  (PostgreSQL's result)
abc       def           (Oracle's result)
I think the behavior of CHAR data type is different in Oracle and
PostgreSQL.
CHAR type of Oracle is in the byte unit whereas the CHAR type of PostgreSQL
is in character unit.
Oracle : CHAR(3) => 3 byte
PostgreSQL : CHAR(3) => 3 characters
When CHAR values are stored in Oracle, they are right-padded with spaces to
the specified length.
If we use concat() then the result is same as Oracle || operator so I think
PostgreSQL also store the CHAR value like Oracle but || operator gives the
different result.
Example:
postgres=# select concat(col1,col2) from hoge1;
        concat
----------------------
 abc       def
(1 rows)

postgres=# select col1 || col2 from hoge1;
 ?column?
----------
 abcdef
(1 rows)

Any idea how to get result same as oracle if CHAR(n) data type is used?



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/operator-tp5817541.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 09/03/2014 06:25 AM, Szymon Guz wrote:
>

>
>
>
> Hi Pavel,
> I think we should have this in core, as this definitely is a bug.

It is documented behavior:

http://www.postgresql.org/docs/9.3/interactive/datatype-character.html

"Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However, the
padding spaces are treated as semantically insignificant. Trailing
spaces are disregarded when comparing two values of type character, and
they will be removed when converting a character value to one of the
other string types. Note that trailing spaces are semantically
significant in character varying and text values, and when using pattern
matching, e.g. LIKE, regular expressions."


>
> Szymon


--
Adrian Klaver
adrian.klaver@aklaver.com

Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2014-09-03 15:25 GMT+02:00 Szymon Guz <mabewlun@gmail.com>:

>> I think we should have this in core, as this definitely is a bug.
>
> hard to say - anything about CHAR(N) is strange,

On a quick scan of the standard, it looks like our current behavior
is non-conforming.

> and this change can break existing applications :(

That is true, but since the only point of supporting CHAR(n) is to
satisfy requirements of the standard, it might be something we
should do, if technically feasible.

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

Hello Pavel,

Thank you for reply.
>postgres=# select 'abc   '::char(7) || 'dbe   '::char(6);
>    ?column?
>----------------
> *abc    abc*
>(1 row)
but it gives the result "abc    abc". It should be "abc    dbe".





-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817674.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Yes function should be
create or replace function concat_character(character, character) returns
text as $$ select concat($1,$2)$$ language sql;
Now its working.

Thank you.



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817686.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi,

The || operator with arguments (character,character) works fine and even ||
operator(character,varchar) also works fine.
but || operator is not working as expected with arguments character data
type and any other data type like integer,smallint,date,text.
Example:
postgres=# select 'ab'::char(10) || 4::int;
 ?column?
----------
 ab4
(1 row)
postgres=# select 'ab'::char(10) || 'a'::text;
  ?column?
-------------
 aba
(1 row)

so I have created || operator with argument character and anyelement.
Example:
create or replace function concat_character(character, anyelement) returns
text as $$ select concat($1,$2)$$ language sql;
create operator || (procedure = concat_character, leftarg = character,
rightarg = anyelement);
it works fine with argument of type int,smallint,bigint,date etc.
but its not working with text and varchar data type.
Example:
postgres=# select 'ab'::char(10) || 4::int;
  ?column?
-------------
 ab        4
(1 row)

postgres=# select 'ab'::char(10) || 'b'::text;
 ?column?
----------
 abb
(1 row)

postgres=# select 'ab'::char(10) || 'b'::varchar(5);
ERROR:  operator is not unique: character || character varying
LINE 1: select 'ab'::char(10) || 'b'::varchar(5);
                              ^
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
Thought?



-----
Regards,
Vinayak,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817712.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Wed, 2014-09-03 at 21:27 -0700, Vinayak wrote:
> Hello Pavel,
>
> Thank you for reply.
> >postgres=# select 'abc   '::char(7) || 'dbe   '::char(6);
> >    ?column?
> >----------------
> > *abc    abc*
> >(1 row)
> but it gives the result "abc    abc". It should be "abc    dbe".
>
>
I believe there was a typo in the function, try this one
:
postgres=# create or replace function concat_character(character,
character) returns text as $$ select concat($1,$2)$$ language sql;
>
>
>
> -----
> Regards,
> Vinayak,
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817674.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


Important Notice: The contents of this email are intended solely for the named addressee and are confidential; any
unauthoriseduse, reproduction or storage of the contents is expressly prohibited. If you have received this email in
error,please delete it and any attachments immediately and advise the sender by return email or telephone.
 

Deakin University does not warrant that this email and any attachments are error or virus free.