How to grab the ID of an artist so i can insert the album into the database with the artist's I-CodePudding

Here is my schema:

CREATE TABLE artists 
(
    id serial PRIMARY KEY,
    name varchar(40) NOT NULL UNIQUE
);

CREATE TABLE albums 
(
    id serial PRIMARY KEY,
    name varchar(40) NOT NULL,
    artist integer NOT NULL,
    FOREIGN KEY(artist) REFERENCES artists(id)
);

CREATE TABLE songs 
(
    id serial PRIMARY KEY,
    name varchar(40) NOT NULL,
    album integer NOT NULL,
    FOREIGN KEY(album) REFERENCES albums(id)
);

This is what I'm doing:

INSERT INTO artists (name) 
VALUES ('Dio') ON CONFLICT DO NOTHING;
INSERT INTO artists (name) 
VALUES ('Amorphis') ON CONFLICT DO NOTHING;

SELECT id FROM artists WHERE name = 'Dio';
SELECT id FROM artists WHERE name = 'Amorphis';
   
INSERT INTO albums (name, artist) VALUES ('Holy Diver', 1) ON CONFLICT DO NOTHING;
INSERT INTO albums (name, artist) VALUES ('Dream Evil', 1) ON CONFLICT DO NOTHING;
INSERT INTO albums (name, artist) VALUES ('Halo', 2) ON CONFLICT DO NOTHING;

Right now I'm inserting into albums by looking at the table for the artist ID and typing manually. How can I do this automatically?

My application will receive the song title, artist name and album name. So it will need to look for the artist ID if the artist exists and if not, create the artist, then grab the ID of said artist and put into the INSERT INTO albums statement.

CodePudding user response：

One way:

INSERT INTO albums (name, artist) 
SELECT 'Holy Diver' AS album_name, id AS artist 
FROM artists 
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;

Another way to do multiple albums from the same artist at once. You can just list them in the VALUES definition.

INSERT INTO albums (name, artist) 
SELECT album_name, id AS artist 
FROM artists,
     (
      VALUES ('Holy Diver'),
             ('Dream Evil')
     ) t(album_name)
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;

And another way for good measure:

INSERT INTO albums (name, artist) 
SELECT album_name, id AS artist 
FROM artists,
     (SELECT 'Holy Diver' AS album_name
      UNION
      SELECT 'Dream Evil') t
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;

And another way:

INSERT INTO albums (name, artist) 
SELECT UNNEST(ARRAY['Holy Diver','Dream Evil']) AS album_name,
       id AS artist 
FROM artists
WHERE name = 'Dio'
ON CONFLICT DO NOTHING;

Ok, I'll stop.