Return Upserted Data

-

There was an interesting issue recently opened. The user made several interesting decisions that to my view can be useful for all (click to see):

  • Spatial types. Not only Postgres has a rich GIS/Spatial support, the driver offers dedicated types, to make the experience as smooth as possible.
  • Batch insert with ON_CONFLICT clause - making the insertion pipeline very efficient.
  • RETURNING clause. Since the ON_CONFLICT clause's action is DO_NOTHING, the clause will return all the INSERTed rows. I believe that it filters out a substantial part of objects, so it effectively reduces the amount of work that needs to be performed with the output (instead of the entire set).

Comments

Post a Comment

Popular posts from this blog

Hierarchical Query in SQL with C# and Entity Framework Core

-
Image
Hierarchies are very common in our lives and therefore in the problems we need to solve. The most familiar cases are organizations, products built from parts and other products, file systems etc. When an hierarchy is stored in a database table, it's usually represented by rows referencing parent rows, recursively. Suppose we want to query all the employees (directly or indirectly) reporting to a mid-level manager. Can we do that in SQL? Of course there is a naive solution to select employees directly reporting to that manager, take their ids, perform the query again, etc. But clearly it's not efficient. Fortunately SQL has a standard support for hierarchical queries in a single and efficient query. The following example demonstrates an hierarchical query using C# and EF Core using ELINQ : int ? managerId = null ; //external parameter - "highest" manager id DbContext.Staffs.Query(() =...
Read more

Entity Framework - High Performance Querying Using Temp Tables

-
Image
Today I came across a nice article explaining how we can optimize a query with multiple Include() of related entities. Usage of a temporary is nice, but the way it's created is not. There is absolutely no need to bring the Ids to the app server and then push them back to the database. The whole operation can be performed in the database using SELECT ... INTO construct: static void PrepareStagingCategories ( Product product ) { var temp = ToTable<Product>(TEMP_PRODUCT_IDS); // create temporary table with product ids SELECT<Product>(product.ProductId.@ as ()).INTO(temp); FROM(product); WHERE(product.Name.Contains( "Chocolate" )); }
Read more

SQL: SELECT DISTINCT on Some Columns

-
Image
In some cases we want to ensure uniqueness only on a subset of row columns. There are many different solutions proposed, some of them are simple and others are less. I didn't find a generic one. So let's state the problem first, then analyze it and define a generic solution. Problem DISTINCT is not a function, we cannot "apply" it on a subset of columns. Some folks try to substitute it with GROUP BY . But GROUP BY requires that all the SELECT ed columns will be either grouped by or aggregated. Let's assume we use a FIRST / MIN / MAX aggregate. In this case all the columns that are not grouped by will have same values. I can hardly believe this is desired. Analysis Let's understand better why the original DISTINCT approach does not work. We get too many rows, since all those not important columns are returned with their multiple values, therefore we have duplicates on the im...
Read more