Postgres inserts with ON CONSTRAINT DO UPSET, and FOR loops to avoid violating constraints

  • does not allow to read a value from a subquery for <expression>
  • does not allow to perform multiple times in case the constraint is violated more than once.
DO $$ DECLARE
yourRecord RECORD;
nextAvailableValue bigint;
BEGIN
FOR yourRecord IN (SELECT * from yourTable) LOOP
SELECT MAX(col1)+1 INTO nextAvailableValue
FROM table_with_values
WHERE col2 = yourRecord.id;

INSERT INTO other_table(
id,
col3
) VALUES (
yourRecord.id,
nextAvailableValue
);
END LOOP;
END $$;

--

--

--

Software Engineer @ London [https://www.linkedin.com/in/elvisciotti]

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Create Amazing GIS Data-Driven Maps Using ArcGIS API for JavaScript

Map of US showing population living in poverty by county

Install and Create Vue.js project using CLI (Angular to Vue.js-Part )

Thank You 🙏

Create an infinite scrolling animation with CSS 💥

Higher Order Components

How to deploy React App on Firebase

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Elvis Ciotti

Elvis Ciotti

Software Engineer @ London [https://www.linkedin.com/in/elvisciotti]

More from Medium

Postgresql User Management in Nutshell

Distributed SQL Tips and Tricks — December 16th, 2021

Trigger a build/release pipeline from the different projects in ADO using REST API

projects

Restore MySQL InnoDB Cluster from mysqlbackup(MySql Enterprise Backup)