Re: || operator - Mailing list pgsql-general
| From | Pavel Stehule |
|---|---|
| Subject | Re: || operator |
| Date | |
| Msg-id | CAFj8pRBmFxH-J044oCmyVDzHH0QobDn+e-=uUiA1PVLkfW9P8w@mail.gmail.com Whole thread Raw |
| In response to | || operator (Vinayak <vinpokale@gmail.com>) |
| Responses |
Re: || operator
Re: || operator |
| List | pgsql-general |
Hi
you can define || operator for char(N) typepostgres=# 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)
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
2014-09-03 15:04 GMT+02:00 Vinayak <vinpokale@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: