Discussion:
|| operator
Vinayak
2014-09-03 13:04:46 UTC
Permalink
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-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Pavel Stehule
2014-09-03 13:20:05 UTC
Permalink
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
Post by Vinayak
Hello,
The behavior of || operator is different in Oracle and PostgreSQL when the
arguments are CHAR(n) data type.
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.
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,
--
http://postgresql.1045698.n5.nabble.com/operator-tp5817541.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general
Szymon Guz
2014-09-03 13:25:22 UTC
Permalink
Post by Pavel Stehule
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
Hi Pavel,
I think we should have this in core, as this definitely is a bug.

Szymon
Pavel Stehule
2014-09-03 13:30:00 UTC
Permalink
Post by Szymon Guz
Post by Pavel Stehule
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
Hi Pavel,
I think we should have this in core, as this definitely is a bug.
hard to say - anything about CHAR(N) is strange, and this change can break
existing applications :(

I remember one previous CHAR(N) issue, and probably it was not fixed too.

I have not any opinion, just I don't know

Pavel
Post by Szymon Guz
Szymon
Kevin Grittner
2014-09-03 14:01:14 UTC
Permalink
Post by Pavel Stehule
Post by Szymon Guz
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.
Post by Pavel Stehule
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Pavel Stehule
2014-09-03 14:37:45 UTC
Permalink
Post by Kevin Grittner
Post by Pavel Stehule
Post by Szymon Guz
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.
Post by Pavel Stehule
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.
It is true, but nobody reported bug yet, so who know, how this feature is
used.

Probably it needs a deeper analyze of difference between Pg CHAR(n) and
standard CHAR(n)

Pavel
Post by Kevin Grittner
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Adrian Klaver
2014-09-03 13:57:07 UTC
Permalink
Post by Szymon Guz
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."
Post by Szymon Guz
Szymon
--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Vinayak
2014-09-04 04:27:23 UTC
Permalink
Hello Pavel,

Thank you for reply.
Post by Pavel Stehule
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.
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Pavel Stehule
2014-09-04 05:35:51 UTC
Permalink
Post by Vinayak
Hello Pavel,
Thank you for reply.
Post by Pavel Stehule
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".
yes

create or replace function concat_character(character, character) returns
text as $$ select concat($1,$1)$$ language sql;

is wrong

should be

create or replace function concat_character(character, character) returns
text as $$ select concat($1,$2)$$ language sql;
Post by Vinayak
-----
Regards,
Vinayak,
--
http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817674.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general
Vinayak
2014-09-04 05:48:52 UTC
Permalink
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.
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Brett Mc Bride
2014-09-04 04:43:12 UTC
Permalink
Post by Vinayak
Hello Pavel,
Thank you for reply.
Post by Pavel Stehule
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;
Post by Vinayak
-----
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 unauthorised use, 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.
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresq
Loading...