TIL: Non standard postgres types in livebook

Livebook has a new, nice feature that allows you to connect to a database. It works with basic data types.

But I found a problem when the data type is not supported by postgrex.

In this case, you can either have to write a custom postgrex extension like the example here. In postgrex GitHub repo, there are many examples for common data types, but XML is missing there.

You can also cast your field to a supported data type:

SELECT demographics::text from person.person   

To play with this example, you can install the example Adventureworks database provided by Microsoft. I made an elixir converter that converts some of the files to a format accepted by PostgreSQL, and also updated the previous version to properly name the columns with underscores.

Comments

[Thibaut Barrère]

I met the same situation and ultimately I converted the smart cell for the connection into code, added the types: DB.PostgrexTypes, and earlier defined them with

Postgrex.Types.define(DB.PostgrexTypes, [Geo.PostGIS.Extension], [])

Thanks for your post which put me on track!