Integer Data Types

FUNCTION SUPPORT FOR INTs quite good, but many functions don't match PG behavior

Parquet Logical Types

DataFusion (release)

Postgres

1-byte signed integer

2-byte signed integer

select 32767::smallint + 1::smallint;

ERROR: smallint out of range

select 32768::smallint;

ERROR: smallint out of range

4-byte signed integer

ERROR: integer out of range

select 2147483648::int;

ERROR: integer out of range

8-byte signed integer

-9223372036854775808 to 9223372036854775807

select 9223372036854775807::bigint + 1::bigint;

ERROR: bigint out of range

select 9223372036854775808::bigint;

ERROR: bigint out of range

1-byte unsigned integer

select 255::tinyint unsigned + 1::tinyint unsigned;

select 256::tinyint unsigned;

2-byte unsigned integer

select 65535::smallint unsigned + 1::smallint unsigned;

select 65536::smallint unsigned;

4-byte unsigned integer

select 4294967295::int unsigned + 1::int unsigned;

select 4294967296::int unsigned;

8-byte unsigned integer

select power(2,64)::bigint unsigned;

select 18446744073709551615::bigint unsigned;

null for values even less than 2^64. some weird behavior here.

Floating Point Types

^^ overflow behavior for int types doesn't match PG

select 9::decimal(1,0) + 1::decimal(1,0);

ArrowError(InvalidArgumentError("10 is too large to store in a Decimal128 of precision 1. Max is 9"))

select 1000000000100000000010000000001000000000::decimal;

returns +---------------------------------------------------+
| Float64(1000000000100000000000000000000000000000) |
+---------------------------------------------------+
| 1701411834604692317316873037.1588410572 |
+---------------------------------------------------+

double or double precision

Arbitrary Precision Numerics

numeric(p,s) or decimal(p,s)

numeric(p,s) or decimal(p,s)

Decimal128(usize,usize)

Decimal256(usize,usize)

not much / no function support for binary types

99999999 9999999999 9999999999 9999999999

I believe we're missing any SQL functions to encode/decode binary data?

Character Types

char / character / varchar / character varying

same as Utf8 but with 64-bit offsets for very large values

timestamp [with time zone]

Timestamp(TimeUnit, Option<String>)

this issue says cast to time doesn't work, but I think it does already, at least for some values https://github.com/apache/arrow-datafusion/issues/193

should be able to cast to interval ('20 minutes'::interval) but can't

currently, can cast to these, but can't make a memory table with them

but not all the array syntax

FixedSizeList(Box<Field>, i32)

can create struct with syntax shown, but can't create memory tables from them. syntax also doesn't match PG similar concept.

Union(Vec<Field>, Vec<i8>, UnionMode)

Dictionary(Box<DataType>, Box<DataType>)

dictionary types are practically invisible to SQL users, right? if you have a TableProvider returning Dictionary types, SQL users will see only the value from the type: select dict_col from my_table .. if dict_col is <int16, utf8>, users will only see the uft8 value. correct?