Looking into adding support for more Postgres types (arrays, ltrees, citext...)

[auyer]

Hi! I'm looking into adding support for more types for the Postgres sources.
I see that citext was just added.
My main concern is with Arrays. Have anyone looked into it yet ?

[auyer]

I created a Draft PR for ltree: #382
Looking for help fixing it.

[wangxiaoying]

My main concern is with Arrays. Have anyone looked into it yet ?

We do support array with numerical types like integer and floats, but object arrays might require some efforts in the pandas dataframe writing part since we did some optimizations for python string writing (allocate the memory in batch instead of on demand). The implementation might not be straightforward, but you can take a look at our currently implementation of supporting string type and numerical array type at here and here. Please feel free to submit a PR on this if you are interested!

[auyer]

I am trying to implement this, but I must admit I've gotten nowhere yet. I have yet to fully understand this crate and how its modules interact.
So far I have accomplished VARCAHR[] and TEXT[] imports with type conversion in the postgres query array_to_json(test_varchararray) .... This results in a "json array" string like '{"element0", "element1"}'.

The behavior I'm trying to match is Sparks. It extracts both these types as list<element: string>.

[auyer]

Another topic: following up on sfackler/rust-postgres#960 it seams that the hex char prefix is by spec.
Is it viable to implement a different conversion method for ltree in binary mode ?

[wangxiaoying]

Yeah, the error is due to our incorrect type we put when creating the BinaryCopyOutIter.

In connectorx we have our own PostgresTypeSystem, which is not exactly a one-to-one mapping to postgres::types::Type. When the query result contains ltree column, currently:

1. We first get the column types in postgres::types::Type and directly convert it to PostgresTypeSystem at here following this mapping definition ("ltree" => PostgresTypeSystem::Text).
2. And when we invoke BinaryCopyOutIter::new, we convert PostgresTypeSystem back to postgres::types::Type at here following this mapping defination (PostgresTypeSystem::Text => postgres::types::Type::TEXT). Which lead to the problem since it should be Other(Other { name: "ltree", oid: 16905, kind: Simple, schema: "public" }).

I think a quick fix is to omit the conversion in step 2 by keeping the original postgres::types::Types we got in step 1 along with the converted PostgresTypeSystems, and pass to the PostgresSourcePartition (adding a member called pg_schema: Vec<postgres::types::Type>). And when we invoke BinaryCopyOutIter::new, we can directly use pg_schema instead of converting the types back.

Another solution is to add a LTREE type in our PostgresTypeSystem and mapping it back to its corresponding postgres::types::Type. But it seems like the ltree type is special postgres::types::Type::Other, which might require some extra effort (I haven't look deep in this).

Please let me know if you are interested in fixing this one. : )

[CircuitCM]

On this topic, any chance timestamp and date data types could be supported? This would also optimize queries to TimescaleDB.

[wangxiaoying]

Hi @CircuitCM , we have supported timestamp and date types in postgres. You can find the full list here.

[CircuitCM]

Thx, I must have used an incorrect partition key.