INSERT ... on conflict do nothing - reading in a csv and generating foreign key table

Question

I'm trying to read in a csv file with the columns artists, albums, songs, and tags.

I wish to populate the artist_album_song table like so:

|artist_id|album_id|song_id|
|---------|--------|-------|
|   1     |     1  |     1 |
|   1     |     1  |     2 |
|   1     |     2  |     1 |
...
|  12     |     1  |     1 |
...

I've designed and am now trying to populate the following tables. The problem is populating the foreign keys in the artist_album_song table as I read in the csv.

What's the best way to INSERT into this table which achieves what I'm trying to do in the INSERT statements I use below (which return a syntax error)? Thanks.

create table artists (
    artist_id SERIAL PRIMARY KEY,
    artist VARCHAR(100) NOT NULL UNIQUE
);

create table albums (
    album_id SERIAL PRIMARY KEY,
    album VARCHAR(100) NOT NULL UNIQUE
);

create table songs (
    song_id SERIAL PRIMARY KEY,
    song VARCHAR(250) NOT NULL UNIQUE
);

create table tags (
    tag_id SERIAL PRIMARY KEY,
    tag VARCHAR(100) NOT NULL UNIQUE
);

create table artists_albums_songs (
    artist_id INTEGER NOT NULL,
    album_id INTEGER NOT NULL,
    song_id INTEGER NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id),
    FOREIGN KEY (album_id) REFERENCES albums(album_id),
    FOREIGN KEY (song_id) REFERENCES songs(song_id),
    PRIMARY KEY (artist_id, album_id, song_id)
);

create table songs_tags (
    song_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (song_id) REFERENCES songs(song_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id),
    PRIMARY KEY (song_id, tag_id)
);

After trying all sorts of variations of statements from the links below, I still can't get this to work.

I've tried the following statements but I keeping getting errors. The first one returns the error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "ON" Position: 161;

Does the 161 refer to the 161st character in the below SQL statement?

INSERT INTO artists_albums_songs
SELECT artist_id, album_id, song_id 
FROM artists a 
    JOIN albums b
        ON a.artist = ?
        AND b.album = ?
    JOIN songs c
        ON c.song = ?
    ON DUPLICATE (artist_id, album_id, song_id) DO NOTHING;

INSERT INTO artists_albums_songs
SELECT artist_id, album_id, song_id 
FROM artists a 
    JOIN albums b
        ON a.artist = ?
        AND b.album = ?
    JOIN songs c
        ON c.song = ?
    WHERE NOT EXISTS (
        SELECT * 
        FROM artists_albums_songs
        WHERE * = ?, ?, ?)

INSERT INTO artists_albums_songs
SELECT artist_id, album_id, song_id 
FROM artists a 
    JOIN albums b
        ON a.artist = ?
        AND b.album = ?
    JOIN songs c
        ON c.song = ?
    ON CONFLICT (song_id) IGNORE;

EDIT: If I remove the last line on the 3 INSERT statements above, it works, but when it comes across a duplicate it says:

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "artists_albums_songs_pkey"
  Detail: Key (artist_id, album_id, song_id)=(1, 1, 1) already exists.

Insert, on duplicate update in PostgreSQL?

Use INSERT ... ON CONFLICT DO NOTHING RETURNING failed rows

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

Answer 1

EDIT 1: I just realised I can handle these errors in Java! So my solution involves just adding a catch statement to handle the Duplicate SQLException

private <T> void insertIntoArtistAlbumSong(T artist, T album, T song) throws SQLException {

    try {

        String artString = artist.toString();
        String albString = album.toString();
        String songString = song.toString();

        // Create SQL insert statement
        String stm =
                "INSERT INTO artists_albums_songs " +
                        "SELECT artist_id, album_id, song_id " +
                        "FROM artists a " +
                        "JOIN albums b " +
                        "ON a.artist = ? " +
                        "AND b.album = ? " +
                        "JOIN songs c " +
                        "ON c.song = ? ;";


        PreparedStatement pstmt = connection.prepareStatement(stm);

        // Set values in prepared statement
        pstmt.setString(1, artString);
        pstmt.setString(2, albString);
        pstmt.setString(3, songString);

        // Insert into table
        pstmt.executeUpdate();

    // ADDED THIS CATCH STATEMENT!
    } catch (SQLException e){
        System.out.println(e.getSQLState());
    }
}

---

Okay, so I found a solution but it only works for populating the table (which is all I actually have to do).

1. Drop the original artists_albums_songs[1] table

2. Create the new artists_albums_songs[2] table without constraints:

   CREATE TABLE artists_albums_songs (
       artist_id INTEGER NOT NULL,
       album_id INTEGER NOT NULL,
       song_id INTEGER NOT NULL
   );
   

3. I then populate the new table[2] with the following statement (via JDBC):

   INSERT INTO artists_albums_songs
   SELECT artist_id, album_id, song_id 
   FROM artists a 
       JOIN albums b
           ON a.artist = ?
           AND b.album = ?
       JOIN songs c
           ON c.song = ?;
   

4. Create a tmp[3] table with the constraints (via psql command line):

   CREATE TABLE tmp (
       artist_id INTEGER NOT NULL,
       album_id INTEGER NOT NULL,
       song_id INTEGER NOT NULL,
       FOREIGN KEY (artist_id) REFERENCES artists(artist_id),
       FOREIGN KEY (album_id) REFERENCES albums(album_id),
       FOREIGN KEY (song_id) REFERENCES songs(song_id),
       PRIMARY KEY (artist_id, album_id, song_id)
   );
   

5. Insert only the distinct rows from the new artists_albums_songs[2] into tmp[3] (via psql):

   INSERT INTO tmp SELECT DISTINCT * FROM artists_albums_songs
   ORDER BY artist_id, album_id, song_id ASC;
   

6. Drop the new artists_albums_songs[2] and rename tmp[3] to artists_albums_songs (via psql):

   DROP TABLE artists_albums_songs;
   ALTER TABLE tmp RENAME TO artists_albums_songs;
   

Answer 2

Error in this row:

 ON DUPLICATE (artist_id, album_id, song_id) DO NOTHING;

Postgtresql use ON CONFLICT keyword https://www.postgresql.org/docs/current/static/sql-insert.html