CODEWITHSUNDEEP
sqlpostgresqlgenerate-series
MikeiLL
MikeiLL

Reputation: 6570

Insert table rows based on concatenated rows from a series

Two tables, 'user' and 'tracks' and I want to create a user for each track that doesn't already have one (userid=0).

We don't have the unique emails required for user so aim to create dummy emails using generate_series:

insert into users (username, email) 
select artist, (
select concat(generate_series(1, (SELECT COUNT(filename) 
                FROM tracks where userid = 0)),'@domain.com')
                ) as email
from tracks 
where userid = 0 
group by artist;

Of course getting the error:

'ERROR:  more than one row returned by a subquery used as an expression'

I could use two SQL calls and have the parent code (Python) perform the loop, but I imagine the right way to do it is just within the SQL statement.

So how do I make the Insert statement sort of, 'loop' through the select?

The expected output:

 id  |                         username                          |         email         
-----+-----------------------------------------------------------+-----------------------
  62 | Sam Jackson                                               | [email protected]
  63 | (unknown person)                                          | [email protected]
  64 | Howard Cocel J.                                           | [email protected]

Upvotes: 0

Views: 133

Answers (2)

Simo Kivistö
Simo Kivistö

Reputation: 4503

You can do this with row_number() window function:

INSERT INTO users (username, email)
(SELECT artist, row_number() OVER () || '@domain.com'    
   FROM (SELECT artist
    FROM tracks 
    WHERE userid = 0 
    GROUP BY artist) sq1
);

Upvotes: 1

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26946

If I well understood your question you need the following query. It is not necessary to create a second query because you operate on the same table (tracks) and on the same userid (0). 

insert into users (username, email) 
select 
artist, 
concat(generate_series(1, COUNT(filename)),'@domain.com') as email
from tracks 
where userid = 0 
group by artist;

Upvotes: 0

Related Questions