How to avoid inserting null values in postgres database

Question

I have the following query that inserts data into a many-to-many join table

INSERT INTO playlist_genre(playlist_id, genre_id)
VALUES  (${playlistId}, ${genre1Id}),
        (${playlistId}, ${genre2Id}),
        (${playlistId}, ${genre3Id})
);

However the problem I've come to is that the values genre2Id and genre3Id are not required by the user so could either be an INTEGER or NULL.

I am trying to find a way to write this same query but so it only inserts if a value is present. Both columns have NOT NULL constraints.

Edit:

Here is my Playlist class

class Playlist {
  constructor(playlist) {
    // required fields
    this.title = playlist.title;
    this.playlistType = playlist.playlistType;
    this.userId = playlist.userId;
    this.numberOfTracks = playlist.numberOfTracks;
    this.lengthInSeconds = playlist.lengthInSeconds;
    this.genre1Id = playlist.genre1Id;
    // not required fields
    this.genre2Id = playlist.genre2Id || null;
    this.genre3Id = playlist.genre3Id || null;
    this.description = playlist.description || null;
    this.releaseDate = playlist.releaseDate || null;
  }

  save() {
    return new Promise((resolve, reject) => {
      db.one(sqlCreatePlaylist, this)
        .then((insertedPlaylist) => {
          // Here is where i want to use insertedPlaylist's id 
          // to insert data into 'playlist_genre' table
          resolve(insertedPlaylist);
        })
        .catch(error => reject(error));
    });
  }

Here is what sqlCreatePlaylist looks like

INSERT INTO playlists(
  user_id,
  title,
  playlist_type,
  number_of_tracks,
  duration,
  description,
  release_date
)
VALUES(
  ${userId},
  ${title},
  ${playlistType},
  ${numberOfTracks},
  ${lengthInSeconds},
  ${description},
  ${releaseDate}
)
RETURNING *;

@GordonLinoff to avoid having loads of `NULL` values and unnecessary columns in my table. — malimichael, Mar 03 '17 at 13:07
Directly related: [skip update columns with pg-promise](http://stackoverflow.com/questions/40697330/skip-update-columns-with-pg-promise). — vitaly-t, Mar 03 '17 at 17:53
It is confusing, because you accept an answer that indicates that the data you are trying to insert is in another table, while your own example with the property variables point at using data in memory. As an author of pg-promise, I was going to publish a proper answer, but you need to clarify that thing first. — vitaly-t, Mar 03 '17 at 18:00

score 3 · Accepted Answer · answered Mar 03 '17 at 13:04

To only insert not nulls:

insert into playlist_genre (playlist_id, genre_id)
select playlist_id, genre_id
from (values
    (${playlistid}, ${genre1id}),
    (${playlistid}, ${genre2id}),
    (${playlistid}, ${genre3id})
) s (playlist_id, genre_id)
where genre_id is not null

How to avoid inserting null values in postgres database

1 Answers1

Linked