Not sufficient documentation on using Postgres range types

Your Libraries & OS Mentoring Libraries
21

You can probably use a join if the data to be updated is stored in some table:

-- adapted from https://www.postgresqltutorial.com/postgresql-update-join/
UPDATE table_name
  SET validity = int4range(lower(validity), other_table.value, '[)')
  FROM other_table
  WHERE table_name.key = other_table.key;

or if you don’t have a table like that and all your data comes from the app, you might be able to use with_cte/2 in which you’d define the records to be updated and then in the main body you’d run a query similar to the one above:

WITH updates (key, value) AS (
  VALUES (1, 2), (3, 4)
)
UPDATE table_name
  SET validity = int4range(lower(validity), updates.value, '[)')
  FROM updates
  WHERE table_name.key = updates.key;

There are other approaches as well, but that’s not Ecto specific, to learn more I’d suggest lurking / asking questions on postgres-specific forums, people there would be more knowledgeable about this type of questions and would probably provide you with better answers.

1 Like