insert statement in postgresql

Question

I have a table with 8 records.

I have 4 records for Dec-17, same 4 records for Jan-18 (only yearmon changes)

I am trying to have same records for the month of Feb-18 as well. All the column values should be same, only the yearmon has to change to Feb-18.

I can write an insert statement if I have less than 5 records. Is there any other way to write a query to get the desired output?

income   score_1  score_2  rank  yearmon
     800      77      90      10   Dec-17
     900      72      44      99   Dec-17
     700      62      44      06   Dec-17
     600      55      23      06   Dec-17
     800      77      90      10   Jan-18
     900      72      44      99   Jan-18
     700      62      44      06   Jan-18
     600      55      23      06   Jan-18

Output looks like

   income   score_1  score_2  rank  yearmon
     800      77      90      10    Dec-17
     900      72      44      99    Dec-17
     700      62      44      06    Dec-17
     600      55      23      06    Dec-17
     800      77      90      10    Jan-18
     900      72      44      99    Jan-18
     700      62      44      06    Jan-18
     600      55      23      06    Jan-18
     800      77      90      10    Feb-18
     900      72      44      99    Feb-18
     700      62      44      06    Feb-18
     600      55      23      06    Feb-18

"i have a table with 8 records." and "But in my master table i have 2000 records for Jan-18" contradict each other. Please correct your question — Ilya Konyukhov
– Ilya Konyukhov, Commented Oct 11, 2018 at 3:13

Gordon Linoff · Accepted Answer · 2018-10-11 11:36:11Z

3

This answers the original question.

The simplest method is union all:

select income, score_1, score_2, rank, yearmon
from t
union all
select income, score_1, score_2, rank, 'Feb-2018'
from t;

If you want to change the table, then insert:

insert into t (income, score_1, score_2, rank, yearmon)
    select income, score_1, score_2, rank, 'Feb-2018'
    from t;

edited Oct 11, 2018 at 11:36

answered Oct 11, 2018 at 2:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user8545255 Over a year ago

Union all gives me different output in some scenarios.Updated my Question.

AmeyaN99 · Accepted Answer · 2018-10-11 06:36:27Z

1

First insert all Distinct values of income, score_1, score_2, RANK combination. then update yearmon to Feb-18

INSERT INTO T
       SELECT DISTINCT income, score_1, score_2, RANK
         FROM T;

    UPDATE T
       SET yearmon = 'Feb-18'
     WHERE yearmon IS NULL;

answered Oct 11, 2018 at 6:36

AmeyaN99

1724 bronze badges

Collectives™ on Stack Overflow

insert statement in postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related