如何避免在Postgres数据库中插入空值 [英] How to avoid inserting null values in postgres database

问题描述

我有以下查询，可将数据插入到多对多连接表中

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})
);

但是我遇到的问题是值 genre2Id 和 genre3Id ，因此可以是 INTEGER 或 NULL 。

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.

我正试图找到一种方法来编写相同的查询，但只有在存在值时才插入。两列都有 NOT 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.

编辑：

这是我的播放列表类

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的样子

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 *;

推荐答案

仅插入非null：

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

这篇关于如何避免在Postgres数据库中插入空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！