search

paradedb / pg_analytics

DuckDB-powered analytics for Postgres
https://paradedb.com
PostgreSQL License
197 stars 13 forks source link

JSON not supported #50

Closed BigBerny closed 1 month ago

BigBerny commented 3 months ago

What happens?

When running this query I get an error:

SELECT "varchar_column"::JSON
FROM predictions

Error: Query 1 ERROR at Line 1: : ERROR: Column varchar_column has Arrow data type Utf8 but is mapped to the BuiltIn(JSONOID) type in Postgres, which are incompatible. If you believe this conversion should be supported, please submit a request at https://github.com/paradedb/paradedb/issues.

To Reproduce

OS:

Ubuntu(?) with PostgreSQL 16.3

ParadeDB Version:

0.8.3

Full Name:

Janis

Affiliation:

Typewise

What is the latest build you tested with? If possible, we recommend testing by compiling the latest dev branch.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include the code required to reproduce the issue?

Did you include all relevant configurations (e.g., CPU architecture, PostgreSQL version, Linux distribution) to reproduce the issue?

To Reproduce

Try to cast to JSON, either in a query or when creating the foreign table.

OS:

Ubuntu(?) with PostgreSQL 16.3

ParadeDB Version:

0.8.3

Full Name:

Janis

Affiliation:

Typewise

What is the latest build you tested with? If possible, we recommend testing by compiling the latest dev branch.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include the code required to reproduce the issue?

Did you include all relevant configurations (e.g., CPU architecture, PostgreSQL version, Linux distribution) to reproduce the issue?

BigBerny commented 3 months ago

Casting to JSON and JSONB (from varchar) both doesn't work.

Query: SELECT "ABTestingNameToGroup"::JSON FROM predictions Query 1 ERROR at Line 1: : ERROR: Column ABTestingNameToGroup has Arrow data type Utf8 but is mapped to the BuiltIn(JSONOID) type in Postgres, which are incompatible. If you believe this conversion should be supported, please submit a request at https://github.com/paradedb/paradedb/issues.

Query: SELECT "ABTestingNameToGroup"::JSONB FROM predictions WARNING: This query was not fully pushed down to DuckDB because DuckDB returned an error. Query times may be impacted. If you would like to see this query pushed down, please submit a request to https://github.com/paradedb/paradedb/issues with the following context: Catalog Error: Type with name JSONB does not exist! Did you mean "JSON"?

Also specifying the type JSONB (or JSON) when creating the table doesn't work.

philippemnoel commented 3 months ago

Casting to JSON and JSONB (from varchar) both doesn't work.

Query: SELECT "ABTestingNameToGroup"::JSON FROM predictions Query 1 ERROR at Line 1: : ERROR: Column ABTestingNameToGroup has Arrow data type Utf8 but is mapped to the BuiltIn(JSONOID) type in Postgres, which are incompatible. If you believe this conversion should be supported, please submit a request at https://github.com/paradedb/paradedb/issues.

Query: SELECT "ABTestingNameToGroup"::JSONB FROM predictions WARNING: This query was not fully pushed down to DuckDB because DuckDB returned an error. Query times may be impacted. If you would like to see this query pushed down, please submit a request to https://github.com/paradedb/paradedb/issues with the following context: Catalog Error: Type with name JSONB does not exist! Did you mean "JSON"?

Also specifying the type JSONB (or JSON) when creating the table doesn't work.

Thanks for reporting, we'll get this fixed ASAP.