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
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)
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 :(