JIYIK CN >

MySQLPostgreSQL is an object-relational database system, which means it can support more complex data types than its competitors .

Today we will learn how to use SELECTthe operator to join the columns of a table.

Using operators to ||join columns of type in PostgreSQLVARCHAR

Now, suppose we create a table with the following configuration.

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

Then we go ahead and insert some values ​​into it.

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

Finally, SELECTthe concatenation operator in is called for display.

select id || u_name from tab

So we will get the following output.

Output:

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

||The operator is defined in the PostgreSQL documentation only as a concatenation string.

Therefore, it is not difficult to join columns where one of the columns must be VARCHARof data type, because VARCHARcorresponds to String.

Manipulate SELECToperations to use ||when columns in PostgreSQL are not strings

SELECTA very simple modification of the query given above can be used to join together columns of any other data type.

Now let us assume the following implementation.

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; therefore, 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.

So, we have two options here:

1. Connect a null between two columns STRINGand make it a valid operation.
2. Add a type conversion to one of the column values.

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

select id ||''|| age from tab

This returns two numbers concatenating all adjacent columns. It intconcatenates an empty string between the two columns; therefore, the operation is valid for this operator because there is at least one String.

Connecting in PostgreSQL using Typecast( ::notation)

To do this we can use:

select id::text || age from tab

or

select id|| age::text from tab

This type converts one of the columns to Texttype, making the operation valid again because there is now a String.

In prehistoric PostgreSQL, this notation was commonly used to convert valid types to any other type.

CAST(expression as type)Using expressions in PostgreSQL

::CAST()The symbol ends with a better counterpart . ::It is efficient and easier to understand for beginners who just look at the code and don't know what it does.

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

CONCAT(col1, col2)Using simple operations in PostgreSQL

If you don't want to do any conversion, make your code as simple as possible. You can switch to concatenation and CONCAT()functions that accept any given data type.

select concat(id, age) from tab

If a column in PostgreSQL NULLisCOALESCE(variable, to_type)

If the variable is null, 0, or some other value, we use COALESCE(variable, to_type)so that it remains valid and is not displayed NULL.

In our case, NULLconvert our column values ​​to a valid Stringtype so they can be connected correctly.

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 inserted one for the second set of values ​​in the table NULL.

If we do this now.

select id::text || age from tab;

It will return:

Output:

So we can call here COALESCE().

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

This tends to return the correct output, with NULLthe values ​​now replaced by 0.