DuckDb csv data load - How to avoid appending .0 to integer values while loading data from csv into DuckDb table column having 'numeric' data type?

Question

I have csv file which I want to load into DuckDb table. CSV file has two columns col1 & col2 having both integer values. I want to load this csv file data into DuckDb table - temp which have same columns as col1(dtype int) & col2(dtype numeric).

CSV file like below

col1,col2
10,20
11,21

I used COPY command to load data.

conn = duckdb.connect()
cur = conn.cursor()
query1 = "create schema public;CREATE TABLE IF NOT EXISTS public.temp(col1 integer,col2 numeric)"
query2 = "COPY public.temp FROM \'/Users/data.csv\' WITH (FORMAT csv, HEADER true)"
cur.execute(query1)
cur.execute(query2)
conn.commit()

Data is loaded in DuckDb table like below

col1,col2
10,20.0
11,21.0

So data loaded correctly for int column but appended .0 for numeric column. This doesn't happen with Postgresql db table.

Why is this happening ? Any solution for this ? Thanks in advance!!!

Answer 1

Re: Postgres, it depends:

select 1::numeric(4,2); 1.0

vs

select 1::numeric; 1.

This is due to Postgres Numeric:

(The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.)

In the DuckDB case Duckdb Numeric:

The default WIDTH and SCALE is DECIMAL(18, 3), if none are specified

So in DuckDB CLI:

select 1::numeric; 1.000

vs

select 1::numeric(4,0); 1

Declare your field with a 0 scale, though if you are doing that just make the field an integer.