I am working with a database which has some records in it already, and I can't modify the existing tables although I can create new tables.

I want to create new records which have the same primary keys as some existing records, but somehow maintain both the old record and the new record, and being able to tell which is which. Ideally queries would have to be modified as little as possible to retrieve the new value for a primary key, if there is a new value, otherwise falling back on the sole existing value.

I am thinking this could be called an "overlay" although a web search for that term in a database context finds only the OVERLAY function string replacement and the use of overlays in GIS.

Is there an existing term for the kind of operation I'm describing here? Is there a well-known approach for handling that, possibly including existing libraries or other software? I can puzzle out a half-baked approach but it seems better to try to find something that's already working.

I am working with Postgresql although any generic information would be appreciated.

  • Look up Temporal Tables wiki.postgresql.org/wiki/Temporal_Extensions more of thing in SQL Server though. Feb 15 at 0:01
  • Thanks for the reference, I will take a look at that. Looks like temporal tables is well-known in the context of SQL Server. I'm especially interested in the discussions about the design strategy, pros & cons, stuff like that. Thanks again. Feb 15 at 17:29

1 Answer 1

create table existing_table (id int primary key, val data)

create table custom_table (id int primary key, val data)

select e.id, case c.id when null then e.val else c.val end as val
from existing_table e
left join custom_table c on e.id=c.id
  • Msg 2715, Level 16, State 6, Line 1 Column, parameter, or variable #2: Cannot find data type data. Feb 16 at 11:03
  • 1
    this answer should add some context just the code does not clarify anything. Feb 16 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.