如何避免在Postgres数据库中插入空值

徐君源

我有以下查询，将数据插入到多对多联接表中
INSERT INTO playlist_genre(playlist_id, genre_id)
VALUES  (${playlistId}, ${genre1Id}),
      (${playlistId}, ${genre2Id}),
      (${playlistId}, ${genre3Id})
);
但是，我遇到的问题是用户并不需要这些值genre2Id和genre3Id，因此可以是anINTEGER或NULL。
我正在尝试找到一种方法来写入此相同的查询，但因此仅在存在值的情况下才插入。两列都有NOT NULL约束。
编辑：
这是我的播放列表课程
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));
});
  }
这是sqlCreatePlaylist的样子
INSERT INTO playlists(
  user_id,
  title,
  playlist_type,
  number_of_tracks,
  duration,
  description,
  release_date
)
VALUES(
  ${userId},
  ${title},
  ${playlistType},
  ${numberOfTracks},
  ${lengthInSeconds},
  ${description},
  ${releaseDate}
)
RETURNING *;

解决方案:

如何避免在Postgres数据库中插入空值

徐君源 LV1