How to iteratively use INSERT INTO in PostgreSQL

Question

I have Table1 with rows I want to iterate over, and insert values from those rows of data into another table.

Table1Name     ->      ID      Name  Date        Type
--------               -------------------------------
a                      101     a     2021-07-01  original
b                      139     b     2021-07-04  original
c                      322     c     2021-07-08  original
d                      431     d     2021-07-09  original

So basically pseudo is:

Loop over Table1:
    INSERT INTO Table2 (id, name, date) VALUES (random(), Table1.name, NOW(), 'original')

Just looping through Table1, adding in one of its values every time.

Sample data and desired results would really help.

Gordon Linoff
– Gordon Linoff

2021-07-09 14:25:18 +00:00
Commented Jul 9, 2021 at 14:25 — Gordon Linoff
– Gordon Linoff, Commented Jul 9, 2021 at 14:25
@GordonLinoff added some extra details

chung
– chung

2021-07-09 14:49:09 +00:00
Commented Jul 9, 2021 at 14:49 — chung
– chung, Commented Jul 9, 2021 at 14:49

Tim Biegeleisen · Accepted Answer · 2021-07-09 14:56:04Z

3

I think you just want an INSERT INTO ... SELECT here:

INSERT INTO Table2 (id, name, date)
SELECT RANDOM(), name, NOW()
FROM Table1;

Most SQL operations are inherently set based, meaning if you want to insert all records from one table into another, the operation would be viewed as a single thing.

edited Jul 9, 2021 at 14:56

answered Jul 9, 2021 at 14:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

4 Comments

chung Over a year ago

hey I added some more details, there are other values I'd want to pass in too.

Tim Biegeleisen Over a year ago

@chung I have updated my answer, but I'm sticking with my general premise.

chung Over a year ago

oh I didn't know you could add like "RANDOM()" into a select, thanks!

bfris Over a year ago

Could/Should you use something like nextval() for an id column which is likely to be a serial datatype?

Collectives™ on Stack Overflow

How to iteratively use INSERT INTO in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related