Postgres循环为选定列中的所有值插入N行 [英] Postgres Insert N Rows in a Loop for All Values in a Selected Column

问题描述

假设我将用户存储为

select * from users_t where user_name like 'ABC%';

id   user_name  
1    ABC1
2    ABC2
..   ..

现在，我需要遍历所有 user_name ，并将该数目的INSERT放入另一个表中， RECALLS_T 。其他所有列都是我定义的硬编码常量。

Now I need to loop through all user_name's and make that number of INSERTs into a different table, RECALLS_T. All the other columns are hard-coded constants that I define.

假设下表，其序列名为 RECALLS_T_ID_SEQ 在ID上：

Assume the following table, with a Sequence called RECALLS_T_ID_SEQ on the ID:

id  created_by_user_name  field1   field2
1   ABC1                  Const1   Const2
2   ABC2                  Const1   Const2
..   ..                   ..       ..

如何将它们插入Postgres循环中？

How do I insert these in a Postgres loop?

附加问题另外，如果我需要为每个User条目插入X（例如5）个调用，该怎么办？假设它不是1：1映射，而是5：1，其中5是硬编码的循环号。

ADDITIONAL QUESTION Also, what if I need to insert X (say 5) Recalls for each User entry? Suppose it's not a 1:1 mapping, but 5:1, where 5 is a hard-coded loop number.

推荐答案

您可以在 select >插入语句：

You can use the select in the insert statement:

insert into recalls_t (created_by_user_name, field1, field2)
select user_name, 'Const1', 'Const2'
from users_t 
where user_name like 'ABC%';

使用功能 generate_series（） 为中的每个条目插入多行users_t 。我添加了列 step 来说明这一点：

Use the function generate_series() to insert more than one row for each entry from users_t. I have added the column step to illustrate this:

insert into recalls_t (created_by_user_name, field1, field2, step)
select user_name, 'Const1', 'Const2', step
from users_t 
cross join generate_series(1, 3) as step
where user_name like 'ABC%'
returning *

 id | created_by_user_name | field1 | field2 | step 
----+----------------------+--------+--------+------
  1 | ABC1                 | Const1 | Const2 |    1
  2 | ABC2                 | Const1 | Const2 |    1
  3 | ABC1                 | Const1 | Const2 |    2
  4 | ABC2                 | Const1 | Const2 |    2
  5 | ABC1                 | Const1 | Const2 |    3
  6 | ABC2                 | Const1 | Const2 |    3
(6 rows)

Db

这篇关于Postgres循环为选定列中的所有值插入N行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！