Question 339973 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select and insert with counter in postgres

I wand to select record from one table and insert into other . Here is my table 1

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 inserted

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search