|| 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@gmail.com>:

On 3 September 2014 15:20, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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

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

On 3 September 2014 15:20, Pavel Stehule <pavel.stehule@gmail.com> wrote:

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-09-03 16:01 GMT+02:00 Kevin Grittner <kgrittn@ymail.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.

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-09-04 6:27 GMT+02:00 Vinayak <vinpokale@gmail.com>:

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".

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;

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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-09-04 11:13 GMT+02:00 Vinayak <vinpokale@gmail.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)

text is more general -- it it does cast to text - there is not || operator
for leftarg character and righarg text

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?

Not too much - it is limit of Postgres type system :(

Pavel