CODEWITHSUNDEEP
pythonpostgresqlsql-updatesql-insertupsert
Mr. Developerdude
Mr. Developerdude

Reputation: 9668

UPSERT using ID only when provioded in PostgreSQL

With the following PostgreSQL query, I would like to "upsert" a row such that if the row data I provide contains data for the field "id", it will be used, and if if not, it will be autogenerated by postgres. Is this possible to carry out?

Schema

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

Query

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
;

Examples:

# 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: I am using Python 3.7 with psycopg 3 if that matters

Upvotes: 0

Views: 992

Answers (1)

Bruno
Bruno

Reputation: 122669

Something like this should work:

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,
              -- ...

  • Using the DEFAULT keyword in the COALESCE expression like this wouldn't work, but it shouldn't really be an issue if you know you want to generate a v4 random UUID anyway: COALESCE(%(id)s::UUID, DEFAULT).

  • You may also run into other problems if you get conflicts with the unique name constraint, in which case this other question may be of interest.

Upvotes: 1

Related Questions