Postgres

Source for PostgresSQL database.

URI

The supported URI format is:

scheme://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

For example:

postgresql://user:password@localhost:5432/postgres_database

Several schemes are supported, and others like sqlalchemy's are supported for compatability.

Valid schemes:

postgresql://
postgres://
postgres+psycopg2://
postgresql+psycopg://

In the case of sqlalchemy, the part on the right e.g. psycopg2 is ignored.

Types supported

Primitive datatypes

Postgres type	Native type	Arrow
`BOOL`	`bool`	`DataType::Boolean`
`INT2`	`16`	`DataType::Int16`
`INT4`	`i32`	`DataType::Int32`
`INT8`	`i64`	`DataType::Int64`
`FLOAT4`	`f32`	`DataType::Float32`
`FLOAT8`	`f64`	`DataType::Float64`
`CHAR`	`String`	`DataType::Utf8`
`BPCHAR`	`String`	`DataType::Utf8`
`TEXT`	`String`	`DataType::Utf8`
`VARCHAR`	`String`	`DataType::Utf8`
`UUID`	`uuid::Uuid`	`DataType::FixedSizeBinary(16)`
`BYTEA`	`Vec<u8>`	`DataType::Binary`
`NUMERIC`	`bigdecimal::BigDecimal`	`DataType::Decimal128`

Time datatypes

Postgres type	Native type	Arrow	Notes
`DATE`	`chrono::NaiveDate`	`DataType::Date32`	32 bit
`TIME`	`chrono::NaiveDateTime`	`DataType::Time64(TimeUnit::Microsecond)`	precision is microseconds
`TIMESTAMP`	`chrono::NaiveDateTime`	`DataType::Timestamp<TimeUnit::Microsecond>`	precision is microseconds
`TIMESTAMPTZ`	`chrono::DateTime<Utc>`	`DataType::Timestamp`

Geo-spatial datatypes

Postgres type	Supported	Native type	Arrow	Notes
`POINT`		`geo::Point`

Array datatypes

Postgres type	Native type	Arrow
`INT2_ARRAY`	`Vec<i16>`	`DataType::List<i16>`
`INT4_ARRAY`	`Vec<i32>`	`DataType::List<i32>`
`INT8_ARRAY`	`Vec<i64>`	`DataType::List<i64>`
`FLOAT4_ARRAY`	`Vec<f32>`	`DataType::List<f32>`
`FLOAT8_ARRAY`	`Vec<f64>`	`DataType::List<f64>`
`Array[TEXT]`	`Vec<String>`	`DataType::List`
`Array_UUID`	`Vec<uuid::Uuid>`	`DataType::List`
`Array[BOOL]`	`Vec<bool>`	`DataType::List`
`Array[DATE]`	`Vec<NaiveDate>`	`DataType::List`
`Array[TIMESTAMP]`	`Vec<NaiveDateTime>`	`DataType::List`
`Array[TIMESTAMPTZ]`	`Vec<DateTime<Utc>`	`DataType::List`
`Array[NUMERIC]`	`Vec<BigDecimal>`	`DataType::List`
`Array[BYTEA]`	`Vec<Vec<u8>>`	`DataType::List`

Example

from conecta import read_sql

t = read_sql(
    conn="postgres://postgres:postgres@localhost:5432",
    queries=['select * from lineitem'],
    partition_on='l_orderkey',
    partition_num=4,
)