The numeric type is not supported as a where clause

[Leo-XM-Zeng]

What happens?

The numeric type is not supported as a where clause

[postgres@halo-centos8 16]$ psql duckdb
psql (16.4)
Type "help" for help.

duckdb=# create table numeric_test(a numeric);
CREATE TABLE
duckdb=# insert into numeric_test values(0.25);
INSERT 0 1
duckdb=# select * from numeric_test;
  a   
------
 0.25
(1 row)

duckdb=# select * from numeric_test where a >= 0.25;
2024-10-23 23:19:16.147 CST [32792] ERROR:  XX000: (PGDuckDB/ExecuteQuery) Invalid type Error: (DuckDB/FilterOperationSwitch) Unsupported duckdb type: 1700
2024-10-23 23:19:16.147 CST [32792] LOCATION:  DuckDBFunctionGuard, pgduckdb_utils.hpp:99
2024-10-23 23:19:16.147 CST [32792] STATEMENT:  select * from numeric_test where a >= 0.25;
ERROR:  (PGDuckDB/ExecuteQuery) Invalid type Error: (DuckDB/FilterOperationSwitch) Unsupported duckdb type: 1700
duckdb=# \dx
                  List of installed extensions
   Name    | Version |   Schema   |         Description          
-----------+---------+------------+------------------------------
 pg_duckdb | 0.0.1   | public     | DuckDB Embedded in Po

To Reproduce

create table numeric_test(a numeric);
insert into numeric_test values(0.25);
select * from numeric_test;
select * from numeric_test where a >= 0.25; -- error

OS:

centos8

pg_duckdb Version:

master

Postgres Version:

REL_16_STABLE

Hardware:

No response

Full Name:

Man Zeng

Affiliation:

Halo

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

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

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

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

• Yes, I have

[Leo-XM-Zeng]

Interestingly, the results are different for different precision numeric types

duckdb=# create table numeric_test2(empno numeric(4,0), sal numeric(7,2) ); 
CREATE TABLE
duckdb=# insert into numeric_test2 select empno,sal from hi_emp_temp limit 1; 
INSERT 0 1
duckdb=# select * from numeric_test2;
 empno |  sal   
-------+--------
  7369 | 800.00
(1 row)

duckdb=# select count(*) from numeric_test2 where empno >= 0.25;
 count 
-------
     1
(1 row)

duckdb=# select count(*) from numeric_test2 where sal >= 0.25;
2024-10-23 23:36:13.095 CST [32922] ERROR:  XX000: (PGDuckDB/ExecuteQuery) Invalid type Error: (DuckDB/FilterOperationSwitch) Unsupported duckdb type: 1700
2024-10-23 23:36:13.095 CST [32922] LOCATION:  DuckDBFunctionGuard, pgduckdb_utils.hpp:99
2024-10-23 23:36:13.095 CST [32922] STATEMENT:  select count(*) from numeric_test2 where sal >= 0.25;
ERROR:  (PGDuckDB/ExecuteQuery) Invalid type Error: (DuckDB/FilterOperationSwitch) Unsupported duckdb type: 1700

To Reproduce

create table numeric_test2(empno numeric(4,0), sal numeric(7,2)); 
insert into numeric_test2 values(7369,800.00);
select * from numeric_test2;
select count(*) from numeric_test2 where empno >= 0.25;
select count(*) from numeric_test2 where sal >= 0.25;  -- error

[Leo-XM-Zeng]

#286

[mkaruza]

Closing as numeric filtering still needs to be developed (#286)