Not sufficient documentation on using Postgres range types

Your Libraries & OS Mentoring Libraries
1

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 []

2

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.

3

The table I have is
CREATE TABLE IF NOT EXISTS public.default_name
(
subject character(15) COLLATE pg_catalog.“default” NOT NULL,
validity int4range,
object character(15) COLLATE pg_catalog.“default”,
CONSTRAINT default_name_subject_validity_excl EXCLUDE USING gist (
subject WITH =,
validity WITH &&)

)

The samples I want to execute using SQL are

insert into default_name values (‘Sai’, ‘[0,)’::int4range, ‘Alluru’);

followed by an update

UPDATE default_name SET
validity = int4range(lower(validity), 10, ‘[)’)
where subject = ‘Sai’ and validity @> 10

This works fine in Postgres unable figure out how to get this working on Using Repo.SQL

4

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.17.4.

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"}])
1 Like
5

I am not using Ecto Schema I need tables to be created a run time, so want to just use plain SQL with prepared statements. By the I am very new to Elixir and the tools.

6

I’m not suggesting Ecto Schema.

7

Ok how about the update statement ?

8

You can use Repo.update_all.

9

I will try, Is it the standard bahavior that errors are not properly reported.

10

Insert worked, thanks a lot I am assuming I can pass a list of objects for batch insert.Which I will try.

11

For update I just want to update the upper bound, I have no idea about the lower bound at that time, so do I not specify the lower in the Range and only specify upper., Would that work ?

12

For update I tried the following it works, but there is no way to not modify the lower bound

validityu = %Postgrex.Range{upper: 100, upper_inclusive: false}

    # from(p in table_name, where: p.subject == ^subject , update: [set: [validity: ^validityu]])
    # |> Repo.update_all([])

In my original queryI have

UPDATE default_name SET
validity = int4range(lower(validity), 0, ‘[)’)
where subject = ‘Sai’ and validity @> 0; which retains the current lower. Looks like this is not possible with Repo.update_all()

13

Is it the standard bahavior that errors are not properly reported.

No, where are the errors not reported properly?

but there is no way to not modify the lower bound

You can use fragment.

Here’s how I’d translate your query:

"default_name"
|> where(subject: "Sai")
|> where([p], fragment("? @> 0", p.validity))
|> update([p], set: [validity: fragment("int4range(lower(?), 0, '[)')", p.validity)])
|> Repo.update_all([])
14

I tired the following and it worked
from(p in table_name, where: p.subject == ^subject and fragment(“validity @> ?::integer”, ^scn),
update: [set: [validity: fragment(“int4range(lower(validity), ?, ‘[)’)”, ^scn)]])
|> Repo.update_all([])

However I am not sure how I can convert the above into batching, it seems like I have to do this for one record at a time, where as the insert below I can provide multiple records in the list and they will be applied as part of a batched prepared statement. Any suggestions I can convert this into a prepared statement batch for update.
Repo.insert_all(table_name, [record, record1])

15

When I said that not enough ERROR details, if you see the above it says QUERY ERROR but no other details.

16

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.

1 Like
17

You can express batch update with update_all the same way you’d express it with raw sql. If you post the SQL query you want to execute, I can help you translate it into ecto functions/macros.

18

This is the original query with {} are the positional parameters

UPDATE name_table SET
validity = int4range(lower(validity),{}, '[)') value = 'x'
where key = {} and validity @> {};

This is the query I came up with

from(p in table_name, where: p.key == ^key and fragment("validity @> ?::integer", ^scn),
      update: [set: 
        [validity: fragment("int4range(lower(validity), ?, '[)')", ^scn)]
        [value:, ^value]
        ])
      |> Repo.update_all([])

Please suggest.

19

This is the corrected update_all

      from(p in table_name, where: p.key == ^key and fragment("validity @> ?::integer", ^scn),
      update:
        [set:
          [validity: fragment("int4range(lower(validity), ?, '[)')", ^scn), value: ^value]
        ]
      )
20

@ruslandoga would you able let me know how I can use this query with batching ?