Postgres adapter - casting to integer type is not trivial

[danie...@gmail.com]

By defaut ecto postgrex adapter casts integer to bigint and this is not always what we want to do. I know that changing this is not possible at this point but maybe we could we add the aliases like int2, int4 and int8  that are supported by postgres and are explicit about the data type.
To cast to integer I need to create a custom type and use an atom that is not in the list - :int in this case that falls into Atom.to_string/1 logic

    Schema |> select([t], type(t.x, :integer)) |> plan() |> all()
    SELECT s0."x"::bigint FROM "schema" AS s0

    defmodule Int do def type, do: :int end
    Schema |> select([t], type(t.x, Int)) |> plan() |> all()
    SELECT s0."x"::int FROM "schema" AS s0

   Schema |> select([t], type(t.x, :int)) |> plan() |> all()
this fails with:
** (UndefinedFunctionError) function :int.type/0 is undefined (module :int is not available)

The problem is that by default postgres does not support all casts - for example boolean can be casted only to integer or text but not to bigint.

postgres@localhost:postgres> SELECT
     pg_catalog.format_type(castsource, NULL) AS source_type,
     pg_catalog.format_type(casttarget, NULL) AS target_type
 FROM pg_cast WHERE pg_catalog.format_type(castsource, NULL) = 'boolean'
source_type  target_type
-----------  -----------------
boolean      integer
boolean      text
boolean      character varying
boolean      character
SELECT 4

postgres@localhost:postgres> select true::integer;
+------+
| int4 |
|------|
| 1    |
+------+
SELECT 1
Time: 0.005s

postgres@localhost:postgres> select true::int4;
+------+
| int4 |
|------|
| 1    |
+------+
SELECT 1
Time: 0.008s

postgres@localhost:postgres> select true::text;
+------+
| text |
|------|
| true |
+------+
SELECT 1
Time: 0.006s

postgres@localhost:postgres> select true::bigint;
cannot cast type boolean to bigint
LINE 1: select true::bigint
                   ^
Time: 0.003s

[Greg Rychlewski]

I believe custom types are the way to go if wanting to use `type/2`. Otherwise fragment is recommended. This at least the intention stated in the docs: https://hexdocs.pm/ecto/Ecto.Query.API.html#type/2-type-vs-fragment

[danie...@gmail.com]

I get it - I should think about type when converting to elixir,
Does it mean that type is often misused because ecto is missing this puzzle piece and it was not added because type works in 99% of the cases.
Having cast_as(field,  :integer) would solve issues like that? Seems like a fairly popular usecase.

[Greg Rychlewski]

Maybe we can allow type/2 to take a string as a second argument and that will be passed  "as is". We do something similar for field/2. That might be more inline with our current conventions.

I would just want to get a thumbs up from Jose in case I am overlooking something.

[José Valim]

I think in this case a fragment is the way to go... given a string type will already be database specific anyway.

José Valim

https://dashbit.co/

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/elixir-ecto/e85c5cd0-f164-4c18-a5a8-3b90473f94e6n%40googlegroups.com.