How to Concatenate Columns in PostgreSQL Using Select

Bilal Shahid Feb 02, 2024
  1. Use the || Operator to Concatenate Columns of VARCHAR Type in PostgreSQL
  2. Manipulate the SELECT Operation to Use || When Columns Are Not Strings in PostgreSQL
  3. Use Typecast (:: Notation) for Concatenation in PostgreSQL
  4. Use the CAST(expression as type) Expression in PostgreSQL
  5. Use the Simple CONCAT(col1, col2) Operation in PostgreSQL
  6. Use COALESCE(variable, to_type) for Concatenation if One Column Is NULL in PostgreSQL
How to Concatenate Columns in PostgreSQL Using Select

PostgreSQL is an object-relational database system which means that it can support much more complex data types than its contender, MySQL.

Today we will be learning about concatenating columns of a table using the SELECT operation.

Use the || Operator to Concatenate Columns of VARCHAR Type in PostgreSQL

Now, let’s assume we create a table with the configuration below.

create table tab(
	id int not null,
	u_name varchar(50) not null,
	PRIMARY KEY (u_name)
);

We then go ahead and insert a few values into it.

insert into tab values(45, 'Jonathon'), (56, 'Mark'), (68, 'Angel');

And finally, call the concatenation operator from the SELECT for the display.

select id || u_name from tab

So we will get an output as follows.

Output:

Use the Concatenation Operator to Concatenate Columns

How does the above query work? We use the || operator in the Select operation.

The || operator is defined in the PostgreSQL documentation to Concat Strings only.

Hence it is not difficult to concatenate columns of which one column must be a VARCHAR data type because VARCHAR corresponds to String.

Manipulate the SELECT Operation to Use || When Columns Are Not Strings in PostgreSQL

A very simple modification of the SELECT query given above can be used to concatenate any other data type columns together.

Let us suppose the following implementation now.

create table tab(
	id int not null,
	age int not null,
	PRIMARY KEY (id)
);
insert into tab values(45, 21), (56, 22), (68, 23)

Now we know that both columns are int; thus, if you try to do this.

select id || age from tab

It will return an error.

Output:

ERROR:  operator does not exist: integer || integer
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Thus here we have two options:

  1. Concatenate a null STRING between both columns and make it a valid operation.
  2. Add typecast to one of the column values.

To serve the former option, all you have to do is change the query to something like this.

select id ||''|| age from tab

This will return both numbers concatenated for all adjacent columns. It concatenates an empty string between both int columns; hence the operation becomes valid for this operator as at least one String is present.

Use Typecast (:: Notation) for Concatenation in PostgreSQL

To do this operation, we can use:

select id::text || age from tab

or

select id|| age::text from tab

This type casts one of the columns to a Text type, making the operation valid again as one String is now present.

In pre-historical PostgreSQL, this notation was commonly used to cast a valid type to any other.

Use the CAST(expression as type) Expression in PostgreSQL

The :: notation ended with a better counterpart, CAST(). It is effective and easier to understand for a beginner who does not know what :: does by just looking at the code.

select CAST(id as varchar(50)) || age from tab

Use the Simple CONCAT(col1, col2) Operation in PostgreSQL

If you don’t want to do any casting, keep your code as simple as possible. You can switch to the CONCAT() function that joins and accepts any data types given.

select concat(id, age) from tab

Use COALESCE(variable, to_type) for Concatenation if One Column Is NULL in PostgreSQL

We use COALESCE(variable, to_type) if a variable is null, 0, or something else so that it remains valid and does not display NULL.

In our case, convert our NULL column values to an effective String type so that it concatenates properly.

Let’s use the following configuration.

create table tab(
	id int not null,
	age int,
	PRIMARY KEY (id)
);

insert into tab values(45, 21), (56, NULL), (68, 23);

We have inserted a NULL for the second set of values in the table.

If we now perform this.

select id::text || age from tab;

It will return:

Output:

Use COALESCE

So we can call COALESCE() here.

select coalesce(id, 0)::text || coalesce(age, 0) from tab;

And this tends to return the correct output with the NULL value now substituted to 0.

Author: Bilal Shahid
Bilal Shahid avatar Bilal Shahid avatar

Hello, I am Bilal, a research enthusiast who tends to break and make code from scratch. I dwell deep into the latest issues faced by the developer community and provide answers and different solutions. Apart from that, I am just another normal developer with a laptop, a mug of coffee, some biscuits and a thick spectacle!

GitHub

Related Article - PostgreSQL Column