saialluru

Not sufficient documentation on using Postgres range types

Using raw SQL insert and update into range types does not work

updateSql = “UPDATE " <> type.namespace <> “_” <> type.predicate <> " SET
validity = int4range(lower(validity)::integer, $1::integer, ‘[)’)
where subject = $2 and validity @> $3::integer”

insetSql = "insert into range_table values ($1, '[$2::integer,)'::int4range, $3);"

Repo.transaction fn ->
    SQL.query(Repo, updateSql, [intvalue, string value, intvalue])
    SQL.query(Repo, insetSql, [stringvalue, intvalue, stringvalue], [])
end

The output does not clearly say what the error is
iex(23)> Postgres.store_sentence(sainame)
[debug] QUERY OK db=0.2ms idle=1404.9ms
begin
[debug] QUERY ERROR db=0.0ms
insert into default_name values ($1, ‘[$2::integer,)’::int4range, $3); [“Sai”, 0, “Alluru”]
[debug] QUERY OK db=0.2ms
rollback

20 comments

#ecto #library

0 1748 20

2021-12-02 19:16:08 UTC

First Post!

ruslandoga

Can you post the query you want to execute here? From what I see, you have a typo in the range definition, [$2::integer,)::int4range: [$2::integer,) is not a valid range.

Off-topic, I’d suggest not using raw sql in Repo.query, and instead going with Repo.insert_all. What you are trying to do can be done with insert_all in a safer and more idiomatic way.

Post #1

Most Liked

ruslandoga

Note that you posted [$2::integer,)::int4range in OP, [$2::integer,) is not a valid range.

In insert_all you can use Postgrex.Range — Postgrex v0.22.2.

Your raw SQL would be roughly equivalent to

validity = %Postgrex.Range{lower: 0, upper: :unbound, upper_inclusive: false}
Repo.insert_all("default_name", [%{subject: "Sai", validity: validity, object: "Alluru"}])

Post #3

ruslandoga

Check the response from Repo.query, it would contain the error reason. The logs you posted only show the executed queries, how long it took, and their status, they don’t show responses as it would be noisy.

Post #15

ruslandoga

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.

Post #20