JuliaDatabases / LibPQ.jl

A Julia wrapper for libpq
MIT License
217 stars 51 forks source link

Support UUIDs with binary_format=true #260

Open robsmith11 opened 1 year ago

robsmith11 commented 1 year ago

It would be great if UUIDs were interpreted using Julia's UUID type.

This works, but probably not the mot efficient way:

postgres=# create table t (x uuid);
CREATE TABLE
postgres=# insert into t values(uuid_in(md5(random()::text || random()::text)::cstring));
INSERT 0 1
postgres=# insert into t values(uuid_in(md5(random()::text || random()::text)::cstring));
INSERT 0 1
postgres=# insert into t values(uuid_in(md5(random()::text || random()::text)::cstring));
INSERT 0 1
postgres=# select * from t;
                  x
--------------------------------------
 b8445ef2-ed8e-b4dd-c5da-c5097155a45c
 ed7676b9-f78f-c2f1-84d5-a6267485cd4a
 77ca6bf5-7621-bc8a-034c-1a853a97b516
julia> t = LibPQ.execute(c, "select * from t", binary_format=true, not_null=true) |> Tables.columntable
(x = ["\xb8D^\xf2펴\xdd\xc5\xda\xc5\tqU\xa4\\", "\xedvv\xb9\xf7\x8f\xc2\xf1\x84զ&t\x85\xcdJ", "w\xcak\xf5v!\xbc\x8a\x03L\x1a\x85:\x97\xb5\x16"],)

julia> [reinterpret(UUIDs.UUID, reverse(Vector{UInt8}(x)))[1] for x in t.x]
3-element Vector{Base.UUID}:
 UUID("b8445ef2-ed8e-b4dd-c5da-c5097155a45c")
 UUID("ed7676b9-f78f-c2f1-84d5-a6267485cd4a")
 UUID("77ca6bf5-7621-bc8a-034c-1a853a97b516")
iamed2 commented 1 year ago

Thanks for this example, that's a great reference for implementation!