Feature Request: Project DuckDB as ETL tool: Query metadata should be more precise, not NUMBER but INTEGER etc

[wernerdaehn]

When you execute CREATE TABLE T2 as SELECT COL1, COL2 from T1 WITH NO DATA, the target table has precise data types, e.g. if T1.COL1 is an integer, T2.COL1 will also be.

When you execute the python code

duckdb.execute("SELECT COL1, COL2 from T1")
columns = duckdb.description

The columns dict does not show INTEGER but a generic NUMBER. I would like to see the same data type as with the CTAS - selecting an INTEGER returns an INTEGER.
I understand that this is not as simple as there can be formulas, it depends on the actual values etc. But for simple cases you can and I am simply asking to use the same logic as with the CTAS. If that means reading a couple of sample rows, so be it.

---

I am using DuckDB to implement a Data Integration tool.
https://github.com/rtdi/DuckTape

And here it hurts that I do not know the data types.

[paultiq]

Huh. I've never used duckdb.description.

Have you tried describe instead? That's what I usually use when trying to analyze this.

c = duckdb.execute("""
    create or replace table foo (col1 bigint, col2 integer, col3 varchar);
    describe select * from foo""")

print(c.df().to_markdown())

	column_name	column_type	null	key	default	extra
0	col1	BIGINT	YES
1	col2	INTEGER	YES
2	col3	VARCHAR	YES

Or, look at the data types of the result:

c = duckdb.sql("""
    create or replace table foo (col1 bigint, col2 integer, col3 varchar);
    select * from foo;
    """).arrow()
print(c.schema)

col1: int64
col2: int32
col3: string

[Tishj]

description is a part of DBAPI 2.0 and it's unhelpful at best.
We've tried to implement it according to the spec, but what they're asking for doesn't align with DuckDB.

Most notably the type_code asks for an object that identifies the columns type, so that it can be compared (https://peps.python.org/pep-0249/#type-objects-and-constructors)

We can't produce a type_code that singlehandedly identifies a type, because types can be arbitrarily nested

Hmm, while saying that, and reading the PEP again, I think I got confused by the vagueness and ambiguity of the spec?
There is no ABC or interface that a type object should follow, I think we can just return a DuckDBPyType object for the type_code ?

[Tishj]

Wrote up #43 that should fix this