Re: || operator

Hi

you can define || operator for char(N) type

postgres=# select oprname, oprleft::regtype, oprright::regtype from
pg_operator where oprname = '||'
;
oprname | oprleft | oprright
---------+-------------+-------------
|| | bytea | bytea
|| | text | text
|| | text | anynonarray
|| | bit varying | bit varying
|| | anyarray | anyarray
|| | anyarray | anyelement
|| | anyelement | anyarray
|| | anynonarray | text
|| | tsvector | tsvector
|| | tsquery | tsquery
(10 rows)

it is defined only for text, and value char(n) is reduced when it is
converted probably

postgres=# create or replace function concat_character(character,
character) returns text as $$ select concat($1,$1)$$ language sql;
CREATE FUNCTION

postgres=# create operator || (procedure = concat_character, leftarg =
character, rightarg = character);
CREATE OPERATOR
postgres=# select 'abc '::char(7) || 'dbe '::char(6);
?column?
----------------
abc abc
(1 row)

concat is variadic "any" function, so implicit casting character(n) -> text
is not used there

Pavel

2014-09-03 15:04 GMT+02:00 Vinayak <vinpokale(at)gmail(dot)com>:

> 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>