Select and insert with counter

Question

I want to select rows from one table and insert into another. Here is my first table:

create table temp 
(
    seq varchar(20),
    prefix varchar(20)
);

insert into temp values ('AAA','A'); -- counter should be 1 as combo of AAA and A is 1
insert into temp values ('AAA','A'); -- counter should be 2 as combo of AAA and A is 2
insert into temp values ('BBB','B'); -- counter should be 1 as combo of BBB and B is 1
insert into temp values ('BBB','B'); -- counter should be 2 as combo of BBB and B is 2
insert into temp values ('BBB','C'); -- counter should be 1 as combo of BBB and C is 1

Now inserting from temp to temp_1

INSERT INTO temp_1 (seq,prefix,counter)  
SELECT 
    seq, prefix, 
    (SELECT COUNT(*) FROM temp t 
     WHERE t.seq = t2.seq AND t.prefix = t2.prefix)
FROM temp t2;  

This is what's inserted.

Answer 1

You can make use of row_number for each group of seq,prefix

INSERT INTO temp_1 (seq,prefix,counter)  
 SELECT seq, prefix, row_number() OVER (partition by seq, prefix)
 FROM tmp;

example:

WITH src(seq,prefix) as ( values ('AAA','A'),('AAA','A'),('BBB','B'),('BBB','B'),('BBB','C'),('BBB','B'))
SELECT seq,prefix,row_number() OVER (partition by seq,prefix)
FROM src;

 seq | prefix | row_number
-----+--------+------------
 AAA | A      |          1
 AAA | A      |          2
 BBB | B      |          1
 BBB | B      |          2
 BBB | B      |          3
 BBB | C      |          1
(6 rows)

Answer 2

Most likely you need the window function row_number() for this. At least it gives you the results you're looking for:

INSERT INTO temp_1 (seq,prefix,counter)  
SELECT seq
    ,prefix
    , row_number() OVER (PARTITION BY prefix) as row_number
FROM temp;