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

User · Answer

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- gt user- gt organization id               - gt where function   q  use   searchKey   names                      q- gt orWhere  phone number    ilike       searchKey                         q- gt orWhere  email    ilike       searchKey                        q- gt orWhereRaw   first name    last name  LIKE             names              - gt orderBy  created at   desc  - gt paginate 20     This worked charm

User · Answer

Try this  select textcat textcat FirstName      LastName  AS Name from person

User · Answer

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

User · Answer

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    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  quot text quot  for storing strings  Best way to check for  quot empty or null value quot

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to concatenate columns in a Postgres SELECT?

Examples related to sql

Examples related to postgresql

Examples related to types

Examples related to concatenation

Examples related to coalesce