How to model external ids with ability to do upserts

[ssendev]

I have multiple external sources from which i want to import data and keep it updated for this purpose i need to store external id's the problem is they can have different types so some are int while others are string or uuid.

What's the best way to model this?

the simplest would be

type Movie {
  name: str;
  ext: tuple<source: str, id: str> { constraint exclusive; }
}

and then convert everyting to str. or go full out and define multiple types

type ExtSource {
  required url: str { constraint exclusive; }
}
abstract type Ext {
  ext_source: ExtSource;
}
type ExtInt extending Ext {
  required ext_int: int64;
  constraint exclusive on ((.ext_source, .ext_int));
}
type ExtStr extending Ext {
  required ext_str: str;
  constraint exclusive on ((.ext_source, .ext_str));
}

how to tie the Movie to it?

type Movie {
  name: str;
  link ext: Ext
}

but then how to do the upsert?
another way would be

type Movie extending ExtInt, ExtStr {
  name: str;
}

but that seems convoluted or is it the performance okay?

[elprans]

Using inheritance would be the way, as you need the constraints to be local to the object your upserting.

There are some limitations currently when it comes to unless conflict and constraint inheritance. In this case the issue is that you cannot use non-delegated constraints in insert Movie unless conflict else (update Movie ...), because the conflicting object could be any ExtStr or ExtInt, not just a Movie!

But if you don't need exclusivity across data types, then delegated constraints would work just fine, e.g:

type ExtSource {
  required url: str { constraint exclusive; }
}
abstract type Ext {
  ext_source: ExtSource;
}
type ExtInt extending Ext {
  ext_int: int64;
  delegated constraint exclusive on ((.ext_source, .ext_int));
}
type ExtStr extending Ext {
  ext_str: str;
  delegated constraint exclusive on ((.ext_source, .ext_str));
}
type Movie extending ExtInt, ExtStr {
  name: str;
};

and then

insert Movie {
  name := 'Dune',
  ext_str := '1',
  ext_source := (select ExtSource filter .url = '...')
}
unless conflict on (.ext_str, .ext_source)
else (
  update Movie
  set {
    name := 'Dune'
  }
);

[abecciu]

@elprans why wouldn't you recommend the simpler "convert everything to string" approach?

[elprans]

It's definitely an option if retaining the original type is not necessary.