Generate N Records-CodePudding

Is there a faster way to do something like this? Maybe using generate_series?

INSERT INTO users (email)
VALUES
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]')

Is it possible to also easily add a relation for a profile?

INSERT INTO profiles (user_id)
SELECT id FROM users WHERE email='[email protected]'

INSERT INTO profiles (user_id)
SELECT id FROM users WHERE email='[email protected]'

INSERT INTO profiles (user_id)
SELECT id FROM users WHERE email='[email protected]'

CodePudding user response：

You may try:

WITH cte AS (
    SELECT generate_series(1, 3, 1) AS num
)

INSERT INTO users (email)
SELECT 'user' || num::text || '@domain.com'
FROM cte;

Modify the second parameter to generate_series if you want to generate more than 3 user emails.

Edit: For your updated requirement:

WITH emails AS (
    SELECT 'user' || generate_series(1, 3) || '@domain.com' AS email
)

INSERT INTO profiles (user_id)
SELECT id FROM users WHERE email IN (SELECT email FROM emails);

CodePudding user response：

INSERT INTO users
SELECT 'user' || generate_series(1, 10) || '@domain.com'

generate_series(1, 10) - This generates a series of 1 to 10 rows

As per the updated requirement,

INSERT INTO profiles (user_id)
SELECT id
FROM users
WHERE email in
    (SELECT 'user' || generate_series(1, 10) || '@domain.com')