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 1750 20

2021-12-02 19:16:08 UTC

First 10 of 20 Posts

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

saialluru

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

Post #2

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

saialluru

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.

Post #4

ruslandoga

I’m not suggesting Ecto Schema.

Post #5

saialluru

Ok how about the update statement ?

Post #6

ruslandoga

You can use Repo.update_all.

Post #7

saialluru

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

Post #8

saialluru

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

Post #9

saialluru

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 ?

Post #10

Load more posts