how to create an extra column and rows using select query in postgresql

Question

Following is a sample of what am trying to achieve (never mind the select query because it just to show my actual problem)

for example,

select col1 from(
select 'tab09' as col1
union 
select 'tab09_01' 
union 
select 'tab09_02' 
union 
select 'tab09_03' 
union 
select 'tab09_04' 
) t order by col1

will return

    col1
    ----------
    tab09
    tab09_01
    tab09_02
    tab09_03
    tab09_04

So, Which PostgreSQL function will helps to get the result like below

    col1       col2
    ----------+----------
    tab09      tab10
    tab09_01   tab10_01
    tab09_02   tab10_02
    tab09_03   tab10_03
    tab09_04   tab10_04

Answer 1

select col1,overlay(col1 placing '10' from 4 for 2) col2 from(
--your select query goes here
) t order by col1

       

---

overlay-postgresql doc

Answer 2

oh oh, i see a MAJOR problem here. UNION is definitely not what you want here. There is a major difference between UNION and UNION ALL. UNION automatically filters duplicates, which is not your goal. UNION ALL does an append. This is a very common mistake many SQL users tend to make. Here are some examples. I hope it helps: http://www.cybertec.at/common-mistakes-union-vs-union-all/ usually a UNION vs UNION ALL problems reaches my desk hidden as "performance problem".