Concatenate three columns data into one column in Postgres

Question

Can anyone tell me which command is used for concatenate three columns data into one column in PostgreSQL database?

e.g.

If the columns are

begin | Month | Year
   12 |     1 | 1988
   13 |     3 | 1900
   14 |     4 | 2000
   15 |     5 | 2012

result like

Begin
12-1-1988 
13-3-1900
14-4-2000
15-5-2012

Have you tried consulting the manual for this very basic question? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 13, 2012 at 6:38
Are you sure you don't want to turn these into a single date type value rather than storing as a string? Do you really need a string here? — Rup
– Rup, Commented Jul 17, 2012 at 9:36

Michael Buen · Accepted Answer · 2012-07-18 12:10:45Z

11

Just use concatenation operator || : http://www.sqlfiddle.com/#!1/d66bb/2

select begin || '-' || month || '-' || year as begin 
from t;

Output:

|     BEGIN |
-------------
| 12-1-1988 |
| 13-3-1900 |
| 14-4-2000 |
| 15-5-2012 |

If you want to change the begin column itself, begin column must be of string type first, then do this: http://www.sqlfiddle.com/#!1/13210/2

update t set begin = begin || '-' || month || '-' || year ;

Output:

|     BEGIN |
-------------
| 12-1-1988 |
| 13-3-1900 |
| 14-4-2000 |
| 15-5-2012 |

UPDATE

About this:

but m not getting null value column date

Use this:

select (begin || '-' || month || '-' || year)::date as begin 
from t

edited Jul 18, 2012 at 12:10

answered Jul 13, 2012 at 5:37

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

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

2 Comments

user1522546 Over a year ago

but m not getting null value column date

Michael Buen Over a year ago

sorry, I can't get what you mean by not getting null value date. if one of the fields has a null, it propagates to the whole expression, hence making the whole result null. Care to elaborate more?

Adriaan Stander · Accepted Answer · 2012-07-13 04:32:27Z

2

Have a look at 9.4. String Functions and Operators

answered Jul 13, 2012 at 4:32

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-06-27 02:25:55Z

0

This is an old post, but I just stumbled upon it. Doesn't it make more sense to create a date data type? You can do that using:

select make_date(year, month, begin)

A date seems more useful than a string (and you can even format it however you like using to_char()).

answered Jun 27, 2017 at 2:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Concatenate three columns data into one column in Postgres

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related