current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite

I have string columns, i.e. a and b.

So select a,b from foo returns values a and b. However, concatenation of a and b does not work. I tried :

select a || b from foo

and

select  a||', '||b from foo

OP's error message (from comments):

No operator matches the given name and argument type(s). You might need to add explicit type casts.

He also stated that both fields are character(2).

share|improve this question
    
is a a string ? –  Raphaël Althaus Nov 13 '13 at 0:26
    
... or another text type? –  PM 77-1 Nov 13 '13 at 0:31
    
@acfrancis Since OP says concatenate I doubt that he's dealing with numeric types, though PostgreSQL would take care of some of them as well. See here:postgresql.org/docs/9.1/static/functions-string.html –  PM 77-1 Nov 13 '13 at 0:34
1  
@PM77-1: Trying to fix the question for anarinsky is driven by good intention, no doubt. But it doesn't add up. The comment mentions the operator +, but the question displays the correct concatenation operator ||. The OP must post the exact data types of the involved columns, the exact statement and the exact error message. And possibly if NULL values are involved. The problem itself is trivial. –  Erwin Brandstetter Nov 13 '13 at 0:53
1  
@ErwinBrandstetter + was suggested by another poster in now deleted comment. Otherwise I fully agree. –  PM 77-1 Nov 13 '13 at 1:00

4 Answers 4

up vote 12 down vote

Whatever the actual types behind the curtain are, you can "fix" your statement by casting to text (any type can be cast to text):

SELECT a::text || ', ' || b::text AS ab FROM foo;

If NULL values can be involved, you may want to use the concat_ws() function (Postgres 9.1 or later):

SELECT concat_ws(', ', a::text, b::text) AS ab FROM foo;

Or just concat() if you don't need to add separators:

SELECT concat(a::text, b::text) AS ab FROM foo;

More details (and why COALESCE is a poor substitute) in this related answer:

Regarding update in the comment:

+ is not a valid operator for string concatenation in Postgres (or the SQL standard). It's a a private idea of Microsoft to add this to their products.

There is hardly any good reason to use character(n). Use text or varchar. Details:

Either way, the statement would just work in modern day Postgres with any character type. Try:

    SELECT 'a '::character(2) || 'b '::character(2)

You must be involving other data types.

share|improve this answer
    
Thank you. The 1st version does not work with null and the 2nd gave me the error for concat_ws: No function matches the given name and argument types. You might need to add explicit type casts. –  anarinsky Nov 13 '13 at 16:10
1  
You did see Postgres 9.1 or later, right? You should have provided your version of Postgres to begin with, in the question. Please update your question with all the requested information, before you come back for anything else. –  Erwin Brandstetter Nov 13 '13 at 16:27
    
Thank you, the solution I found works for any Postgres version –  anarinsky Nov 19 '14 at 16:35
    
SELECT concat(a, b) FROM foo; works for me in Postgres 9.3 when a and b are VARCHARs. –  elimisteve Feb 3 at 1:27
up vote 1 down vote

The problem was in nulls in the values; then the concatenation does not work with nulls. The solution is as follows:

select coalesce(a, '')||coalesce(b, '') from foo;

share|improve this answer
up vote 0 down vote

it is better to use CONCAT function in PostgreSQL for concatenation

eg : select CONCAT(first_name,last_name) from person where pid = 136

if you are using column_a || ' ' || column_b for concatenation for 2 column , if any of the value in column_a or column_b is null query will return null value. which may not be preferred in all cases.. so instead of this

||

use

CONCAT

it will return relevant value if either of them have value

share|improve this answer
up vote 0 down vote

CONCAT functions sometimes not work with older postgreSQL version

see what I used to solve problem without using CONCAT

 u.first_name || ' ' || u.last_name as user,

Or also you can use 

 "first_name" || ' ' || "last_name" as user,

in second case I used double quotes for first_name and last_name

Hope this will be useful, thanks

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.