![]() So here're the most used different setups for ids using Ecto in PostgreSQL that I can think of: So the elixir and phoenix versions really don't matter for the scope of this, but "ecto" and "postgres" does, so here're the hex packages versions: "ecto": "3.4.5"Īnd I am using PostgreSQL 11. To be honest this project could be a simple mix project with ecto setup, but I want to save some setup time and I want to use phoenix generators to create the schemas. I'll start with a plain phoenix project: mix phx.new hello_world ![]() The main focus of this post is to discuss if the type of primary key affects the UPSERT command. Also if you want to seed some data, this is also a way to "reset" that data to the seeded one. It's a great fit to be used when importing or seeding data into a database, as this command can be executed many times for the same data without raising not unique constraints. I choose this strategy because I think that it's very powerful and useful command. In this post we're taking a look into an specific type of upserts: Insert a row into the database, or if that row exists, then we update a subset of the input. There are some ways to set this up, so please check the Ecto UPSERT for reference. In the Ecto land we can use "UPSERT" by calling a regular /2 function and set some options that defines the upsert strategy. So everytime that I mention UPSERT in this post I mean that INSERT. As we can infer, they are not the same SQL statement.Įcto follows an approach and naming convention very similar to the PostgreSQL implementation. MySQL as another example uses a slightly different command, it would be something like: INSERT. You can find out more information about PostgreSQL UPSERT in this great post. As we can see this is a regular INSERT command, with some kind of checking for existing data based on conflicts and then, if that happens, there's an UPDATE section. PostgreSQL, for instance, introduced it on the 9.5 version and it takes the form of INSERT. This is just a common name that developers use to describe an atomic "INSERT OR UPDATE" SQL command. Don't expect this feature to be present in all database systems, and don't expect that the databases that implement that will implement using the same syntax. I was intrigued by that and I started to investigate if the type of the primary key would have a different behavior in this command.īefore jumping into that, there's no UPSERT command in SQL nor in Ecto. ![]() Spoiler alert, you could be surprised by non expected returns, so bare with me.Ī couple of days ago I found a strange behavior with the return of an "UPSERT" Ecto command. Let's take a look into Ecto's implementation of PostgreSQL Upsert and how different types of primary keys could change the results of this command. ![]() Elixir PostgreSQL Ecto UPSERT for different Primary Keys in Elixir ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |