Postgres query to combine two column values

Question

Lets say I have a postgres table with the following data:

hostname | ip
---------+--------
a1       | 198.162.4.2
a1       | 198.162.7.5
a2       | 10.43.243.4
a3       | 10.3.1.1

I want to copy the values from table tmp to table abc in psql. I want to have an sql insert command to display the hostname_ip in hostname column like below:

hostname | ip
---------+--------
a1_198.162.4.2 | 198.162.4.2    
a1_198.162.7.5 | 198.162.7.5
a2_10.43.243.4 | 10.43.243.4
a3_10.3.1.1    | 10.3.1.1

trempo · Accepted Answer · 2020-11-30 04:12:43Z

1

You can do like below

insert into abc(hostname,ip)
select (hostname || '_' || ip) as hostname,ip from tmp;

answered Nov 30, 2020 at 4:12

trempo

586 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

trempo Over a year ago

Do you mean update same table? Yes it is possible

Tim Biegeleisen · Accepted Answer · 2020-11-30 04:08:13Z

1

I suggest using CONCAT_WS here:

SELECT CONCAT_WS('_', hostname, ip) AS hostname, ip
FROM yourTable;

answered Nov 30, 2020 at 4:08

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Comments

Sridhar · Accepted Answer · 2020-11-30 05:20:14Z

0

SELECT CONCAT(hostname,'_',ip) as hostname,ip FROM table;

answered Nov 30, 2020 at 5:20

Sridhar

111 bronze badge

Collectives™ on Stack Overflow

Postgres query to combine two column values

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related