How to concatenate columns in a Postgres SELECT?

Question

I have two string columns a and b in a table foo.

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

Update from comments: both columns are type character(2).

Answer 1

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;

Answer 2

With string type columns like character(2) (as you mentioned later), the displayed concatenation just works because, quoting the manual:

[...] the string concatenation operator (||) accepts non-string input, so long as at least one input is of a string type, as shown in Table 9.8. For other cases, insert an explicit coercion to text [...]

Bold emphasis mine. The 2nd example (select a||', '||b from foo) works for any data types since the untyped string literal ', ' defaults to type text making the whole expression valid in any case.

For non-string data types, you can "fix" the 1st statement by casting at least one argument to text. (Any type can be cast to text):

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

Judging from your own answer, "does not work" was supposed to mean "returns NULL". The result of anything concatenated to NULL is NULL. If NULL values can be involved and the result shall not be NULL, use concat_ws() to concatenate any number of values (Postgres 9.1 or later):

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

Separators are only added between non-null values, i.e. only where necessary.

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

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

No need for type casts here since both functions take "any" input and work with text representations.

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

• Combine two columns and add into one new column

Regarding update in the comment

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

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

• Any downsides of using data type "text" for storing strings?
• Best way to check for "empty or null value"

Answer 3

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

Answer 4

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

Answer 5

As i was also stuck in this, think i should share the solution that worked best for me. I also think that this is much simpler.

If you use Capitalized table name.

SELECT CONCAT("firstName", ' ', "lastName") FROM "User"

If you use lowercase table name

SELECT CONCAT(firstName, ' ', lastName) FROM user

That's it!. As PGSQL counts Double Quote for column declaration and Single Quote for string, this works like a charm.

Answer 6

PHP's Laravel framework, I am using search first_name, last_name Fields consider like Full Name Search

Using || symbol Or concat_ws(), concat() methods

$names = str_replace(" ", "", $searchKey);                               
$customers = Customer::where('organization_id',$this->user->organization_id)
             ->where(function ($q) use ($searchKey, $names) {
                 $q->orWhere('phone_number', 'ilike', "%{$searchKey}%"); 
                 $q->orWhere('email', 'ilike', "%{$searchKey}%");
                 $q->orWhereRaw('(first_name || last_name) LIKE ? ', '%' . $names. '%');
    })->orderBy('created_at','desc')->paginate(20);

This worked charm!!!

Answer 7

For example if there is employee table which consists of columns as:

employee_number,f_name,l_name,email_id,phone_number 

if we want to concatenate f_name + l_name as name.

SELECT employee_number,f_name ::TEXT ||','|| l_name::TEXT  AS "NAME",email_id,phone_number,designation FROM EMPLOYEE;

Answer 8

Try this

select concat_ws(' ', FirstName, LastName) AS Name from person;