Postgresql - Postgres, Insert into many values with select

Alyona
August 16, 2023
356 views
0 votes
2 Answers

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

Answers

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.

Assuming you have Tables like below,

CREATE TABLE Users (
    id serial PRIMARY KEY,
    name varchar(255),
    IsParticipates boolean
);

CREATE TABLE Stocks (
    id serial PRIMARY KEY,
    name varchar(255),
    userId integer
);

Replace StockName with actual ones

WITH TempStocks AS ( -- update values here ↓
    SELECT unnest(ARRAY['StockName1', 'StockName2', 'StockName3', 'StockName4', 'StockName5', 'StockName6']) AS stockName
)
INSERT INTO Stocks (name, userId)
SELECT ts.stockName, u.id
FROM Users u
CROSS JOIN TempStocks ts
WHERE u.IsParticipates;

select * from Stocks;

Demo

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Postgres, Insert into many values with select

Answers