How to Store bit varying using Ecto?

Questions / Help
1

Hi everyone,

I have a bit string like <<1, 0, 0, 1>> and I would like to store it in postgresql using bit varying datatype.

Currently, ecto doesn’t have a bit type in schema and I have used binary, so what happened in the DB is instead of storing it as 1001, the DB represents each integer in 8bits like 00000001 00000000 00000000 00000001

I’m confused and is there a way to use ecto to store the bitstring as it is in bit varying data type of postgres?

Thanks.

2 Likes
2

Without access to a computer, I’d venture to say but cannot validate that creating a custom type would be the way to solve this.

3

Thanks @christhekeele, but how do I represent bits in the Custom Type? All bit strings are considered as byte array by Postgresql

4

4 bytes, as elixir assumes a size of 8 unless otherwise specified.

4 bytes.

Looks correct for me.

But perhaps you meant this:

iex(1)> << 1::size(1), 0::size(1), 0::size(1), 1::size(1)>>
<<9::size(4)>>
3 Likes
5

Thanks @NobbZ, Maybe, I didn’t give the full situation properly, Let me get this clear,

I have a bit string like b = <<23, 102, 34>>
I’m want to store it as bits in Postgres, so I’m using the next step:
a = for <<c::1 <- b>>, do: <<a::1>>
gives me this,

[
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<1::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<0::size(1)>>,
  <<1::size(1)>>,
  <<0::size(1)>>
]

I want to store it as bits in the Postgres and What is the correct way to save them as 1-bit bitstring rather than saving it as list of 1-bit binary?

The problem here is I’m trying to understand if I can send bit data as it is through ecto get it stored in Postgres as bit varying

Thanks in advance for explaining the size operation.

1 Like
6

Thanks @NobbZ, I just learned now by experimenting, that I don’t have to play around bits in Elixir, just send the bitstring straight to DB and it will automatically represent it in bits and gives me my expected result.

Thanks all, for the help.

6 Likes
7

Hi @pmangalakader

I’m trying the same but cannot find the correct type to pass to the field definition on the schema model.
Any idea what might do the trick?

8

@Batou99 As far as I can remember, I used binary or string as my field model in the schema and the underlying migration represented the column as bit_varying.

So, if you send the binary to Schema and then to DB, the DB automatically handled the encoding of binary to bit_varying.

Hope, this helps you!

1 Like
9

In case someone else ends up having the same issue.
@pmangalakader is right you just need to use :string as your field type on ecto.

The trick though is not using bitstrings on the elixir side at all. Just use strings of 1s and 0s (e.g "100101")
When persisting them on postgres (or cockroach) they get automatically casted as varbit.

Thanks for your reply @pmangalakader you put me on the right track.

10

Just adding some more info about this.
Using string on the ecto side works but not quite.
On the postgres side it stores each bit as a byte. E.g you store “1001” on the elixir side and this translates to <“1”, “0”, “0” , “1”> on the postgres side, wasting a lot of space (you use 4x8 bits instead of 4)
Also if you want do end up doing bitwise operations on the elixir side you need to convert the strings you get from the DB (“1001”) into a varbit <1:1, 0:1, 0:1 1:1> which also wastes some processing.
So either I did not find the correct way to do this or there’s something amiss on postgrex

11

I did a quick experiment and think it works as you want if:

  1. Define your field in the migration as:
add :bits,            :"bit varying"
  1. In your schema as binary:
field :bits,            :binary
  1. Store binary data:
iex>  Repo.insert %MyTable{bits: <<1,1,1,2,2,2,3,3,3>> }                         

18:08:43.172 [debug] QUERY OK db=3.0ms decode=0.7ms queue=0.8ms idle=1871.8ms
INSERT INTO "my_table" ("bits","inserted_at","updated_at") VALUES ($1,$2,$3) [<<1, 1, 1, 2, 2, 2, 3, 3, 3>>, ~N[2021-09-09 10:08:43], ~N[2021-09-09 10:08:43]]
{:ok,
 %MyTable{
   __meta__: #Ecto.Schema.Metadata<:loaded, "my_table">,
   bits: <<1, 1, 1, 2, 2, 2, 3, 3, 3>>,
   inserted_at: ~N[2021-09-09 10:08:43],
   updated_at: ~N[2021-09-09 10:08:43]
 }}
3 Likes
12

Thanks for looking into it @kip
Your example works because <<1,1,1,2,2,2,3,3,3>> it’s a proper binary (size is a multiple of 8) if you use a non binary varbit <<1::1, 0::1, 1::1>> it just dies.

I managed to store varbits on the DB side bypassing ecto wrapping (Using straight SQL insert from elixir) but as soon as you try to wrap the result into a struct (e.g Data |> Repo.all ) it crashes saying that the bits field is not a binary (which makes sense)

I think the missing piece here is ecto support of varbit at schema level, it is supported at data level via postgrex but if the schema does not recognise the type it just forces you to use SQL all around.

1 Like
13

Yes, I was too fast on the that bogus example. I haven’t dived into Ecto for a bit (and it makes my head spin every time I do) but I’ll take this as a challenge to dig into.

14

I have created an ecto custom type that supports the Postgresql bit varying data type. I think this is now operating as expected for bitstring Elixir types serialised to a Postgres bit varying type.

How to use

  1. Add the Bitstring type module in this gist to your project
  2. Adjust your schema to use the type Bitstring as the type for your bit string field
  3. Ensure your migration uses the type :"bit varying" for the appropriate column

Example

iex> Repo.insert %MyTable{bits: <<1::1, 0::1, 1::1>>}

12:29:20.620 [debug] QUERY OK db=1.1ms queue=0.8ms idle=1861.8ms
INSERT INTO "my_table" ("bits","inserted_at","updated_at") VALUES ($1,$2,$3) [<<5::size(3)>>, ~N[2021-09-23 04:29:20], ~N[2021-09-23 04:29:20]]
{:ok,
 %MyTable{
   __meta__: #Ecto.Schema.Metadata<:loaded, "my_table">,
   bits: <<5::size(3)>>,
   inserted_at: ~N[2021-09-23 04:29:20],
   updated_at: ~N[2021-09-23 04:29:20]
 }}
iex> Repo.all MyTable                            

12:29:25.479 [debug] QUERY OK source="my_table" db=0.3ms idle=1723.0ms
SELECT o0."bits", o0."inserted_at", o0."updated_at" FROM "my_table" AS o0 []
[
  %MyTable{
    __meta__: #Ecto.Schema.Metadata<:loaded, "my_table">,
    bits: <<5::size(3)>>,
    inserted_at: ~N[2021-09-23 04:29:20], 
    updated_at: ~N[2021-09-23 04:29:20]
  }
]
6 Likes
15

Works like a charm @kip!
Thanks for looking into it.

1 Like