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

A Methodical Approach to Event Listeners in React

JSON Web Tokens in Magento 2.4.4

My First Thoughts on React-Native

Top 10 features why Next JS is popular

An alternative solution for changing ReactJS style dynamically

Please welcome our 1st Time Speakers

features of node.js ?

Angular and Internet Explorer

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

How to master the CONNECT BY clause in Oracle to analyze hierarchical data

Installing Spark/PySpark on Mac-OS Monterey with Anaconda/MiniConda/VS Code

How much data fits into a Postgres jsonb field?

VanHack