[Postgres] On conflict Do Something Clause in Postgres
Instead of first checking to see if a record already exists within your table, we can do a on conflict do update. In this command, we can ether insert a row into our table, if it does exist, then check to see if all of the columns match up. This saves us a database call and is pretty straightforward to understand.
postgres=# insert into users (user_handle, first_name, last_name, email)
values (uuid_generate_v4(), 'Lucie', 'Jones', 'Lucie-Jones@gmail.com') on conflict do nothing:
We can also choose to update
instead of doing nothing
:
postgres=# insert into users values (uuid_generate_v4(), 'Lucie', 'Hawkins', 'Lucie-Jones@gmail.com')
on conflict (email) do update set first_name = excluded.first_name, last_name = excluded.last_name;
With this command (on conflict <column name> do
), you choose the column in which there is a conflict (user has same email address, but has changed their last name, in this case), and then define the columns you wants to update when this conflict occurs. (eg. Lucie Jones' name will be updated to Lucie Hawkins because her account was identified by the email address conflict)
The excluded.
refers to incoming data for that column.
This action is commonly referred to as an "upsert".
We can also update this query with a where
clause.
postgres=# insert into users as u values (uuid_generate_v4(), 'Lucie', 'Cook', 'Lucie-Jones@gmail.com')
on conflict (email) do update set first_name = excluded.first_name, last_name = excluded.last_name
where u.first_name <> 'Lucie';
<>
for 'does not equal'
In this example, if there is an email
conflict and the original records firstname
is equal to 'Lucie' the row will not be updated
相关文章
- hive on spark VS SparkSQL VS hive on tez
- 将Sql Server迁移到Always on集群 - 账号的同步
- RESTORE detected an error on page (0:0) in database
- [Mise] Update a count state value with the x-on event listener directive in Alpine JS
- [PWA] Disable Text Selection and Touch Callouts in a PWA on iOS
- [Angular] Design API for show / hide components based on Auth
- [Firebase + PWA] Keynote: Progressive Web Apps on Firebase
- {sharepoint} More on SharePoint 2010 Application Pools
- Plugging mcrypt into PHP, on Mac OS X Snow Leopard 10.6.1[转]
- String copy on write 引发的线程不安全
- [Javascript] Broadcaster + Operator + Listener pattern -- 14. Marking Done Based on a Condition
- [Tool] Enable Prettier in VSCode as Format on Save and add config files to gitingore
- [Javascript] Use a custom sort function on an Array in Javascript
- [React] Update State Based on Props using the Lifecycle Hook getDerivedStateFromProps in React16.3
- check user valid on JQuery
- Install MongoDB Community Edition on Windows
- why changes on Object in CRM system could not be replicated
- why changes on Object in CRM system could not be replicated
- [CoreBluetooth] API MISUSE: can only accept this command while in the powered on state(iOS蓝牙)
- Could not find method implementation() for arguments [directory ‘libs‘] on object of type org.gradle
- linux 空间不够了,怎么办?Disk Requirements:At least 11MB more space needed on the / filesystem.
- NVIDIA之AI Course:Getting Started with AI on Jetson Nano—Class notes(二)
- NVIDIA之AI Course:Getting Started with AI on Jetson Nano—Class notes(四)
- 已解决SyntaxError: Non-UTF-8 code starting with ‘å‘ in file E:/Python/test6.py on line 1, but no enc
- org.apache.thrift.transport.TTransportException: Could not create ServerSocket on address 0.0.0.0/0.0.0.0:9083.
- 论文解读(Survey)《Self-supervised Learning on Graphs: Contrastive, Generative,or Predictive》第一部分:问题阐述
- HDU 4836 The Query on the Tree lca || 欧拉序列 || 动态树
- Codeforces Round #423 (Div. 2, rated, based on VK Cup Finals)