Postgres, Insert into many values with select

Question

This is my first post here so please accept my apologies in advance if anything is out of place.

I have table "Users", for each user there is a statement about whether he participates in promotions "IsParticipates", this is one of the columns of the "Users" table and has a value of true or false. I need to create new table "Stocks". It should contain the name of the promotion and the user ID. There are several standard promotions for one user. But the user may refuse to participate in any or he may have an individual.

This is shortened version of my spreadsheet

  table : Users
+-------+-----------+---------------+
|   id  |   name    |IsParticipates |
+-------+-----------+---------------+
|   1   |   John    |     true      |
|   2   |   Mary    |     false     |
|   3   |   Jeff    |     true      |
|   4   |   Bill    |     false     |
|   5   |   Bob     |     false     |
+-------+-----------+---------------+

I want to get another table

  table : Stocks
+-------+--------------+---------+
|   id  |   name       |  userId |
+-------+--------------+---------+
|   1   |  StockName1  |     1   |
|   2   |  StockName2  |     1   |
|   3   |  StockName3  |     1   |
|   4   |  StockName4  |     1   |
|   5   |  StockName5  |     1   |
|   6   |  StockName6  |     1   |
|   7   |  StockName1  |     3   |
|   8   |  StockName2  |     3   |
|   9   |  StockName3  |     3   |
|  10   |  StockName4  |     3   |
|  11   |  StockName5  |     3   |
|  12   |  StockName6  |     3   |
+-------+--------------+---------+

Now I need to enter the default values. I tried:

INSERT INTO ""Stocks"" 
        (""Name"", ""UserId"")
      WITH
          u as (
              SELECT ""Id""
              FROM ""Users""
              WHERE ""IsParticipates""
          )
      VALUES
      ('StockName1', (SELECT ""Id"" FROM u)),
      ('StockName2', (SELECT ""Id"" FROM u)),
      ('StockName3', (SELECT ""Id"" FROM u)),
      ('StockName4', (SELECT ""Id"" FROM u)),
      ('StockName5', (SELECT ""Id"" FROM u)),
      ('StockName6', (SELECT ""Id"" FROM u));

But this only works if the user is one, but I have a lot of them. I think this could be split into 6 requests since I have 6 stocks, but is this really necessary? Will you have any ideas? Thanks in advance

Answer 1

Create the stocks table with a serial (autoincrement) id column:

create table stocks (id serial primary key, name varchar(20), userid int);

INSERT a CROSS JOIN of the stocknames and the IsParticipates true rows:

insert into stocks (name, userid)
select name, id
from
  (values ('StockName1'),
          ('StockName2'),
          ('StockName3'),
          ('StockName4'),
          ('StockName5'),
          ('StockName6')) v(name)
cross join (select id from users where IsParticipates is true) u;

Verify the result:

select * from stocks;

returns:

id  name    userid
1   StockName1  1
2   StockName2  1
3   StockName3  1
4   StockName4  1
5   StockName5  1
6   StockName6  1
7   StockName1  3
8   StockName2  3
9   StockName3  3
10  StockName4  3
11  StockName5  3
12  StockName6  3

https://dbfiddle.uk/tMHJEJvQ

However, I'd consider storing the different stock names in a table.