Why postgres not casting from string

Questions / Help
1

I have this simple query, which raises an error for p1.id when I pass an id as a string.

                 Ecto.Query<from e0 in counter, join: p1 in "tents",
                  on: e0.tent_id == p1.id, where: e0.id == ^"3034" and ^true,
                  where: p1.insta_id == ^6136 and (p1.id == ^"3963" and ^true)>

It raises this error:

          Postgrex expected an integer in -9223372036854775808..9223372036854775807, got "3963"

It works fine for this query:

  Ecto.Query<from p0 in tent, join: e1 in assoc(p0, :counters),
  where: p0.insta_id == ^6133 and (p0.id == ^"3961" and ^true),
  where: e1.id == ^"3032" and ^true, limit: ^100, offset: ^0,
 preload: [[:counters]]

I can solve this error by converting String to integer, but I am wondering what’s special in this query that makes the postgres raises an error without casting it as an integer.

Thanks

2

Because postgres is not JavaScript. Columns have a type for a reason.

Implicit casting/coersion of values was and is a major source of bugs in a lot of languages.

3 Likes
3

This doesn’t answer his question. He pointed out a scenario where casting DOES happen, and is trying to explain why it doesn’t happen in a different scenario.

Perhaps there is something particular to the way primary key columns are defined?

4

For me those two queries do not have really something in common…

For the first query the error message complains about "3963", which is compared to p1.id. Though this field does not exist in the second query.

So without knowing anything about the schemas involved, I’d say that there does no conversion happens.

In the second query, is tent actually the same as "tents" from the first query or are they different?

5

Both have strings that contain textual representations of numbers, they are casted in the second case, and not in the first case.

Postgres isn’t doing the casting in the second query, Ecto is. In the first query, you’re using "tents" as the source of the query, which means you’re doing a schemaless query. Without an Ecto schema, ecto doesn’t know what the database expects for each column, and can’t try to help convert values.

In the second query it looks like you’re using a schema because it says in tent, and if you’re using a schema ecto can make an effort to cast values for you.

3 Likes