仅当 PostgreSQL 中提供时才使用 ID 进行 UPSERT

Question

通过以下 PostgreSQL 查询，我想“更新插入”一行，这样如果我提供的行数据包含字段“id”的数据，则将使用它，如果不包含，则将使用它将由 postgres 自动生成。这可以实现吗？

架构

create table if not exists spotify_crons (
      id uuid default uuid_generate_v4 () primary key
    , name varchar(32) unique
    , seed_artists varchar(32)
    , seed_tracks varchar(32)
    , seed_genres varchar(256)
    , created_at timestamptz not null default now()
    , updated_at timestamptz not null default now()
);
comment on column spotify_crons.id is 'The UUID of the cron entry';
comment on column spotify_crons.name is 'The unique name of the cron entry';
comment on column spotify_crons.seed_artists is 'The spotify artist seed of the cron entry';
comment on column spotify_crons.seed_tracks is 'The spotify track seed of the cron entry';
comment on column spotify_crons.seed_genres is 'The spotify genre seed of the cron entry';
comment on column spotify_crons.created_at is 'The time of creation for the cron entry';
comment on column spotify_crons.updated_at is 'The time of last update for the cron entry';

查询

insert into spotify_crons
    (
          id
        , name
        , seed_artists
        , seed_tracks
        , seed_genres
        , created_at
        , updated_at
    )
values
    (
          case
            when %(id)s is distinct from null
            then %(id)s
            else gen_random_uuid()
          end
        , %(name)s
        , %(seed_artists)s
        , %(seed_tracks)s
        , %(seed_genres)s
        , now()
        , now()
    )
on
    conflict (id)
do
    update
set
    name = %(name)s
  , seed_artists = %(seed_artists)s
  , seed_tracks = %(seed_tracks)s
  , seed_genres = %(seed_genres)s
  , updated_at = now()
returning id
;

示例：

# Should insert new row with auto generated ID
{ "id": None, "name": "Hello", ...}

# Should insert the row with the provided UUID
{ "id": "some_uuid_that_doesn't exist", "name": "Hello", ...}

# Should update the row with the matching UUID with new data
{ "id": "some_uuid_that_exists", "name": "Hello", ...}

PS：如果重要的话，我正在使用 Python 3.7 和 psycopg 3

Answer 1

像这样的东西应该有效：

INSERT INTO spotify_crons (id, name,
                          -- ...
                          )
VALUES (COALESCE(%(id)s::UUID, uuid_generate_v4()),
        %(name)s,
        -- ...
       )
ON CONFLICT (id)
DO UPDATE SET name=EXCLUDED.name,
              -- ...

• 在 COALESCE 表达式中使用 DEFAULT 关键字是行不通的，但如果您知道的话，这应该不是问题无论如何，您想生成 v4 随机 UUID：COALESCE(%(id)s::UUID, DEFAULT)。

• 如果与唯一名称约束发生冲突，您也可能会遇到其他问题，在这种情况下这个其他问题可能会感兴趣。