search

heavyai / heavydb

HeavyDB (formerly OmniSciDB)
https://heavy.ai
Apache License 2.0
2.95k stars 448 forks source link

Is UPSERT supported? #554

Open YanzheL opened 4 years ago

YanzheL commented 4 years ago

From these lines of code in ParserWrapper.h and ParserWrapper.cpp, it shows the UPSERT keyword is used. But I can't find any documentation of its syntax.

If it is not implemented yet, when will it be available?

My use case is that I need to sync kafka KTable changelog stream to a table in OmnisciDB. Without upsert support, this operation will use two SQL queries for one insert (check existence, then insert/update).

alexbaden commented 4 years ago

Hi @YanzheL

UPSERT is part of our calcite grammar but is not currently supported. We're planning some other enhancements to our update and insert paths first, so unfortunately UPSERT is not currently on the near term roadmap.

cmdares commented 3 years ago

regard

mdespriee commented 3 years ago

@YanzheL did you manage to workaround the lack of UPSERT ? I have the same situation (KTABLE), so UPSERT is central in this use-case... What did you do ? Custom consumer with custom insert/update logic ?

kimgiftww commented 2 years ago

Would like to see UPSERT implemented as well.

I wonder would EXISTS and NOT EXISTS subqueries (https://docs.omnisci.com/v5.1.1/) be used to partially replicate what UPSERT does?

cdessanti commented 2 years ago

Hi,

Upsert is like Oracle's Merge statement, right? So using semijoins can be replicated. You have to ten two instructions (update and insert) instead a single one