1

Take the following tables

CREATE TABLE album(
  id SERIAL PRIMARY KEY NOT NULL,
  duration INTEGER NOT NULL
);

CREATE TABLE genre(
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(32) NOT NULL
);

CREATE TABLE album_genre(
  album_id REFERENCES album(id),
  genre_id REFERENCES genre(id)
);

I have the following data to be inserted 

const album = {
  duration: 12345
  genre1Id: 1,
  genre2Id: 2,
  genre3Id: 3
}

How could I construct a SQL query that inserts the album data and then inserts into album_genre using the genre id's and the inserted album's id

malimichael
  • 249
  • 1
  • 5
  • 17
  • Again, the same as in [your previous solution](http://stackoverflow.com/questions/42579443/how-to-avoid-inserting-null-values-in-postgres-database), it requires completely different solutions, depending on whether the insert data is in memory or in a table. – vitaly-t Mar 04 '17 at 13:44
  • 1
    @vitaly-t All the data to be inserted is coming from client posted data to my API including the genre id's which also exist in table as well. – malimichael Mar 04 '17 at 14:05
  • In that case the first solution by Gordon Linoff is suitable. – vitaly-t Mar 04 '17 at 20:19

1 Answers1

2

In Postgres, you could construct the query using inserts in CTEs. The following works for the situation you describe:

with data(duration, genre1Id, genre2Id, genre3Id) as (
      values(12345, 1, 2, 3)
     ),
     i as (
      insert into albums(duration)
          select duration
          from data
          returning *
     )
insert into album_genre (album_id, genre_id)
    select i.id, v.genre
    from i join
         data d
         on i.duration = d.duration, lateral
         (values (d.genre1Id), (d.genre2Id), (d.genre3Id)) v(genre);

For multiple rows, the same construct works, but assumes that duration is unique. You need a unique identifier to match the inserted rows with the original data.

malimichael
  • 249
  • 1
  • 5
  • 17
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Assuming that insert data is in a table, and not in memory, which the author again fails to disclose. – vitaly-t Mar 04 '17 at 13:45
  • Thanks this works, still very new to programming so not too sure why! I will be sure to do some research into CTE's. – malimichael Mar 04 '17 at 13:51