Re: || operator - Mailing list pgsql-general

From Pavel Stehule
Subject Re: || operator
Date
Msg-id CAFj8pRBtogkwqvasEjhrN1UwcJQpqcSZ9k9tbkdV9TEuokz=RQ@mail.gmail.com
Whole thread Raw
In response to Re: || operator  (Vinayak <vinpokale@gmail.com>)
List 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
 

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

pgsql-general by date:

Previous
From: Vinayak
Date:
Subject: Re: || operator
Next
From: Alanoly Andrews
Date:
Subject: Re: Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.