enigmare/v2-crawler

1{"id":"stack-72883083","source":"stackoverflow","questionId":72883083,"title":"Create an auto incrementing primary key in DuckDB","tags":["sql","duckdb"],"text":"Title: Create an auto incrementing primary key in DuckDB\nTags: sql, duckdb\nSource: Stack Overflow\n\nQuestion:\nMany database engines support auto-incrementing primary keys, and I would like to use this approach in my new DuckDB approach, but I can't figure out how to set it up. For example, in MySQL:\n\n```\nCREATE TABLE Persons (\n Personid int NOT NULL AUTO_INCREMENT,\n LastName varchar(255) NOT NULL,\n FirstName varchar(255),\n Age int,\n PRIMARY KEY (Personid)\n);\n```\n\n========================================\n\nCode:\n```text\nCREATE TABLE Persons (\n    Personid int NOT NULL AUTO_INCREMENT,\n    LastName varchar(255) NOT NULL,\n    FirstName varchar(255),\n    Age int,\n    PRIMARY KEY (Personid)\n);\n```\n\n```text\nCREATE TABLE Persons (\n    Personid integer primary key,\n    LastName varchar(255) not null,\n    FirstName varchar(255),\n    Age integer\n);\n```\n\n```text\nCREATE SEQUENCE seq_personid START 1;\n```\n\n```text\nINSERT INTO Persons VALUES (nextval('seq_personid'), 'Doe', 'John', 99);\n```\n\n========================================\n\nComments:\n- Did you try google, it seems you have to use sequences because there is no auto increment... duckdb.org/docs/sql/statements/create_sequence\n- Yes, I came across that page, but can't see how to impement it exactly to fill a primary key column. Do you have any suggestions?\n- yes :), create a table, then create the sequence. Then while inserting just use \"insert into table persons (personid,...) values (nextval('name_of_your_sequence'), ...)\"\n- see my answer if this is what you wanted\n- You can use the default too, e.g. `Personid integer primary key default nextval('seq_personid')`, which is closer to the auto increment/serial behaviour of PostgreSQL, MySQL, etc.\n- Yes. For a fuller answer: Alternatively, try this: 1. Create a sequence: `sql CREATE SEQUENCE seq_personid START 1;` 2. Create a table: `sql CREATE TABLE Persons ( Personid INTEGER PRIMARY KEY DEFAULT NEXTVAL('Publisher_Id_Seq'), LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Age INTEGER );` 3. Insert some data: `sql INSERT INTO Persons VALUES ('Doe', 'John', 99);`\n- @SualehFatehi You create a sequence named seq_personid in step 1 and then refers to a sequence named Publisher_Id_Seq in step 2 which probably won't work.\n- INSERT INTO Persons SELECT nextval('seq_personid') ... gives unpredictable results. The incremented id doesn't the order of the inserts in my experience (tried using ORDER BY but no luck). Better to use MartinTournoij and @SualehFatehi answer","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":58,"estimatedTokens":624}}2{"id":"stack-77066397","source":"stackoverflow","questionId":77066397,"title":"DuckDB - What's the difference between .sql and .execute function?","tags":["python","duckdb"],"text":"Title: DuckDB - What's the difference between .sql and .execute function?\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nI am a newbie using DuckDb library in python and while going through docs I stumbled upon 2 functions to execute sql instructions, namely execute() and sql().\n\nWhat's the difference between the 2? I am really scratching my head with this.\n\n========================================\n\nCode:\n```text\nsql\n```\n\n```text\nexecute\n```\n\n```text\nDuckDBPyRelation\n```\n\n```text\nduckdb.sql(query: str, alias: str = 'query_relation', connection: duckdb.DuckDBPyConnection = None) → duckdb.DuckDBPyRelation\n```\n\n```text\nDuckDBPyConnection\n```\n\n```text\nduckdb.execute(query: str, parameters: object = None, multiple_parameter_sets: bool = False, connection: duckdb.DuckDBPyConnection = None) → duckdb.DuckDBPyConnection\n```\n\n========================================\n\nComments:\n- Hi @Keraion, I'm sorry to reply to your comment years later, but I have a question. If I'm looping over files and for each one, doing a `COPY (SELECT * FROM {filepath}) TO {output}` in duckdb, is `conn.sql()` or `conn.execute()` better for this task? Should a rule of thumb be that if nothing is returned, to use .execute() and if it is, use .sql()?","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":6,"totalLines":40,"estimatedTokens":310}}3{"id":"stack-66027598","source":"stackoverflow","questionId":66027598,"title":"how to vacuum (reduce file size) on duckdb","tags":["duckdb"],"text":"Title: how to vacuum (reduce file size) on duckdb\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nI am testing duckdb database for analytics and I must say is very fast. The issue is the database file is growing and growing but I need to make it small to it.\n\nIn sqlite I recall to use the VACUUM commadn, but here same command is doing nothing. Size is the same.\n\nhow to reduce file size for duckdb database?\n\n========================================\n\nTop Answer:\nAs of DuckDB version 0.10.0:\n\nVACUUM Statement\n\nThe VACUUM statement alone does nothing and is at present provided for\nPostgreSQL-compatibility. The VACUUM ANALYZE statement recomputes\ntable statistics if they have become stale due to table updates or\ndeletions.\n\n========================================\n\nComments:\n- Did you ever figure this out? I'm looking for something similar...\n- No clue. I exported the complete SQL dump code and then create all database using a different filename.","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":0,"totalLines":28,"estimatedTokens":239}}4{"id":"stack-77364053","source":"stackoverflow","questionId":77364053,"title":"can I open duckdb file in read-only mode while other process writing to the the same database file?","tags":["duckdb"],"text":"Title: can I open duckdb file in read-only mode while other process writing to the the same database file?\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nI am running simple script which creates db file, creates table and doing inserts into that table (see below).\n\nWhile the script is running, I am trying to use duckdb cli tool to connect as readonly to the same db file.\n\n```\nduckdb -readonly db.db\n```\n\nIt gave me the error: `Error: unable to open database \"db.db\": IO Error: Could not set lock on file \"db.db\": Resource temporarily unavailable`\n\nIs it a bug or feature of duckdb? Can anyone explain the meaning of 'read-only' from duckdb point of view?\n\n```\nimport duckdb, time\nimport numpy as np\n\nif __name__ == \"__main__\":\n conn = duckdb.connect(\"db.db\", read_only = False)\n conn.sql(\"create table if not exists quotes (ts float, bid float, ask float)\")\n ts = 0\n mq = 20000.0\n ts = 0\n while True:\n mq += np.random.rand()\n conn.execute(\"insert into quotes values(?, ?, ?)\", [ts, mq - 100, mq + 100])\n ts += 1\n time.sleep(1)\n\n conn.close()\n```\n\n========================================\n\nCode:\n```text\nduckdb -readonly db.db\n```\n\n```text\nimport duckdb, time\nimport numpy as np\n\nif __name__ == \"__main__\":\n    conn = duckdb.connect(\"db.db\", read_only = False)\n    conn.sql(\"create table if not exists quotes (ts float, bid float, ask float)\")\n    ts = 0\n    mq = 20000.0\n    ts = 0\n    while True:\n        mq += np.random.rand()\n        conn.execute(\"insert into quotes values(?, ?, ?)\", [ts, mq - 100, mq + 100])\n        ts += 1\n        time.sleep(1)\n\n    conn.close()\n```\n\n```text\nError: unable to open database \"db.db\": IO Error: Could not set lock on file \"db.db\": Resource temporarily unavailable\n```\n\n========================================\n\nComments:\n- thanks for quick resolution on this one","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":3,"totalLines":70,"estimatedTokens":451}}5{"id":"stack-77680599","source":"stackoverflow","questionId":77680599,"title":"How to declare local variable in DuckDB","tags":["duckdb"],"text":"Title: How to declare local variable in DuckDB\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nIn SQL Server, I can declare a local variable and use it like so:\n\n```\nDECLARE @x Int = 7\nSELECT @x * 2;\n```\n\nResult: 14\n\nHow can I declare a **local** variable and use it in my DuckDB SQL script?\n\nNote: this is not the same as SQL *parameters*.\n\n========================================\n\nTop Answer:\nThe `DECLARE` keyword is not supported in DuckDB (and many other SQL systems).\n\nYou can use macros to define constants:\n\n```\nCREATE TEMP MACRO x() AS (SELECT 7);\n```\n\nThen, you can use them as follows:\n\n```\nSELECT x();\n```\n\n(Thanks for the suggestion in @curiouscheese's comment.)\n\nAlternatively, you may create a temporary table with a single value:\n\n```\nCREATE TEMP TABLE x AS SELECT 7::INT AS val;\n```\n\nThen, use it as a scalar subquery:\n\n```\nSELECT (SELECT val FROM x);\n```\n\nDuckDB is good at optimizing subqueries (both scalar and other types), so this this approach should incur no performance penalty.\n\nUpdate: version 1.1 will introduce support for the `SET VARIABLE` syntax (https://github.com/duckdb/duckdb/pull/13084).\n\n========================================\n\nCode:\n```text\nDECLARE @x Int = 7\nSELECT @x * 2;\n```\n\n```text\nSET VARIABLE\n```\n\n```text\nCREATE TEMP MACRO x() AS (SELECT 7);\n```\n\n```text\nSELECT x();\n```\n\n```text\nCREATE TEMP TABLE x AS SELECT 7::INT AS val;\n```\n\n```text\nSELECT (SELECT val FROM x);\n```\n\n```text\nDECLARE\n```\n\n```text\nSET VARIABLE\n```\n\n```text\nWITH seven as (SELECT 7 as n),\n     r as (SELECT s FROM range(1, 4) x(s))\nSELECT n, s FROM seven, r;\n```\n\n========================================\n\nComments:\n- Maybe copy from workarounds for SQLite: stackoverflow.com/questions/7739444/&hellip;\n- Is it possible to use `CREATE TEMPORARY FUNCTION` in duck db?\n- @curiouscheese: you can use `CREATE TEMPORARY MACRO`, see duckdb.org/docs/sql/statements/create_macro","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":9,"totalLines":101,"estimatedTokens":472}}6{"id":"stack-78876915","source":"stackoverflow","questionId":78876915,"title":"How to display full text and all rows in DuckDB console results?","tags":["sql","console","duckdb"],"text":"Title: How to display full text and all rows in DuckDB console results?\nTags: sql, console, duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm using the DuckDB console (duckdb.exe) on Windows and encountering issues with result display. My queries are truncating both individual row content and the total number of rows shown. Here are the specifics:\n\nText truncation: When I run a query like:\n\n```\nSELECT message FROM logs WHERE message ILIKE '%time service de%';\n```\n\nThe result truncates the 'message' column content.\n\nEven when I limit to a single row:\n\n```\nSELECT message FROM logs WHERE message ILIKE '%time service de%' LIMIT 1;\n```\n\nRow limitation: I've noticed that if there are more than 40 rows in the result set, some are omitted from the display.\n\nQuestions:\n\n- How can I display the complete text of each row in the query results?\n\n- Is there a way to show all rows in the result set, regardless of the total count?\n\n- Are there any console settings or command-line options to adjust these display behaviors?\n\nAny guidance on configuring the DuckDB console for full result display would be greatly appreciated.\n\n========================================\n\nTop Answer:\nFor anyone else looking just to increase the number of rows displayed in box mode there is:\n\n```\n.maxrows 1234\n```\n\nFrom the documentation:\n\n`.maxrows COUNT` Sets the maximum number of rows for display. Only for duckbox mode\n\n========================================\n\nCode:\n```sql\nSELECT message FROM logs WHERE message ILIKE '%time service de%';\n```\n\n```sql\nSELECT message FROM logs WHERE message ILIKE '%time service de%' LIMIT 1;\n```\n\n```text\n.mode line\n```\n\n```text\n.maxrows 1234\n```\n\n```text\n.maxrows COUNT\n```\n\n```text\nduckdb -c \"ATTACH 'sqlitedatabase.db' AS sqlite_db (TYPE sqlite); USE sqlite_db; SELECT * FROM Windows10\"\n```\n\n```text\nduckdb -c \".maxrows 9999\" -c \".maxwidth 9999\" -c \"ATTACH 'sqlitedatabase.db' AS sqlite_db (TYPE sqlite); USE sqlite_db; SELECT * FROM Windows10\"\n```\n\n========================================\n\nComments:\n- This is exactly what I want! Thank you!\n- do you know how can I switch to the next page (row 41 to 80) of the query result?\n- `.mode box` can also be used to view scrollable tables.\n- As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.\n- That answer has already been given years ago. Please, avoid code only answer, and add some explanation. Especially when answering to old questions, it is important to explain why your answer is different, and even better than existing answers.","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":7,"totalLines":85,"estimatedTokens":673}}7{"id":"stack-78165559","source":"stackoverflow","questionId":78165559,"title":"How to write a polars dataframe to DuckDB","tags":["python","dataframe","uri","python-polars","duckdb"],"text":"Title: How to write a polars dataframe to DuckDB\nTags: python, dataframe, uri, python-polars, duckdb\nSource: Stack Overflow\n\nQuestion:\nI am trying to write a Polars DataFrame to a duckdb database. I have the following simple code which I expected to work:\n\n```\nimport polars as pl\nimport duckdb\n\npldf = pl.DataFrame({'mynum': [1,2,3,4]})\nwith duckdb.connect(database=\"scratch.db\", read_only=False) as con:\n pldf.write_database(table_name='test_table', connection=con)\n```\n\nHowever, I get the following error:\n\n```\nsqlalchemy.exc.ArgumentError: Expected string or URL object, got I get a similar error if I use the non-default `engine='adbc'` instead of `df.write_database()`'s default `engine='sqlalchemy'`.\n\nSo it seemed it should be easy enough to just swap in a URI for my ducdkb database, but I haven't been able to get that to work either. Potentially it's complicated by my being on Windows?\n\n========================================\n\nTop Answer:\nYou can also use execute.\n\n```\nimport polars as pl\nimport duckdb\n\npldf = pl.DataFrame({'mynum': [1,2,3,4]})\nwith duckdb.connect(database=\"scratch.db\", read_only=False) as con:\n con.execute(f\"\"\"\n CREATE TABLE IF NOT EXISTS 'test_table' AS SELECT * FROM pldf;\n \"\"\")\n```\n\n========================================\n\nCode:\n```text\nimport polars as pl\nimport duckdb\n\npldf = pl.DataFrame({'mynum': [1,2,3,4]})\nwith duckdb.connect(database=\"scratch.db\", read_only=False) as con:\n    pldf.write_database(table_name='test_table', connection=con)\n```\n\n```text\nsqlalchemy.exc.ArgumentError: Expected string or URL object, got <duckdb.duckdb.DuckDBPyConnection object\n```\n\n```text\nengine='adbc'\n```\n\n```text\ndf.write_database()\n```\n\n```text\nengine='sqlalchemy'\n```\n\n```py\nduckdb.sql(\"SELECT * FROM df\").show()\n```\n\n```py\ndf.write_database(\n    table_name='test_table',\n    connection=\"duckdb:///scratch.db\",\n)\n```\n\n```py\nwith duckdb.connect(database=\"scratch.db\", read_only=False) as con:\n    con.query(\"SELECT * FROM test_table\").show()\n```\n\n```text\n┌───────┐\n│ mynum │\n│ int64 │\n├───────┤\n│     1 │\n│     2 │\n│     3 │\n│     4 │\n└───────┘\n```\n\n```text\nduckdb-engine\n```\n\n```text\nimport polars as pl\nimport duckdb\n\npldf = pl.DataFrame({'mynum': [1,2,3,4]})\nwith duckdb.connect(database=\"scratch.db\", read_only=False) as con:\n    con.execute(f\"\"\"\n        CREATE TABLE IF NOT EXISTS 'test_table' AS SELECT * FROM pldf;\n    \"\"\")\n```\n\n========================================\n\nComments:\n- interesting, the first time I ran this I got an error that polars.DataFrame.to_pandas(use_pyarrow_extension_array=True) requires pyarrow>8.0, so I guess they're still using pandas under the hood. I guess with pandas2 and pyarrow it makes sense.\n- @MaxPower Exactly, if you take a look in the source for `pl.DataFrame.write_database`, you'll find: *Writing with engine 'sqlalchemy' currently requires pandas. Install with: pip install pandas*.\n- Can you do something like `duckdb.sql(\"insert into scratch.test_table select * from df\")`?\n- @DeanMacGregor This raises an error about the table not existing. At which point would you try this statement?\n- I don't know, I've never used duckdb-engine that's why I asked it as a question.","metadata":{"transformedAt":"2026-08-18T18:32:26.974Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":11,"totalLines":118,"estimatedTokens":789}}8{"id":"stack-79313103","source":"stackoverflow","questionId":79313103,"title":"asof-join with multiple inequality conditions","tags":["python","dataframe","python-polars","numba","duckdb"],"text":"Title: asof-join with multiple inequality conditions\nTags: python, dataframe, python-polars, numba, duckdb\nSource: Stack Overflow\n\nQuestion:\nI have two dataframes: **a (~600M rows)** and **b (~2M rows)**. What is the best approach for joining b onto a, when using 1 equality condition and **2 inequality conditions** on the respective columns?\n\n- a_1 = b_1\n\n- a_2 >= b_2\n\n- a_3 >= b_3\n\nI have explored the following paths so far:\n\n**Polars**:\n\n- join_asof(): only allows for 1 inequality condition\n\n- join_where() with filter(): even with a small tolerance window, the standard Polars installation runs out of rows (4.3B row limit) during the join, and the polars-u64-idx installation runs out of memory (512GB)\n\n- **DuckDB**: ASOF LEFT JOIN: also only allows for 1 inequality condition\n\n- **Numba**: As the above didn't work, I tried to create my own join_asof() function - see code below. It works fine but with increasing lengths of a, it becomes prohibitively slow. I tried various different configurations of for/ while loops and filtering, all with similar results.\n\nNow I'm running a bit out of ideas... What would be a more efficient way to implement this?\n\nThank you\n\n```\nimport numba as nb\nimport numpy as np\nimport polars as pl\nimport time\n\n@nb.njit(nb.int32[:](nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:]), parallel=True)\ndef join_multi_ineq(a_1, a_2, a_3, b_1, b_2, b_3, b_4):\n output = np.zeros(len(a_1), dtype=np.int32)\n\n for i in nb.prange(len(a_1)):\n\n for j in range(len(b_1) - 1, -1, -1):\n\n if a_1[i] == b_1[j]:\n\n if a_2[i] >= b_2[j]:\n\n if a_3[i] >= b_3[j]:\n output[i] = b_4[j]\n break\n\n return output\n\nlength_a = 5_000_000\nlength_b = 2_000_000\n\nstart_time = time.time()\noutput = join_multi_ineq(a_1=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n a_2=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n a_3=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n b_1=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n b_2=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n b_3=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n b_4=np.random.randint(1, 1_000, length_b, dtype=np.int32))\nprint(f\"Duration: {(time.time() - start_time):.2f} seconds\")\n```\n\n========================================\n\nTop Answer:\nYou can use DuckDB (Postgresql) `distinct on` clause:\n\n```\nimport duckdb\n\ndf_res = duckdb.sql(\"\"\"\n select distinct on (a.a1, a.a2, a.a3)\n a.a1,\n a.a2,\n a.a3,\n b.b4\n from df_a as a\n inner join df_b as b on\n a.a1 = b.b1 and\n a.a2 >= b.b2 and\n a.a3 >= b.b3\n order by\n b.b2 desc,\n b.b3 desc\n\"\"\").pl()\n```\n\nYou could also try to use `pl.DataFrame.join_where()` but in lazy mode. I'm assuming your 'a' dataframe has unique key, in this example case - `a1,a2,a3`.\n\n- `pl.DataFrame.lazy()` to treat DataFrame as LazyFrame.\n\n- `pl.LazyFrame.join_where()` to join lazy frames together.\n\n- `pl.LazyFrame.sort()` to sort the results.\n\n- `pl.LazyFrame.drop()` to drop `b2,b3` columns.\n\n- `pl.LazyFrame.unique()` to leave only one row per `a1,a2,a3`.\n\n- `pl.LazyFrame.collect()`.\n\n```\ndf_res = (\n df_a.lazy()\n .join_where(\n df_b.lazy(),\n pl.col.a1 == pl.col.b1,\n pl.col.a2 >= pl.col.b2,\n pl.col.a3 >= pl.col.b3\n )\n .sort([\"a1\",\"a2\",\"a3\",\"b2\",\"b3\"])\n .drop([\"b2\",\"b3\"])\n .unique([\"a1\",\"a2\",\"a3\"], keep=\"first\")\n).collect()\n```\n\nIf none of these work, you could try to split one of the frames into N chunks with `pl.DataFrame.partition_by()`, process chunks separately and then use `pl.concat()` to concat them back.\n\n```\nN = 20\n\ndf_a_list = (\n df_a\n .with_columns(r = pl.int_range(pl.len()) * N // pl.len())\n .partition_by(\"r\", include_key=False)\n)\n\ndf_res = pl.concat([\n df_a_t.join_where(\n df_b,\n pl.col.a1 == pl.col.b1,\n pl.col.a2 >= pl.col.b2,\n pl.col.a3 >= pl.col.b3\n )\n .sort([\"a1\",\"a2\",\"a3\",\"b2\",\"b3\"])\n .drop([\"b2\",\"b3\"])\n .unique([\"a1\",\"a2\",\"a3\"], keep=\"first\")\n for df_a_t in df_a_list\n])\n```\n\n========================================\n\nCode:\n```py\nimport numba as nb\nimport numpy as np\nimport polars as pl\nimport time\n\n\n@nb.njit(nb.int32[:](nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:]), parallel=True)\ndef join_multi_ineq(a_1, a_2, a_3, b_1, b_2, b_3, b_4):\n    output = np.zeros(len(a_1), dtype=np.int32)\n\n    for i in nb.prange(len(a_1)):\n\n        for j in range(len(b_1) - 1, -1, -1):\n\n            if a_1[i] == b_1[j]:\n\n                if a_2[i] >= b_2[j]:\n\n                    if a_3[i] >= b_3[j]:\n                        output[i] = b_4[j]\n                        break\n\n    return output\n\n\nlength_a = 5_000_000\nlength_b = 2_000_000\n\nstart_time = time.time()\noutput = join_multi_ineq(a_1=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n                         a_2=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n                         a_3=np.random.randint(1, 1_000, length_a, dtype=np.int32),\n                         b_1=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n                         b_2=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n                         b_3=np.random.randint(1, 1_000, length_b, dtype=np.int32),\n                         b_4=np.random.randint(1, 1_000, length_b, dtype=np.int32))\nprint(f\"Duration: {(time.time() - start_time):.2f} seconds\")\n```\n\n```py\nimport numba as nb\nimport numpy as np\nimport time\n\nlength_a = 5_000_000\nlength_b = 2_000_000\n\na_1=np.random.randint(1, 1_000, length_a, dtype=np.int32)\na_2=np.random.randint(1, 1_000, length_a, dtype=np.int32)\na_3=np.random.randint(1, 1_000, length_a, dtype=np.int32)\nb_1=np.random.randint(1, 1_000, length_b, dtype=np.int32)\nb_2=np.random.randint(1, 1_000, length_b, dtype=np.int32)\nb_3=np.random.randint(1, 1_000, length_b, dtype=np.int32)\nb_4=np.random.randint(1, 1_000, length_b, dtype=np.int32)\n\nIntList = nb.types.ListType(nb.types.int32)\n\n@nb.njit(nb.int32[:](nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:], nb.int32[:]), parallel=True)\ndef join_multi_ineq_fast(a_1, a_2, a_3, b_1, b_2, b_3, b_4):\n    output = np.zeros(len(a_1), dtype=np.int32)\n    b1_indices = nb.typed.Dict.empty(key_type=nb.types.int32, value_type=IntList)\n    for j in range(len(b_1)):\n        val = b_1[j]\n        if val in b1_indices:\n            b1_indices[val].append(j)\n        else:\n            lst = nb.typed.List.empty_list(item_type=np.int32)\n            lst.append(j)\n            b1_indices[val] = lst\n    kmean = 0\n    for i in nb.prange(len(a_1)):\n        if a_1[i] in b1_indices:\n            indices = b1_indices[a_1[i]]\n            v2 = a_2[i]\n            v3 = a_3[i]\n            for k in range(len(indices) - 1, -1, -1):\n                j = indices[np.uint32(k)]\n                #assert a_1[i] == b_1[j]\n                if v2 >= b_2[j] and v3 >= b_3[j]:\n                    output[i] = b_4[j]\n                    break\n    return output\n\n%time join_multi_ineq_fast(a_1, a_2, a_3, b_1, b_2, b_3, b_4)\n```\n\n```none\nRoman's code:        >120.00 sec     (require a HUGE amount of RAM: >16 GiB)\nNaive Numba code:      24.85 sec\nThis implementation:    0.83 sec     <-----\n```\n\n```text\nO(n²)\n```\n\n```text\nb_1\n```\n\n```text\nb_1\n```\n\n```text\na_1[i] == b_1[j]\n```\n\n```text\nj\n```\n\n```text\nk\n```\n\n```py\nimport duckdb\n\ndf_res = duckdb.sql(\"\"\"\n    select distinct on (a.a1, a.a2, a.a3)\n        a.a1,\n        a.a2,\n        a.a3,\n        b.b4\n    from df_a as a\n        inner join df_b as b on\n            a.a1 = b.b1 and\n            a.a2 >= b.b2 and\n            a.a3 >= b.b3\n    order by\n        b.b2 desc,\n        b.b3 desc\n\"\"\").pl()\n```\n\n```py\ndf_res = (\n    df_a.lazy()\n    .join_where(\n        df_b.lazy(),\n        pl.col.a1 == pl.col.b1,\n        pl.col.a2 >= pl.col.b2,\n        pl.col.a3 >= pl.col.b3\n    )\n    .sort([\"a1\",\"a2\",\"a3\",\"b2\",\"b3\"])\n    .drop([\"b2\",\"b3\"])\n    .unique([\"a1\",\"a2\",\"a3\"], keep=\"first\")\n).collect()\n```\n\n```py\nN = 20\n\ndf_a_list = (\n    df_a\n    .with_columns(r = pl.int_range(pl.len()) * N // pl.len())\n    .partition_by(\"r\", include_key=False)\n)\n\ndf_res = pl.concat([\n    df_a_t.join_where(\n        df_b,\n        pl.col.a1 == pl.col.b1,\n        pl.col.a2 >= pl.col.b2,\n        pl.col.a3 >= pl.col.b3\n    )\n    .sort([\"a1\",\"a2\",\"a3\",\"b2\",\"b3\"])\n    .drop([\"b2\",\"b3\"])\n    .unique([\"a1\",\"a2\",\"a3\"], keep=\"first\")\n    for df_a_t in df_a_list\n])\n```\n\n```text\ndistinct on\n```\n\n```text\npl.DataFrame.join_where()\n```\n\n```text\na1,a2,a3\n```\n\n```text\npl.DataFrame.lazy()\n```\n\n```text\npl.LazyFrame.join_where()\n```\n\n```text\npl.LazyFrame.sort()\n```\n\n```text\npl.LazyFrame.drop()\n```\n\n```text\nb2,b3\n```\n\n```text\npl.LazyFrame.unique()\n```\n\n```text\na1,a2,a3\n```\n\n```text\npl.LazyFrame.collect()\n```\n\n```text\npl.DataFrame.partition_by()\n```\n\n```text\npl.concat()\n```\n\n========================================\n\nComments:\n- The best place for improvement likely is within the equi join. Are the equi join columns' values duplicated? Heavily duplicated? A binary search within the equi join and subsequent binary searches within the non equi joins may provide some perf improvement. If U can, provide sample input dataframes, with expected output dataframe\n- did you check .join_where( ) ?\n- @rehaqds he mentioned it in no. 2 of his attempts\n- I got an *error* on the first code (`Argument 'name' has incorrect type`). The code 2 and 3 takes >16 GiB of RAM (huge). The code 2 takes so much RAM that my machine was frozen (>22 GiB). Moreover, the Polar codes are **slower** than the one of the OP (requiring nearly no additional memory space)...\n- yep, lazy processing of large dataframes doesn't always work as expected. have you tried the one with partitioning? maybe you can increase amount of chunks?\n- Thank you Roman for your suggestions! The DISTINCT ON/ ORDER BY query is quite elegant and it ran in ~2min on the actual data. However, J&#233;r&#244;me's suggestion ended up being quite a bit faster (~30sec, incl. all the pre- and post-processing required for numba) so I'll mark that one as the answer.\n- @usdn interesting, thanks. I guess unless there's proper way of doing \"asof\" join on multiple columns it's hard to beat numba.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":25,"totalLines":386,"estimatedTokens":2505}}9{"id":"stack-76854735","source":"stackoverflow","questionId":76854735,"title":"How to increase row output limit in DuckDB in Python?","tags":["python","duckdb"],"text":"Title: How to increase row output limit in DuckDB in Python?\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm working with DuckDB in Python (in a Jupyter Notebook). How can I force DuckDB to print all rows in the output rather than truncating rows? I've already increased output limits in the Jupyter Notebook.\n\nThis would be the equivalent of setting .maxrows in the CLI, but I can't find how to do this in Python.\n\n========================================\n\nTop Answer:\nThere is not currently a way to increase that maxrows parameter in Python. I would recommend outputting your data as a Pandas or Arrow dataframe and using the flags that those libraries support to decide what is shown.\n\nEx:\n\n```\nmy_df = duckdb.sql(\"select 42\").df()\n```\n\n========================================\n\nCode:\n```py\nduckdb.sql(\"select * from range(100)\").show(max_rows=100)\n```\n\n```text\nshow\n```\n\n```py\nmy_df = duckdb.sql(\"select 42\").df()\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":3,"totalLines":34,"estimatedTokens":234}}10{"id":"stack-74152013","source":"stackoverflow","questionId":74152013,"title":"Importing parquet file in chunks and insert in DuckDB","tags":["python","pandas","parquet","pyarrow","duckdb"],"text":"Title: Importing parquet file in chunks and insert in DuckDB\nTags: python, pandas, parquet, pyarrow, duckdb\nSource: Stack Overflow\n\nQuestion:\nI am trying to load the parquet file with row size group = 10 into duckdb table in chunks. I am not finding any documents to support this.\n\nThis is my work so on: see code\n\n```\nimport duckdb\nimport pandas as pd\nimport gc\nimport numpy as np\n\n# connect to an in-memory database\ncon = duckdb.connect(database='database.duckdb', read_only=False)\n\ndf1 = pd.read_parquet(\"file1.parquet\")\ndf2 = pd.read_parquet(\"file2.parquet\")\n\n# create the table \"my_table\" from the DataFrame \"df1\"\ncon.execute(\"CREATE TABLE table1 AS SELECT * FROM df1\")\n\n# create the table \"my_table\" from the DataFrame \"df2\"\ncon.execute(\"CREATE TABLE table2 AS SELECT * FROM df2\")\n\ncon.close()\ngc.collect()\n```\n\nPlease help me load both the tables with parquet files with row group size or chunks. ALso, load the data to duckdb as chunks\n\n========================================\n\nTop Answer:\nThis is not necessarily a solution (I like the pyarrow oriented one already submitted!), but here are some other pieces of information that may help you. I am attempting to guess what your root cause problem is! (https://xyproblem.info/)\n\nIn the next release of DuckDB (and on the current master branch), data will be written to disk in a streaming fashion for inserts. This should allow you to insert ~any size of Parquet file into a file-backed persistent DuckDB without running out of memory. Hopefully it removes the need for you to do batching at all (since DuckDB will batch based on your rowgroups automatically)! For example:\n\n```\ncon.execute(\"CREATE TABLE table1 AS SELECT * FROM 'file1.parquet'\")\n```\n\nAnother note is that the typically recommended size of a rowgroup is closer to 100,000 or 1,000,000 rows. This has a few benefits over very small rowgroups. Compression will work better, since compression operates within a rowgroup only. There will also be less overhead spent on storing statistics, since each rowgroup stores its own statistics. And, since DuckDB is quite fast, it will process a 100,000 or 1,000,000 row rowgroup quite quickly (whereas the overhead of reading statistics may slow things down with really small rowgroups).\n\n========================================\n\nCode:\n```text\nimport duckdb\nimport pandas as pd\nimport gc\nimport numpy as np\n\n# connect to an in-memory database\ncon = duckdb.connect(database='database.duckdb', read_only=False)\n\ndf1 = pd.read_parquet(\"file1.parquet\")\ndf2 = pd.read_parquet(\"file2.parquet\")\n\n# create the table \"my_table\" from the DataFrame \"df1\"\ncon.execute(\"CREATE TABLE table1 AS SELECT * FROM df1\")\n\n# create the table \"my_table\" from the DataFrame \"df2\"\ncon.execute(\"CREATE TABLE table2 AS SELECT * FROM df2\")\n\ncon.close()\ngc.collect()\n```\n\n```text\ndf1 = pd.read_parquet(\"file1.parquet\")\n```\n\n```text\nimport pyarrow.parquet as pq\nparquet_file = pq.ParquetFile('example.parquet')\nfor i in parquet_file.iter_batches(batch_size=10):\n    print(\"RecordBatch\")\n    print(i.to_pandas())\n```\n\n```text\nfor i in parquet_file.iter_batches(batch_size=10, columns=['user_address'], row_groups=[0,2,3]):\n```\n\n```py\ncon.execute(\"CREATE TABLE table1 AS SELECT * FROM 'file1.parquet'\")\n```\n\n========================================\n\nComments:\n- Does this answer your question? Is it possible to read parquet files in chunks?","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":5,"totalLines":95,"estimatedTokens":844}}11{"id":"stack-79097421","source":"stackoverflow","questionId":79097421,"title":"rolling sum with right-closed interval in duckdb","tags":["python","postgresql","python-polars","duckdb"],"text":"Title: rolling sum with right-closed interval in duckdb\nTags: python, postgresql, python-polars, duckdb\nSource: Stack Overflow\n\nQuestion:\nIn Polars / pandas I can do a rolling sum where row each row the window is `(row - 10 minutes, row]`. For example:\n\n```\nimport polars as pl\n\ndata = {\n \"timestamp\": [\n \"2023-08-04 10:00:00\",\n \"2023-08-04 10:05:00\",\n \"2023-08-04 10:10:00\",\n \"2023-08-04 10:10:00\",\n \"2023-08-04 10:20:00\",\n \"2023-08-04 10:20:00\",\n ],\n \"value\": [1, 2, 3, 4, 5, 6],\n}\n\ndf = pl.DataFrame(data).with_columns(pl.col(\"timestamp\").str.strptime(pl.Datetime))\n\nprint(\n df.with_columns(pl.col(\"value\").rolling_sum_by(\"timestamp\", \"10m\", closed=\"right\"))\n)\n```\n\nThis outputs\n\n```\nshape: (6, 2)\n┌─────────────────────┬───────┐\n│ timestamp ┆ value │\n│ --- ┆ --- │\n│ datetime[μs] ┆ i64 │\n╞═════════════════════╪═══════╡\n│ 2023-08-04 10:00:00 ┆ 1 │\n│ 2023-08-04 10:05:00 ┆ 3 │\n│ 2023-08-04 10:10:00 ┆ 9 │\n│ 2023-08-04 10:10:00 ┆ 9 │\n│ 2023-08-04 10:20:00 ┆ 11 │\n│ 2023-08-04 10:20:00 ┆ 11 │\n└─────────────────────┴───────┘\n```\n\nHow can I do this in DuckDB? Closest I could come up with is:\n\n```\nrel = duckdb.sql(\"\"\"\nSELECT\n timestamp,\n value,\n SUM(value) OVER roll AS rolling_sum\nFROM df\nWINDOW roll AS (\n ORDER BY timestamp\n RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW\n)\nORDER BY timestamp;\n\"\"\")\nprint(rel)\n```\n\nbut that makes the window `[row - 10 minutes, row]`, not `(row - 10 minutes, row]`\n\nAlternatively, I could do\n\n```\nrel = duckdb.sql(\"\"\"\nSELECT\n timestamp,\n value,\n SUM(value) OVER roll AS rolling_sum\nFROM df\nWINDOW roll AS (\n ORDER BY timestamp\n RANGE BETWEEN INTERVAL '10 minutes' - INTERVAL '1 microsecond' PRECEDING AND CURRENT ROW\n)\nORDER BY timestamp;\n\"\"\")\n```\n\nbut I'm not sure about how robust that'd be?\n\n========================================\n\nTop Answer:\nDuckDB window operator maintainer here. Your second solution will do what you want, and it is unfortunate that the standard does not have a way to specify this (although it sounds like a useful extension). Possibly of interest, we *do* support `EXCLUDE CURRENT ROW`, which would give you a half-open interval on the right.\n\nI'm not sure what you mean by \"robust\", but I'm guessing you are concerned about timestamp accuracy changing under you, and that should not be an issue. We do have multiple precision types, but they will all get converted internally to µs precision for windowing operations. Changing the internal timestamp precision would invalidate every database out there, and that is not going to happen.\n\nFor performance, there should also be no difference as they both use constants that get evaluated at query compile time. On the other hand, adding a second window function will definitely be twice as slow.\n\n========================================\n\nCode:\n```py\nimport polars as pl\n\ndata = {\n    \"timestamp\": [\n        \"2023-08-04 10:00:00\",\n        \"2023-08-04 10:05:00\",\n        \"2023-08-04 10:10:00\",\n        \"2023-08-04 10:10:00\",\n        \"2023-08-04 10:20:00\",\n        \"2023-08-04 10:20:00\",\n    ],\n    \"value\": [1, 2, 3, 4, 5, 6],\n}\n\ndf = pl.DataFrame(data).with_columns(pl.col(\"timestamp\").str.strptime(pl.Datetime))\n\nprint(\n    df.with_columns(pl.col(\"value\").rolling_sum_by(\"timestamp\", \"10m\", closed=\"right\"))\n)\n```\n\n```text\nshape: (6, 2)\n┌─────────────────────┬───────┐\n│ timestamp           ┆ value │\n│ ---                 ┆ ---   │\n│ datetime[μs]        ┆ i64   │\n╞═════════════════════╪═══════╡\n│ 2023-08-04 10:00:00 ┆ 1     │\n│ 2023-08-04 10:05:00 ┆ 3     │\n│ 2023-08-04 10:10:00 ┆ 9     │\n│ 2023-08-04 10:10:00 ┆ 9     │\n│ 2023-08-04 10:20:00 ┆ 11    │\n│ 2023-08-04 10:20:00 ┆ 11    │\n└─────────────────────┴───────┘\n```\n\n```text\nrel = duckdb.sql(\"\"\"\nSELECT\n  timestamp,\n  value,\n  SUM(value) OVER roll AS rolling_sum\nFROM df\nWINDOW roll AS (\n  ORDER BY timestamp\n  RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW\n)\nORDER BY timestamp;\n\"\"\")\nprint(rel)\n```\n\n```py\nrel = duckdb.sql(\"\"\"\nSELECT\n  timestamp,\n  value,\n  SUM(value) OVER roll AS rolling_sum\nFROM df\nWINDOW roll AS (\n  ORDER BY timestamp\n  RANGE BETWEEN INTERVAL '10 minutes' - INTERVAL '1 microsecond' PRECEDING AND CURRENT ROW\n)\nORDER BY timestamp;\n\"\"\")\n```\n\n```text\n(row - 10 minutes, row]\n```\n\n```text\n[row - 10 minutes, row]\n```\n\n```text\n(row - 10 minutes, row]\n```\n\n```text\nimport duckdb\n\nrel = duckdb.sql(\"\"\"\nSELECT\n  timestamp,\n  value,\n  SUM(value) OVER roll - coalesce(SUM(value) OVER exclude, 0) AS rolling_sum\nFROM df\nWINDOW roll AS (\n  ORDER BY timestamp\n  RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND CURRENT ROW\n), exclude AS (\n  ORDER BY timestamp\n  RANGE BETWEEN INTERVAL 10 minutes PRECEDING AND INTERVAL 10 minutes PRECEDING\n)\nORDER BY timestamp;\n\"\"\")\nprint(rel)\n```\n\n```text\n┌─────────────────────┬───────┬─────────────┐\n│      timestamp      │ value │ rolling_sum │\n│      timestamp      │ int64 │   int128    │\n├─────────────────────┼───────┼─────────────┤\n│ 2023-08-04 10:00:00 │     1 │           1 │\n│ 2023-08-04 10:05:00 │     2 │           3 │\n│ 2023-08-04 10:10:00 │     3 │           9 │\n│ 2023-08-04 10:10:00 │     4 │           9 │\n│ 2023-08-04 10:20:00 │     5 │          11 │\n│ 2023-08-04 10:20:00 │     6 │          11 │\n└─────────────────────┴───────┴─────────────┘\n```\n\n```text\nEXCLUDE CURRENT ROW\n```\n\n========================================\n\nComments:\n- So from what you're saying it sounds safe to use 10 minutes - 1 microsecond interval. It still feels a bit weird though.\n- Yes, it is safe, and yes it is weird. I'm going to look around and see if anyone has extended the syntax to support this - it wouldn't be hard to add.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":10,"totalLines":223,"estimatedTokens":1393}}12{"id":"stack-77214715","source":"stackoverflow","questionId":77214715,"title":"Get a hash of a table in duckdb","tags":["r","duckdb"],"text":"Title: Get a hash of a table in duckdb\nTags: r, duckdb\nSource: Stack Overflow\n\nQuestion:\nI have duckdb database with large tables (~100mln rows) where I want to see if some values have changed in a specific table. I do not need to see which rows have changed, just if any have changed at all.\n\nOne way to achieve this is to use a hash of the table. In its simplest form that could be a row-count but ideally it would take the values of the table into account as well, which a proper hash algorithm like md5 or similar would do.\n\nA solution would be to get the data into R and then compute a hash, but this would mean pulling all data into R, which might take a long while.\n\nIs there an efficient way to do this? Ie can I `CALL`/`PRAGMA` duckdb to get a hash/size/id of a table?\n\n### MWE\n\nA minimum working example using R would be this.\n\n```\ncon [1] \"24bc99234ecb908438a9e0bfb4313c73\"\n\n# update some values in the table\nDBI::dbExecute(con, \"UPDATE mtcars SET disp = 123 WHERE mpg = 10.4\")\n# compute the hash again to see if it has changed\nrlang::hash(DBI::dbReadTable(con, \"mtcars\"))\n#> [1] \"64802a862b2419d9d098bbd43b614aab\"\n```\n\n========================================\n\nCode:\n```text\ncon <- DBI::dbConnect(duckdb::duckdb())\nDBI::dbWriteTable(con, \"mtcars\", mtcars)\n\nrlang::hash(DBI::dbReadTable(con, \"mtcars\")) # transfers all data into R and computes the hash in R\n#> [1] \"24bc99234ecb908438a9e0bfb4313c73\"\n\n# update some values in the table\nDBI::dbExecute(con, \"UPDATE mtcars SET disp = 123 WHERE mpg = 10.4\")\n# compute the hash again to see if it has changed\nrlang::hash(DBI::dbReadTable(con, \"mtcars\"))\n#> [1] \"64802a862b2419d9d098bbd43b614aab\"\n```\n\n```text\nCALL\n```\n\n```text\nPRAGMA\n```\n\n```text\nD create table tbl as (select 1 as a, 2 as b, 3 as c);\nD select tbl::text, hash(tbl::text), md5(tbl::text) from tbl;\n┌──────────────────────────┬────────────────────────────┬──────────────────────────────────┐\n│   CAST(tbl AS VARCHAR)   │ hash(CAST(tbl AS VARCHAR)) │    md5(CAST(tbl AS VARCHAR))     │\n│         varchar          │           uint64           │             varchar              │\n├──────────────────────────┼────────────────────────────┼──────────────────────────────────┤\n│ {'a': 1, 'b': 2, 'c': 3} │        6764392534128998287 │ e31681d6e7ab078c9679fcd4f50136eb │\n└──────────────────────────┴────────────────────────────┴──────────────────────────────────┘\n```\n\n```text\nSELECT tbl::TEXT, HASH(tbl::TEXT), MD5(tbl::TEXT) FROM tbl;\n```\n\n```text\nSELECT md5(string_agg(tbl::text, '')) FROM tbl;\n```\n\n========================================\n\nComments:\n- I have actually just asked pretty much the same question in DuckDB's Discord Server, because, afaik, there is no function to do that. But let's see if someone throw us some light on that. Meanwhile, not sure if it works for you, but the workaround I am using is to keep one table per duckdb file and hash the whole file -e.g. rlang::hash_file(\"foo.db\")-\n- Note there is a `hash()` function in duckdb, but this seems to work only with values or sets but not tables/queries: duckdb.org/docs/sql/functions/utility.\n- The `tbl::text` thing casting the whole row to JSON is awesome! Way to go duckdb! A note on `SELECT md5(string_agg(tbl::text, '')) FROM tbl;`, please correct if I am wrong: the md5() result will change if the order of rows changes. duckdb maintains row order more than other DBs, but still it can change. Untested: I think `SELECT SUM(hash(tbl::text))` would avoid this, and be much faster, and probably \"good enough\" for most circumstances?","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":6,"totalLines":77,"estimatedTokens":882}}13{"id":"stack-75727685","source":"stackoverflow","questionId":75727685,"title":"How do I get a list of table-like objects visible to duckdb in a python session?","tags":["python","pandas","dataframe","duckdb"],"text":"Title: How do I get a list of table-like objects visible to duckdb in a python session?\nTags: python, pandas, dataframe, duckdb\nSource: Stack Overflow\n\nQuestion:\nI like how duckdb lets me query DataFrames as if they were sql tables:\n\n```\ndf = pandas.read_parquet(\"my_data.parquet\")\ncon.query(\"select * from df limit 10\").fetch_df()\n```\n\nI also like how duckdb has metadata commands like `SHOW TABLES;`, like a real database. However, `SHOW TABLES;` doesn't show pandas DataFrames or other table-like objects.\n\nmy question is: does duckdb offer something like `SHOW TABLES;` that includes both (1) real database tables and (2) table-like objects (e.g. pandas DataFrames) and their schemas?\n\nThanks!\n\n========================================\n\nTop Answer:\nWas just searching for this and I found this worked, when select * from duckdb_tables didn't work for me:\n\n```\nimport duckdb\nduck_db = duckdb.connect('my_db_location', read_only=False)\npandas_df = duck_db.execute(\"SHOW TABLES\").df()\nprint(pandas_df)\n```\n\n========================================\n\nCode:\n```py\ndf = pandas.read_parquet(\"my_data.parquet\")\ncon.query(\"select * from df limit 10\").fetch_df()\n```\n\n```text\nSHOW TABLES;\n```\n\n```text\nSHOW TABLES;\n```\n\n```text\nSHOW TABLES;\n```\n\n```py\nimport duckdb\n\ndf = duckdb.sql(\"SELECT * FROM duckdb_tables;\").df()\nprint(df.dtypes)\n\ndatabase_name             object\ndatabase_oid               int64\nschema_name               object\nschema_oid                 int64\ntable_name                object\ntable_oid                  int64\ninternal                    bool\ntemporary                   bool\nhas_primary_key             bool\nestimated_size             int64\ncolumn_count               int64\nindex_count                int64\ncheck_constraint_count     int64\nsql                       object\ndtype: object\n```\n\n```text\nduckdb_%\n```\n\n```text\nSHOW TABLES\n```\n\n```text\nimport duckdb\nduck_db = duckdb.connect('my_db_location', read_only=False)\npandas_df = duck_db.execute(\"SHOW TABLES\").df()\nprint(pandas_df)\n```\n\n========================================\n\nComments:\n- Last time I checked - you would need to register a view and then query `information_schema.columns` github.com/duckdb/duckdb/discussions/3623\n- The link that is provided in this answer is gold.\n- Interesting, it should work - Did you try with `SELECT * FROM duckdb_tables()` -> adding parenthesis to the table function ?","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":8,"totalLines":94,"estimatedTokens":597}}14{"id":"stack-78953239","source":"stackoverflow","questionId":78953239,"title":"Minimum periods in rolling mean","tags":["python","duckdb"],"text":"Title: Minimum periods in rolling mean\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nSay I have:\n\n```\ndata = {\n 'id': ['a', 'a', 'a', 'b', 'b', 'b', 'b'],\n 'd': [1,2,3,0,1,2,3],\n 'sales': [5,1,3,4,1,2,3],\n}\n```\n\nI would like to add a column with a rolling mean with window size 2, with `min_periods=2`, over `'id'`\n\nIn Polars, I can do:\n\n```\nimport polars as pl\n\ndf = pl.DataFrame(data)\ndf.with_columns(sales_rolling = pl.col('sales').rolling_mean(2).over('id'))\n```\n\n```\nshape: (7, 4)\n┌─────┬─────┬───────┬───────────────┐\n│ id ┆ d ┆ sales ┆ sales_rolling │\n│ --- ┆ --- ┆ --- ┆ --- │\n│ str ┆ i64 ┆ i64 ┆ f64 │\n╞═════╪═════╪═══════╪═══════════════╡\n│ a ┆ 1 ┆ 5 ┆ null │\n│ a ┆ 2 ┆ 1 ┆ 3.0 │\n│ a ┆ 3 ┆ 3 ┆ 2.0 │\n│ b ┆ 0 ┆ 4 ┆ null │\n│ b ┆ 1 ┆ 1 ┆ 2.5 │\n│ b ┆ 2 ┆ 2 ┆ 1.5 │\n│ b ┆ 3 ┆ 3 ┆ 2.5 │\n└─────┴─────┴───────┴───────────────┘\n```\n\nWhat's the DuckDB equivalent? I've tried\n\n```\nimport duckdb\n\nduckdb.sql(\"\"\"\n select\n *,\n mean(sales) over (\n partition by id \n order by d\n range between 1 preceding and 0 following\n ) as sales_rolling \n from df\n\"\"\").sort('id', 'd')\n```\n\nbut get\n\n```\n┌─────────┬───────┬───────┬───────────────┐\n│ id │ d │ sales │ sales_rolling │\n│ varchar │ int64 │ int64 │ double │\n├─────────┼───────┼───────┼───────────────┤\n│ a │ 1 │ 5 │ 5.0 │\n│ a │ 2 │ 1 │ 3.0 │\n│ a │ 3 │ 3 │ 2.0 │\n│ b │ 0 │ 4 │ 4.0 │\n│ b │ 1 │ 1 │ 2.5 │\n│ b │ 2 │ 2 │ 1.5 │\n│ b │ 3 │ 3 │ 2.5 │\n└─────────┴───────┴───────┴───────────────┘\n```\n\nThis is very close, but duckdb still calculates the rolling mean when there's only a single value in the window. How can I replicate the `min_periods=2` (default) behaviour from Polars?\n\n========================================\n\nCode:\n```py\ndata = {\n    'id': ['a', 'a', 'a', 'b', 'b', 'b', 'b'],\n    'd': [1,2,3,0,1,2,3],\n    'sales': [5,1,3,4,1,2,3],\n}\n```\n\n```py\nimport polars as pl\n\ndf = pl.DataFrame(data)\ndf.with_columns(sales_rolling = pl.col('sales').rolling_mean(2).over('id'))\n```\n\n```text\nshape: (7, 4)\n┌─────┬─────┬───────┬───────────────┐\n│ id  ┆ d   ┆ sales ┆ sales_rolling │\n│ --- ┆ --- ┆ ---   ┆ ---           │\n│ str ┆ i64 ┆ i64   ┆ f64           │\n╞═════╪═════╪═══════╪═══════════════╡\n│ a   ┆ 1   ┆ 5     ┆ null          │\n│ a   ┆ 2   ┆ 1     ┆ 3.0           │\n│ a   ┆ 3   ┆ 3     ┆ 2.0           │\n│ b   ┆ 0   ┆ 4     ┆ null          │\n│ b   ┆ 1   ┆ 1     ┆ 2.5           │\n│ b   ┆ 2   ┆ 2     ┆ 1.5           │\n│ b   ┆ 3   ┆ 3     ┆ 2.5           │\n└─────┴─────┴───────┴───────────────┘\n```\n\n```py\nimport duckdb\n\nduckdb.sql(\"\"\"\n    select\n        *,\n        mean(sales) over (\n            partition by id \n            order by d\n            range between 1 preceding and 0 following\n        ) as sales_rolling \n    from df\n\"\"\").sort('id', 'd')\n```\n\n```text\n┌─────────┬───────┬───────┬───────────────┐\n│   id    │   d   │ sales │ sales_rolling │\n│ varchar │ int64 │ int64 │    double     │\n├─────────┼───────┼───────┼───────────────┤\n│ a       │     1 │     5 │           5.0 │\n│ a       │     2 │     1 │           3.0 │\n│ a       │     3 │     3 │           2.0 │\n│ b       │     0 │     4 │           4.0 │\n│ b       │     1 │     1 │           2.5 │\n│ b       │     2 │     2 │           1.5 │\n│ b       │     3 │     3 │           2.5 │\n└─────────┴───────┴───────┴───────────────┘\n```\n\n```text\nmin_periods=2\n```\n\n```text\n'id'\n```\n\n```text\nmin_periods=2\n```\n\n```py\nduckdb.sql(\"\"\"\n    from df\n    select\n        *,\n        case\n            when count(*) over rolling2 = 2 then \n                mean(sales) over rolling2\n        end as sales_rolling\n    window rolling2 as (\n        partition by id \n        order by d\n        rows between 1 preceding and current row\n    )   \n\"\"\").sort('id', 'd')\n\n┌─────────┬───────┬───────┬───────────────┐\n│   id    │   d   │ sales │ sales_rolling │\n│ varchar │ int64 │ int64 │    double     │\n├─────────┼───────┼───────┼───────────────┤\n│ a       │     1 │     5 │          NULL │\n│ a       │     2 │     1 │           3.0 │\n│ a       │     3 │     3 │           2.0 │\n│ b       │     0 │     4 │          NULL │\n│ b       │     1 │     1 │           2.5 │\n│ b       │     2 │     2 │           1.5 │\n│ b       │     3 │     3 │           2.5 │\n└─────────┴───────┴───────┴───────────────┘\n```\n\n```text\ncase\n```\n\n```text\ncount\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":11,"totalLines":193,"estimatedTokens":1058}}15{"id":"stack-78936413","source":"stackoverflow","questionId":78936413,"title":"DuckDB: how do I use the result of a query on `DESCRIBE` to `SELECT` from a table?","tags":["sql","duckdb"],"text":"Title: DuckDB: how do I use the result of a query on `DESCRIBE` to `SELECT` from a table?\nTags: sql, duckdb\nSource: Stack Overflow\n\nQuestion:\nSuppose I want to select all columns of a certain type from a DuckDB table. For example, selecting all `VARCHAR` type columns, after creating a table like:\n\n```\nCREATE TABLE dummy (x VARCHAR, y BIGINT, z VARCHAR);\nINSERT INTO dummy\nVALUES ('a', 0, 'a'),\n ('b', 1, 'b'),\n ('c', 2, 'c');\n```\n\nInngeneral, I might have an arbitrary number of `VARCHAR` type columns, so this query should be \"dynamic\". I get a list of the relevant columns using `DESCRIBE`:\n\n```\nSELECT column_name\nFROM (DESCRIBE dummy)\nWHERE column_type = 'VARCHAR';\n```\n\nThis statement gives me a list of the column names which have type `VARCHAR`. But how do I use this? I tried using the `COLUMNS` expression:\n\n```\nSELECT COLUMNS(\n c->c IN (\n SELECT column_name\n FROM (DESCRIBE dummy)\n WHERE column_type = 'VARCHAR'\n )\n )\nFROM dummy\n```\n\nBut this gives me the error: `BinderException: Binder Error: Table function cannot contain subqueries`. I don't really understand the error. I get the same error when trying:\n\n```\nSELECT COLUMNS(\n c->list_contains(\n (\n SELECT column_name\n FROM (DESCRIBE dummy)\n WHERE column_type = 'VARCHAR'\n ),\n c\n )\n )\nFROM dummy\n```\n\nHow do I connect the dots between getting a list of columns by querying `DESCRIBE tbl`, and then using that list to select from `tbl`?\n\n========================================\n\nTop Answer:\nTry to use the duckdb_columns() function since it provides metadata about the columns available in the DuckDB instance :\n\n```\nSELECT column_name\n FROM duckdb_columns()\n WHERE table_name = 'dummy' AND data_type = 'VARCHAR'\n```\n\nI am using Python to concatenate the column names into a single string, then used that string in the SELECT :\n\n```\nimport duckdb\n\ncon = duckdb.connect('your_database.db')\n\ncolumn_names = con.execute(\"\"\"\n SELECT column_name\n FROM duckdb_columns()\n WHERE table_name = 'dummy' AND data_type = 'VARCHAR';\n\"\"\").fetchall()\n\ncolumn_names = [col[0] for col in column_names]\n\ncolumns_string = ', '.join(column_names)\nquery = f\"SELECT {columns_string} FROM dummy;\"\n\nresult = con.execute(query).fetchdf()\n\nprint(result)\n```\n\nCheck @jqurious it may suits you well using SQL level variables.\n\n========================================\n\nCode:\n```sql\nCREATE TABLE dummy (x VARCHAR, y BIGINT, z VARCHAR);\nINSERT INTO dummy\nVALUES ('a', 0, 'a'),\n  ('b', 1, 'b'),\n  ('c', 2, 'c');\n```\n\n```text\nSELECT column_name\nFROM (DESCRIBE dummy)\nWHERE column_type = 'VARCHAR';\n```\n\n```text\nSELECT COLUMNS(\n    c->c IN (\n      SELECT column_name\n      FROM (DESCRIBE dummy)\n      WHERE column_type = 'VARCHAR'\n    )\n  )\nFROM dummy\n```\n\n```sql\nSELECT COLUMNS(\n    c->list_contains(\n      (\n        SELECT column_name\n        FROM (DESCRIBE dummy)\n        WHERE column_type = 'VARCHAR'\n      ),\n      c\n    )\n  )\nFROM dummy\n```\n\n```text\nVARCHAR\n```\n\n```text\nVARCHAR\n```\n\n```text\nDESCRIBE\n```\n\n```text\nVARCHAR\n```\n\n```text\nCOLUMNS\n```\n\n```text\nBinderException: Binder Error: Table function cannot contain subqueries\n```\n\n```text\nDESCRIBE tbl\n```\n\n```text\ntbl\n```\n\n```py\nduckdb.sql(\"\"\"\nSET VARIABLE VARCHAR_NAMES = (\n   SELECT LIST(column_name)\n   FROM (DESCRIBE dummy)\n   WHERE column_type = 'VARCHAR'\n)\n\"\"\")\n\nduckdb.sql(\"\"\"\nFROM DUMMY SELECT COLUMNS(x -> x in GETVARIABLE('VARCHAR_NAMES'))\n\"\"\")\n```\n\n```text\n┌─────────┬─────────┐\n│    x    │    z    │\n│ varchar │ varchar │\n├─────────┼─────────┤\n│ a       │ a       │\n│ b       │ b       │\n│ c       │ c       │\n└─────────┴─────────┘\n```\n\n```text\nSET VARIABLE\n```\n\n```text\nGETVARIABLE\n```\n\n```text\nSELECT column_name\n    FROM duckdb_columns()\n    WHERE table_name = 'dummy' AND data_type = 'VARCHAR'\n```\n\n```text\nimport duckdb\n\ncon = duckdb.connect('your_database.db')\n\ncolumn_names = con.execute(\"\"\"\n    SELECT column_name\n    FROM duckdb_columns()\n    WHERE table_name = 'dummy' AND data_type = 'VARCHAR';\n\"\"\").fetchall()\n\ncolumn_names = [col[0] for col in column_names]\n\ncolumns_string = ', '.join(column_names)\nquery = f\"SELECT {columns_string} FROM dummy;\"\n\nresult = con.execute(query).fetchdf()\n\nprint(result)\n```\n\n```text\n.once\n```\n\n```text\n.read\n```\n\n```text\nSELECT\n```\n\n```text\njson_execute_serialized_sql\n```\n\n========================================\n\nComments:\n- Try to use the duckdb_columns() function since it provides metadata about the columns available in the DuckDB instance.\n- That only gives me the column names though? I've discovered a few ways to get the column names (and now can add to it the one you shared, thank you), but I do not yet know how to use the column names in order to select from a table with said columns.\n- Do you mean that you want to retrieve data from the returned list of the columns ?\n- yes, that's what I am trying to do, and that's why I get the errors I described above.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":22,"totalLines":247,"estimatedTokens":1206}}16{"id":"stack-77797976","source":"stackoverflow","questionId":77797976,"title":"Working with large CSV file using duckdb or arrow in R","tags":["r","database","duckdb"],"text":"Title: Working with large CSV file using duckdb or arrow in R\nTags: r, database, duckdb\nSource: Stack Overflow\n\nQuestion:\nI know nothing about databases nor duckdb nor arrow---hence the struggle, likely.\nI have a large CSV file (7.5 GB) on my hard drive. In R, without loading it into my memory, I want to:\n\n- Extract column names\n\n- Select a subset of columns and rows\n\nAnd then load this subset dataset into R.\n\nHow can I do this? I am struggling to understand how to do this based on what I found online.\n\n========================================\n\nTop Answer:\nIf there is enough space on the hard disk, I would import the csv into a duckdb file using\n\n```\nlibrary(duckdb)\ncon `duckdb_read_csv` is really fast and uses multithreading I believe.\n\nNow you can use SQL to get information on the columns, e.g.\n\n```\nstmt and to select your rows, e.g.\n\n```\nstmt 0;\"\n)\ndat Don't forget to disconnect after use, e.g.\n\n```\nDBI::dbDisconnect(con, shutdown=TRUE)\n```\n\nEDIT:\n\"Tbh I have several large CSV that I need to filter all at once, do you reckon I could create a database with all of them (whatever that means) and do a massive select and filter exercise at once?\"\n\nYou could either call `duckdb::duckdb_read_csv` for each csv and perform the filtering afterwards. You would simply call `duckdb_read_csv` several times with the same `con` and `name` argument. Note that the structure of the CSVs (number, names, and types of the columns) needs to be the same.\n\nOr otherwise, if disk space is an issue, you could also read in one csv, and then perform the filtering as deletion inside the duckdb:\n\n```\nstmt 0;\")\nDBI::dbExecute(con, stmt)\n```\n\nThen you would read in the next csv. When all CSV are read, you could call `dbReadTable` to get the required data set:\n\n```\ndat <- DBI::dbReadTable(con, \"your_tbl\")\n```\n\n========================================\n\nCode:\n```text\ninstall.packages(\"pacman\")\npacman::p_load(duckdb, arrow, dbplyr)\n  \npath_to_csv <- \"c:/path/to/file.csv\"\n\n# Create a database from one local csv file (does not upload the data)\ndatabase <- arrow::open_dataset(source = path_to_csv, format = \"csv\") %>%\n   arrow::to_duckdb()\n\n# Final dataset\nclean_loaded_data <- database %>%\n   select(column_1) %>%\n   filter(item %in% list) %>%\n   collect()\n```\n\n```text\ncollect()\n```\n\n```text\nlibrary(duckdb)\ncon <- DBI::dbConnect(duckdb::duckdb(), \"your.duckdb\") # creates an empty file 'your.duckdb'\nduckdb::duckdb_read_csv(con, \"your_tbl\", \"your_1.csv\") # populates db from csv\nduckdb::duckdb_read_csv(con, \"your_tbl\", \"your_2.csv\") # append to the existing data\n```\n\n```text\nstmt <- paste0(\n    \"SELECT column_name, data_type FROM \",\n    \"information_schema.columns WHERE \",\n    \"table_name = 'your_tbl';\"\n)\nDBI::dbGetQuery(con, stmt)\n```\n\n```text\nstmt <- paste0(\n   \"SELECT your_col1, your_col2 FROM your_tbl \",\n   \"WHERE your_col3>0;\"\n)\ndat <- DBI::dbGetQuery(con, stmt)\n```\n\n```text\nDBI::dbDisconnect(con, shutdown=TRUE)\n```\n\n```text\nstmt <- paste0(\"DELETE FROM your_tbl WHERE your_col3>0;\")\nDBI::dbExecute(con, stmt)\n```\n\n```text\ndat <- DBI::dbReadTable(con, \"your_tbl\")\n```\n\n```text\nduckdb_read_csv\n```\n\n```text\nduckdb::duckdb_read_csv\n```\n\n```text\nduckdb_read_csv\n```\n\n```text\ncon\n```\n\n```text\nname\n```\n\n```text\ndbReadTable\n```\n\n```text\ncolumns = DBI::dbGetQuery(\"Describe read_csv_auto('path/to/file.csv')\")\n```\n\n```text\nstmt <- paste0(\n    \"COPY (SELECT your_col1, your_col2 FROM your_tbl \",\n    \"WHERE your_col3>0;) to 'my_csv_file.csv' (FORMAT CSV, HEADER 1);\"\n)\nDBI::dbGetQuery(con, stmt)\n```\n\n```text\ncon <- DBI::dbConnect(duckdb::duckdb(), \"persistent_db.duckdb\")\n...\nstmt <- paste0(\n    \"Create table my_table as (SELECT column_name, data_type FROM \",\n    \"information_schema.columns WHERE \",\n    \"table_name = 'your_tbl');\"\n)\nDBI::dbGetQuery(con, stmt)\n```\n\n```text\nselect\n```\n\n```text\nsearch\n```\n\n```text\nluau filter\n```\n\n```text\nindex\n```\n\n```text\nsearch\n```\n\n========================================\n\nComments:\n- You can select columns and number of rows when reading in, for example: `data.table:fread(\"myfile.txt\", select = 1:3, nrows = 10)`\n- Does it not read the whole CSV file first? If it does not, it could work for the columns but not for filtering based on conditions, it seems\n- Related post stackoverflow.com/q/58296934/680068 and stackoverflow.com/q/63134876/680068\n- nrows argument will read only specified number of to we, not a whole file. For filtering rows use grep.\n- In comments you mention you have several csv files, is it by any chance a partitioned dataset where directory structure is based on some variables like years and/or months, `2023&#47;01&#47;file.csv` ?\n- This approach seems great. I'm just missing a step. In line 2 of your code, you seem to be assuming that I have a .duckbd file, which I don't and don't know how to create. Could you explain that step better? Mind that I have been doing data analysis and statistical inference for years but do not know the first thing about databases. Tbh I have several large CSV that I need to filter all at once, do you reckon I could create a database with all of them (whatever that means) and do a massive select and filter exercise at once? Thanks a lot!!\n- Edited answer accordingly.\n- I will try to implement your approach again and see if it's faster than the one I suggested. I will award the answer based on that. In any case, thanks a lot!\n- This looks like an even simpler / more efficient solution.\n- Selected my answer as it is less verbose and uses dplyr syntax, which I am more familiar with compared to SQL-type syntax. Not sure if there is a performance difference between Karten's solution and mine.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":22,"totalLines":198,"estimatedTokens":1404}}17{"id":"stack-75805412","source":"stackoverflow","questionId":75805412,"title":"DuckDB: Truncate a timestamp to a 15 minute interval?","tags":["duckdb"],"text":"Title: DuckDB: Truncate a timestamp to a 15 minute interval?\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nIs there function or equivalent that will truncate a timestamp to 15 minute intervals, e.g. the logical equivalent of\n\n```\nselect date_trunc('15 minutes', timestamp)?\n```\n\n========================================\n\nCode:\n```text\nselect date_trunc('15 minutes', timestamp)?\n```\n\n```sql\n-- Truncate timestamps to N minute intervals. In this test, N=15.\nWITH my_table(timestamp_column) as (\nVALUES \n    (timestamp with time zone '2023-01-25T05:00:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:01:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:02:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:03:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:04:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:05:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:06:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:07:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:08:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:09:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:10:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:11:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:12:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:13:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:14:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:15:00+03:00'),\n    (timestamp with time zone '2023-01-25T05:16:00+03:00')\n)\nSELECT\n  timestamp_column,\n  time_bucket(interval '15 minutes', timestamp_column) as with_bucket,\n  date_trunc('hour', timestamp_column)\n    + (floor(date_part('minute', timestamp_column) / 15)::int\n        * interval '15 minute') AS like_postgres\nFROM my_table\nORDER BY timestamp_column;\n```\n\n```text\ntime_bucket(interval, timestamp)\n```\n\n```text\ntime_bucket\n```\n\n========================================\n\nComments:\n- epoch(time) does return The number of seconds since midnight, you can round that to a multiple of '15 minutes', and convert it back to time ?\n- BTW: Why did you not post the answers that you found for other DBMSs ? It could create a nice collection of info on this matter....\n- @Luuk, they were the first questions that showed up in the search, and I wanted to keep this question focused on DuckDB. It would be great to have a question that pointed to the appropriate solutions for each database!","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":64,"estimatedTokens":610}}18{"id":"stack-78875297","source":"stackoverflow","questionId":78875297,"title":"\"horizontal sum\" in DuckDB?","tags":["python","python-polars","duckdb"],"text":"Title: \"horizontal sum\" in DuckDB?\nTags: python, python-polars, duckdb\nSource: Stack Overflow\n\nQuestion:\nIn Polars I can do:\n\n```\nimport polars as pl\ndf = pl.DataFrame({'a': [1,2,3], 'b': [4, 5, 6]})\ndf.select(pl.sum_horizontal('a', 'b'))\n\nshape: (3, 1)\n┌─────┐\n│ a │\n│ --- │\n│ i64 │\n╞═════╡\n│ 5 │\n│ 7 │\n│ 9 │\n└─────┘\n```\n\nIs there a way to do this with DuckDB?\n\n========================================\n\nTop Answer:\nIf one wants to avoid explicitly specifying the columns,\nthen assuming all the columns in table t are integers, you can use the following technique, provided the JSON extension has been loaded, as by:\n\n```\nload JSON;\n```\n\n```\nselect list_sum(list_transform( json_keys(j), \n k -> json_extract(j,k)::INTEGER)) as sum \nfrom (select t::json j from t);\n```\n\nWith a bit of work, the assumptions can be weakened,\ne.g. non-integer values could be replaced by 0;\nor one could use the `EXCLUDE` feature to exclude certain columns, as by:\n\n```\nselect u::json j from (select * exclude(x) from t) as u;\n```\n\n### Example:\n\n```\nfrom t;\n┌───────┬───────┐\n│ a │ b │\n│ int32 │ int32 │\n├───────┼───────┤\n│ 1 │ 4 │\n│ 2 │ 5 │\n│ 3 │ 6 │\n└───────┴───────┘\n```\n\nOutput from the query shown above:\n\n```\n┌────────┐\n│ sum │\n│ int128 │\n├────────┤\n│ 5 │\n│ 7 │\n│ 9 │\n└────────┘\n```\n\n========================================\n\nCode:\n```py\nimport polars as pl\ndf = pl.DataFrame({'a': [1,2,3], 'b': [4, 5, 6]})\ndf.select(pl.sum_horizontal('a', 'b'))\n\nshape: (3, 1)\n┌─────┐\n│ a   │\n│ --- │\n│ i64 │\n╞═════╡\n│ 5   │\n│ 7   │\n│ 9   │\n└─────┘\n```\n\n```py\nduckdb.sql(\"\"\"\nfrom df\nselect \n  list_value(unpack(columns(*))).list_sum()\n\"\"\")\n```\n\n```py\n# ┌────────────────────────────────────────────┐\n# │ list_sum(list_value(a := df.a, b := df.b)) │\n# │                   int128                   │\n# ├────────────────────────────────────────────┤\n# │                                          5 │\n# │                                          7 │\n# │                                          9 │\n# └────────────────────────────────────────────┘\n```\n\n```py\nduckdb.sql(\"\"\"\nfrom df \nselect list_sum(list_value(*columns(*)))\n\"\"\")\n```\n\n```text\n┌──────────────────────────────────┐\n│ list_sum(list_value(df.a, df.b)) │\n│              int128              │\n├──────────────────────────────────┤\n│                                5 │\n│                                7 │\n│                                9 │\n└──────────────────────────────────┘\n```\n\n```text\nUNPACK()\n```\n\n```text\n*\n```\n\n```text\nCOLUMNS()\n```\n\n```text\nlist_*\n```\n\n```text\nload JSON;\n```\n\n```text\nselect list_sum(list_transform( json_keys(j), \n  k -> json_extract(j,k)::INTEGER)) as sum \nfrom (select t::json j from t);\n```\n\n```text\nselect u::json j from (select * exclude(x) from t) as u;\n```\n\n```text\nfrom t;\n┌───────┬───────┐\n│   a   │   b   │\n│ int32 │ int32 │\n├───────┼───────┤\n│     1 │     4 │\n│     2 │     5 │\n│     3 │     6 │\n└───────┴───────┘\n```\n\n```text\n┌────────┐\n│  sum   │\n│ int128 │\n├────────┤\n│      5 │\n│      7 │\n│      9 │\n└────────┘\n```\n\n```text\nEXCLUDE\n```\n\n========================================\n\nComments:\n- Isn't it just `select a + b` - or am I misunderstanding?\n- right, i can just do `rel.select('+'.join(rel.columns))`. that was easy. thanks!\n- I was looking for solution to sum any number of columns and completely missed that there're only 2 in the actual question.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":15,"totalLines":197,"estimatedTokens":831}}19{"id":"stack-78548905","source":"stackoverflow","questionId":78548905,"title":"Check CSV headers in IMPORT DATABASE","tags":["sql","r","duckdb"],"text":"Title: Check CSV headers in IMPORT DATABASE\nTags: sql, r, duckdb\nSource: Stack Overflow\n\nQuestion:\n`duckdb` comes with a `IMPORT DATABASE 'folder'` feature.\n\nFirst, a `schema.sql` is executed. This file contains a lot of `CREATE TABLE` statements.\n\nThen, a `load.sql` file is executed. This file contains a lot of `COPY` statements that import, e.g. CSV files.\n\nI wonder if I can verify that the headers of the CSV file are the same as the field names in the `CREATE TABLE` statements. I am using R, so a minimal example looks like this:\n\n```\n# write example schema.sql, load.sql, and iris.csv\n folder suppressWarnings()\n write.csv(iris, \"dbdump/iris.csv\", row.names=FALSE)\n \n writeLines(paste(\n \"CREATE TABLE iris(\",\n \" sepal_length DOUBLE,\",\n \" sepal_width DOUBLE,\",\n \" petal_length DOUBLE,\", \n \" petal_width DOUBLE,\", \n \" species VARCHAR);\", sep=\"\\n\"), \"dbdump/schema.sql\"\n )\n \n writeLines(\n \"COPY iris FROM 'iris.csv' (DELIMITER ',', HEADER);\", \n \"dbdump/load.sql\"\n )\n```\n\nNow I can import the data by\n\n```\nlibrary(duckdb)\n con If I check the column names,\n\n```\nDBI::dbReadTable(con, \"iris\") |> str()\n #'data.frame': 150 obs. of 5 variables:\n # $ sepal_length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...\n # $ sepal_width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...\n # $ petal_length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...\n # $ petal_width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...\n # $ species : chr \"setosa\" \"setosa\" \"setosa\" \"setosa\" ...\n```\n\nThe field names from the `CREATE TABLE` statement are used. However, the `iris.csv` has a different header:\n\n```\ncolnames(iris)\n # [1] \"Sepal.Length\" \"Sepal.Width\" \"Petal.Length\" \"Petal.Width\" \n # [5] \"Species\"\n```\n\nI wish I could get a warning in this case. Especially, I worry that the column order of the CSV is different from the `CREATE TABLE` statement and goes undetected if the type is the same.\n\nDo I have to program this check manually or is there a more convenient way?\n\n========================================\n\nCode:\n```php\n# write example schema.sql, load.sql, and iris.csv\n    folder <- tempdir()\n    curr_wd <- getwd()\n    #on.exit(setwd(curr_wd))\n    setwd(folder)\n    \n    dir.create(\"dbdump\") |> suppressWarnings()\n    write.csv(iris, \"dbdump/iris.csv\", row.names=FALSE)\n    \n    writeLines(paste(\n        \"CREATE TABLE iris(\",\n        \"  sepal_length DOUBLE,\",\n        \"  sepal_width DOUBLE,\",\n        \"  petal_length DOUBLE,\", \n        \"  petal_width DOUBLE,\", \n        \"  species VARCHAR);\", sep=\"\\n\"), \"dbdump/schema.sql\"\n    )\n    \n    writeLines(\n        \"COPY iris FROM 'iris.csv' (DELIMITER ',', HEADER);\", \n        \"dbdump/load.sql\"\n    )\n```\n\n```php\nlibrary(duckdb)\n    con <- DBI::dbConnect(drv=duckdb::duckdb(), dbdir=\":memory:\")\n    #on.exit(DBI::dbDisconnect(con, shutdown=TRUE))\n    DBI::dbExecute(con, \"IMPORT DATABASE 'dbdump';\")\n```\n\n```php\nDBI::dbReadTable(con, \"iris\") |> str()\n    #'data.frame':   150 obs. of  5 variables:\n    # $ sepal_length: num  5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...\n    # $ sepal_width : num  3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...\n    # $ petal_length: num  1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...\n    # $ petal_width : num  0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...\n    # $ species     : chr  \"setosa\" \"setosa\" \"setosa\" \"setosa\" ...\n```\n\n```php\ncolnames(iris)\n    # [1] \"Sepal.Length\" \"Sepal.Width\"  \"Petal.Length\" \"Petal.Width\" \n    # [5] \"Species\"\n```\n\n```text\nduckdb\n```\n\n```text\nIMPORT DATABASE 'folder'\n```\n\n```text\nschema.sql\n```\n\n```text\nCREATE TABLE\n```\n\n```text\nload.sql\n```\n\n```text\nCOPY\n```\n\n```text\nCREATE TABLE\n```\n\n```text\nCREATE TABLE\n```\n\n```text\niris.csv\n```\n\n```text\nCREATE TABLE\n```\n\n```text\nwriteLines(paste(\n    \"stopifnot(\",\n    \" all(colnames(read.csv('dbdump/iris.csv', nrows=1)) ==\",\n    \" c('sepal_length', 'sepal_width', 'petal_length', 'petal_width', 'species'))\",\n    \")\", sep=\"\\n\"), \"dbdump/check_headers.R\"\n)\n```\n\n```text\nsource(\"dbdump/check_headers.R\")\n```\n\n```text\nwriteLines(paste(\n    \"COPY iris(sepal_length, sepal_width, petal_length, petal_width, species)\",\n    \"FROM 'iris.csv' (DELIMITER ',', HEADER);\"), \"dbdump/load.sql\"\n)\n```\n\n```text\nIMPORT DATABASE\n```\n\n```text\nload.sql\n```\n\n========================================\n\nComments:\n- Table is already created with new column names, \"HEADER\" inside COPY just means \"there is a header ignore 1st row\". I think we will need to have a manual check for correct overlap of column names.\n- Check out github.com/duckdb/duckdb/issues/3506","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":19,"totalLines":185,"estimatedTokens":1118}}20{"id":"stack-72969276","source":"stackoverflow","questionId":72969276,"title":"How can I write raw binary data to duckdb from R?","tags":["sql","r","duckdb"],"text":"Title: How can I write raw binary data to duckdb from R?\nTags: sql, r, duckdb\nSource: Stack Overflow\n\nQuestion:\nMy best guess is that this simply isn't currently supported by the `{duckdb}` package, however I'm not sure if I'm doing something wrong/not in the in the intended way. Here's a reprex which reproduces the (fairly self-explanatory) issue:\n\n```\ncon Error: rapi_execute: Unsupported column type for scan\n#> Error: rapi_register_df: Failed to register data frame: std::exception\n```\n\nNB (1), I'm trying to find a way to write arbitrary R objects to SQL. To do this, I plan to serialise the objects in question to binary format, write to SQL, read back and unserialise. I also want to find a method that works reliably with as many SQL backends as possible, as I'm planning to create a package which allows the user to specify the connection.\n\nNB (2), I've posted this as an issue on the `duckdb` github as I have a feeling this is simply a bug/not yet a supported feature.\n\n### Edit #1\n\nI'm now more convinced that this is simply a bug with `{duckdb}`. From the documentation for `DBI::dbDataType()`:\n\nIf the backend needs to override this generic, it must accept all basic R data types as its second argument, namely logical, integer, numeric, character, dates (see Dates), date-time (see DateTimeClasses), and difftime. If the database supports blobs, this method also must accept lists of raw vectors, and `blob::blob` objects.\n\n`duckdb` certainly supports `blob` types, so as far as I can see, these objects should be writeable. Note, this code produces the same issue outlined above (using `blob::blob()` instead of `I(list())`:\n\n```\nDBI::dbAppendTable(\n conn = con,\n name = \"raw_test\",\n value = data.frame(file = blob::blob(as.raw(1:3))),\n field.types = list(file = \"blob\")\n)\n\n#> Error: rapi_execute: Unsupported column type for scan\n#> Error: rapi_register_df: Failed to register data frame: std::exception\n```\n\nI'm leaving this open for now in case any kindly `duckdb` dev can confirm this is a bug/missing feature, or if anyone can suggest a workaround.\n\n========================================\n\nCode:\n```text\ncon <- DBI::dbConnect(duckdb::duckdb())\n\n# Note: this connection would work fine\n# con <- DBI::dbConnect(RSQLite::SQLite())\n\nDBI::dbCreateTable(\n  conn = con,\n  name = \"raw_test\",\n  fields = list(file = \"blob\")\n)\n\nDBI::dbAppendTable(\n  conn = con,\n  name = \"raw_test\",\n  value = data.frame(file = I(list(as.raw(1:3)))),\n  field.types = list(file = \"blob\")\n)\n\n#> Error: rapi_execute: Unsupported column type for scan\n#> Error: rapi_register_df: Failed to register data frame: std::exception\n```\n\n```text\nDBI::dbAppendTable(\n    conn = con,\n    name = \"raw_test\",\n    value = data.frame(file = blob::blob(as.raw(1:3))),\n    field.types = list(file = \"blob\")\n)\n\n#> Error: rapi_execute: Unsupported column type for scan\n#> Error: rapi_register_df: Failed to register data frame: std::exception\n```\n\n```text\n{duckdb}\n```\n\n```text\nduckdb\n```\n\n```text\n{duckdb}\n```\n\n```text\nDBI::dbDataType()\n```\n\n```text\nblob::blob\n```\n\n```text\nduckdb\n```\n\n```text\nblob\n```\n\n```text\nblob::blob()\n```\n\n```text\nI(list())\n```\n\n```text\nduckdb\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":12,"totalLines":115,"estimatedTokens":787}}21{"id":"stack-78946585","source":"stackoverflow","questionId":78946585,"title":"Is there a way to use column list in group by clause in DuckDB?","tags":["sql","duckdb"],"text":"Title: Is there a way to use column list in group by clause in DuckDB?\nTags: sql, duckdb\nSource: Stack Overflow\n\nQuestion:\nSuppose that I have the following data\n\n```\nCREATE TABLE sample_table (\n YEAR INTEGER,\n BRAND VARCHAR, \n PRODUCT VARCHAR, \n SALES INTEGER \n);\n\nINSERT INTO sample_table (YEAR, BRAND, PRODUCT, SALES) VALUES\n(2023, 'AX', 'A', 10),\n(2024, 'AX', 'A', 20),\n(2024, 'AX', 'B', 70),\n(2022, 'AY', 'C', 20),\n(2023, 'AY', 'C', 90),\n;\n```\n\nIs there a way to create a macro to achieve the same result below where I can just use Brand and Product as list arguments\n\n```\nSELECT YEAR BRAND, PRODUCT, SUM(SALES) FROM SAMPLE_TABLE \n GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));\n\n───────┬─────────┬────────────┐\n│ BRAND │ PRODUCT │ sum(SALES) │\n│ int32 │ varchar │ int128 │\n├───────┼─────────┼────────────┤\n│ 2024 │ │ 90 │\n│ 2022 │ │ 20 │\n│ 2022 │ C │ 20 │\n│ 2022 │ │ 20 │\n│ 2023 │ │ 90 │\n│ 2023 │ │ 100 │\n│ 2023 │ A │ 10 │\n│ 2024 │ B │ 70 │\n│ 2023 │ C │ 90 │\n│ 2023 │ │ 10 │\n│ 2024 │ │ 90 │\n│ 2024 │ B │ 70 │\n│ 2024 │ A │ 20 │\n│ 2023 │ C │ 90 │\n│ 2023 │ A │ 10 │\n│ 2024 │ A │ 20 │\n│ 2022 │ C │ 20 │\n├───────┴─────────┴────────────┤\n│ 17 rows 3 columns\n```\n\nWhat I had in mind is\n\n```\nCREATE OR REPLACE MACRO MSUM(\n GRPCOLS\n ) AS TABLE (\n FROM TBL\n SELECT \n COLUMNS(C -> (LIST_CONTAINS(GRPCOLS, C))),\n SUM(SALES)\n GROUP BY YEAR, GROUPING SETS(CUBE(COLUMNS(C -> LIST(CONTAINS(GRPCOLS, C)))))\n );\n\nWITH TBL AS (SELECT * FROM SAMPLE_TABLE)\n FROM MSUM([BRAND, PRODUCT]);\n```\n\nbut it can't be done because if I understood it right `COLUMNS` is an star expression and can't be used in `GROUP BY`\n\n```\nBinder Error: STAR expression is not supported here\n```\n\nAny ideas?\n\n========================================\n\nCode:\n```text\nCREATE TABLE sample_table (\n    YEAR INTEGER,\n    BRAND VARCHAR, \n    PRODUCT VARCHAR,  \n    SALES INTEGER     \n);\n\nINSERT INTO sample_table (YEAR, BRAND, PRODUCT, SALES) VALUES\n(2023, 'AX', 'A', 10),\n(2024, 'AX', 'A', 20),\n(2024, 'AX', 'B', 70),\n(2022, 'AY', 'C', 20),\n(2023, 'AY', 'C', 90),\n;\n```\n\n```text\nSELECT YEAR BRAND, PRODUCT, SUM(SALES) FROM SAMPLE_TABLE \n  GROUP BY YEAR, GROUPING SETS(CUBE(BRAND, PRODUCT));\n\n───────┬─────────┬────────────┐\n│ BRAND │ PRODUCT │ sum(SALES) │\n│ int32 │ varchar │   int128   │\n├───────┼─────────┼────────────┤\n│  2024 │         │         90 │\n│  2022 │         │         20 │\n│  2022 │ C       │         20 │\n│  2022 │         │         20 │\n│  2023 │         │         90 │\n│  2023 │         │        100 │\n│  2023 │ A       │         10 │\n│  2024 │ B       │         70 │\n│  2023 │ C       │         90 │\n│  2023 │         │         10 │\n│  2024 │         │         90 │\n│  2024 │ B       │         70 │\n│  2024 │ A       │         20 │\n│  2023 │ C       │         90 │\n│  2023 │ A       │         10 │\n│  2024 │ A       │         20 │\n│  2022 │ C       │         20 │\n├───────┴─────────┴────────────┤\n│ 17 rows            3 columns\n```\n\n```text\nCREATE OR REPLACE MACRO MSUM(\n    GRPCOLS\n    ) AS TABLE (\n    FROM TBL\n    SELECT \n        COLUMNS(C -> (LIST_CONTAINS(GRPCOLS, C))),\n        SUM(SALES)\n    GROUP BY YEAR, GROUPING SETS(CUBE(COLUMNS(C -> LIST(CONTAINS(GRPCOLS, C)))))\n    );\n\nWITH TBL AS (SELECT * FROM SAMPLE_TABLE)\n    FROM MSUM([BRAND, PRODUCT]);\n```\n\n```text\nBinder Error: STAR expression is not supported here\n```\n\n```text\nCOLUMNS\n```\n\n```text\nGROUP BY\n```\n\n```py\nduckdb.sql(\"\"\"\ncreate or replace macro msum(tbl, grpcols) as table (\n   from query(format(\n      '\n      from {0}\n      select {1}, sum(sales)\n      group by year, grouping sets(cube({1}))\n      ', \n      tbl, \n      array_to_string(grpcols, ',')\n   ))\n)\n\"\"\")\n```\n\n```py\nduckdb.sql(\"\"\"\nfrom msum(sample_table, [brand, product])\n\"\"\")\n```\n\n```text\n┌─────────┬─────────┬────────────┐\n│  BRAND  │ PRODUCT │ sum(sales) │\n│ varchar │ varchar │   int128   │\n├─────────┼─────────┼────────────┤\n│ AY      │ NULL    │         20 │\n│ AY      │ C       │         20 │\n│ AY      │ NULL    │         90 │\n│ NULL    │ B       │         70 │\n│ NULL    │ NULL    │         20 │\n│ AX      │ NULL    │         10 │\n│ AX      │ NULL    │         90 │\n│ NULL    │ A       │         10 │\n│ NULL    │ A       │         20 │\n│ NULL    │ C       │         20 │\n│ NULL    │ NULL    │         90 │\n│ AX      │ A       │         20 │\n│ NULL    │ C       │         90 │\n│ NULL    │ NULL    │        100 │\n│ AX      │ A       │         10 │\n│ AX      │ B       │         70 │\n│ AY      │ C       │         90 │\n├─────────┴─────────┴────────────┤\n│ 17 rows              3 columns │\n└────────────────────────────────┘\n```\n\n```text\nquery()\n```\n\n========================================\n\nComments:\n- Great solution. Would be possible in the macro call to use something like `SELECT * FROM 'SAMPLE_TABLE.CSV'`supposing that the table is saved in a file in disk or the table must already exist in memory? I tried it some ways reading the json page but none worked\n- @Rooh You can pass a subquery as a string e.g. `from msum('(from ''SAMPLE_TABLE.csv'')', [brand, product])`\n- @jqurious so is `json_execute_serialized_sql` a way of executing dynamic sql in duckdb? or are there other ways?\n- @RomanPekar It is a way. `query()` and `query_table()` are also in nightly. github.com/duckdb/duckdb/pull/10586 - there is a lack of docs/examples on the whole topic.","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":10,"totalLines":216,"estimatedTokens":1320}}22{"id":"stack-71467792","source":"stackoverflow","questionId":71467792,"title":"How to import a .sql file into DuckDB database?","tags":["python","duckdb"],"text":"Title: How to import a .sql file into DuckDB database?\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm exploring DuckDB for one of my project.\n\nHere I have a sample Database file downloaded from https://www.wiley.com/en-us/SQL+for+Data+Scientists%3A+A+Beginner%27s+Guide+for+Building+Datasets+for+Analysis-p-9781119669364\n\nI'm trying to import **FarmersMarketDatabase** into my DuckDB database.\n\n```\ncon.execute(\"IMPORT DATABASE 'FarmersMarketDatabase'\")\n```\n\nIt throws out an error as:\n\n```\nRuntimeError: IO Error: Cannot open file \"FarmersMarketDatabase\\schema.sql\": The system cannot find the path specified.\n```\n\nHow to load the databases into DuckDB?\n\n========================================\n\nTop Answer:\nI am not sure if you can load .sql file into DuckDB directly. Maybe you can first export sqlite database to CSV file(s). Then load CSV file(s) into DuckDB: https://duckdb.org/docs/data/csv.\n\n========================================\n\nCode:\n```text\ncon.execute(\"IMPORT DATABASE 'FarmersMarketDatabase'\")\n```\n\n```text\nRuntimeError: IO Error: Cannot open file \"FarmersMarketDatabase\\schema.sql\": The system cannot find the path specified.\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":2,"totalLines":38,"estimatedTokens":290}}23{"id":"stack-79747544","source":"stackoverflow","questionId":79747544,"title":"Inserting to a (temp) table from an insert statement with returning clause in duckdb","tags":["python","duckdb","executemany"],"text":"Title: Inserting to a (temp) table from an insert statement with returning clause in duckdb\nTags: python, duckdb, executemany\nSource: Stack Overflow\n\nQuestion:\nI am working with inserting and manipulating data in a DuckDB database using Python. The incoming data is staged in several temporary tables, before it is moved to permanent tables (largely for duplication checking). From an INSERT statement with a RETURNING clause I need to insert the returned values (primary key produced by the insert statement plus some business keys) into a temporary \"mapping\" table used for mapping the new primary key from the permanent table, to some of the data in the other temporary tables. Because I am dealing with large amounts of data at once (10,000s of rows) I'd prefer to not return, fetch, and insert separately.\n\nBelow is the code I attempted to use:\n\n```\nimport duckdb\n\nconn = duckdb.connect(\"duckdb.db\")\nconn.execute(\n \"\"\" \n CREATE SEQUENCE seq_id START 1;\n CREATE TABLE Data(\n id INTEGER DEFAULT nextval('seq_id') PRIMARY KEY,\n col1 VARCHAR,\n col2 INTEGER,\n col3 VARCHAR,\n col4 VARCHAR);\"\"\"\n)\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE Data_temp(\n col1 VARCHAR,\n col2 INTEGER,\n col3 VARCHAR,\n col4 VARCHAR);\"\"\"\n)\n\nconn.execute(\n \"\"\"\n CREATE TABLE Other_data(\n id INTEGER REFERENCES DATA(id),\n cola FLOAT,\n colb FLOAT);\"\"\"\n)\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE Other_data_temp(\n cola FLOAT,\n colb FLOAT,\n col1 VARCHAR,\n col2 INTEGER);\"\"\"\n)\n\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE temp_mapping(\n id INTEGER,\n col1 VARCHAR,\n col2 INTEGER);\"\"\"\n)\n\ninput_data = [\n [\"green\", 3, \"round\", \"sweet\"],\n [\"red\", 3, \"square\", \"sweet\"],\n [\"blue\", 2, \"square\", \"bitter\"],\n]\n\ninput_other_data = [\n [1.43, 4.23, \"green\", 3],\n [6.45, 9.0, \"red\", 3],\n [4.8, 0.2, \"blue\", 2],\n]\n\nconn.executemany(\n \"\"\"\n INSERT INTO Data_temp(col1, col2, col3, col4)\n VALUES (?,?,?,?);\"\"\",\n input_data,\n)\n\nconn.executemany(\n \"\"\"\n INSERT INTO Other_data_temp\n VALUES (?,?,?,?);\"\"\",\n input_other_data,\n)\n\nmapping = conn.execute(\n \"\"\"\n INSERT INTO Data(\n col1, col2, col3, col4)\n SELECT \n T.col1, T.col2, T.col3, T.col4\n FROM Data_temp AS T\n RETURNING id, col1, col2;\"\"\"\n)\n\nconn.executemany(\n \"\"\"\n INSERT INTO temp_mapping(\n id, col1, col2) \n VALUES (?, ?, ?);\"\"\",\n mapping,\n)\n\n### Exception: duckdb.duckdb.InvalidInputException: Invalid Input Error: executemany requires a list of parameter sets to be provided\n\nconn.execute(\n \"\"\"\n INSERT INTO Other_data(id, cola, colb)\n SELECT TM.id, ODT.cola, ODT.colb\n FROM Other_data_temp AS ODT\n JOIN temp_mapping AS TM\n ON TM.col1 = ODT.col1\n AND TM.col2 = ODT.col2;\n\"\"\"\n)\nconn.commit()\ncount = conn.execute(\"SELECT COUNT(*) FROM Other_data;\")\n\nprint(count.fetchall()[0][0])\n\nconn.close()\n```\n\nI have an SQLite version of the code (near identical, except how the autogenerated primary key is handled), where I can pass the RETURNING cursor object directly as input for executemany, which works perfectly:\n\n```\nimport sqlite3\n\nconn = sqlite3.connect(\"sqlite.db\")\nconn.execute(\n \"\"\" \n CREATE TABLE Data(\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n col1 VARCHAR,\n col2 INTEGER,\n col3 VARCHAR,\n col4 VARCHAR);\"\"\"\n)\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE Data_temp(\n col1 VARCHAR,\n col2 INTEGER,\n col3 VARCHAR,\n col4 VARCHAR);\"\"\"\n)\n\nconn.execute(\n \"\"\"\n CREATE TABLE Other_data(\n id INTEGER REFERENCES DATA(id),\n cola FLOAT,\n colb FLOAT);\"\"\"\n)\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE Other_data_temp(\n cola FLOAT,\n colb FLOAT,\n col1 VARCHAR,\n col2 INTEGER);\"\"\"\n)\n\nconn.execute(\n \"\"\"\n CREATE TEMP TABLE temp_mapping(\n id INTEGER,\n col1 VARCHAR,\n col2 INTEGER);\"\"\"\n)\n\ninput_data = [\n [\"green\", 3, \"round\", \"sweet\"],\n [\"red\", 3, \"square\", \"sweet\"],\n [\"blue\", 2, \"square\", \"bitter\"],\n]\n\ninput_other_data = [\n [1.43, 4.23, \"green\", 3],\n [6.45, 9.0, \"red\", 3],\n [4.8, 0.2, \"blue\", 2],\n]\n\nconn.executemany(\n \"\"\"\n INSERT INTO Data_temp(col1, col2, col3, col4)\n VALUES (?,?,?,?);\"\"\",\n input_data,\n)\n\nconn.executemany(\n \"\"\"\n INSERT INTO Other_data_temp\n VALUES (?,?,?,?);\"\"\",\n input_other_data,\n)\n\nmapping = conn.execute(\n \"\"\"\n INSERT INTO Data(\n col1, col2, col3, col4)\n SELECT \n T.col1, T.col2, T.col3, T.col4\n FROM Data_temp AS T\n RETURNING id, col1, col2;\"\"\"\n)\n\nconn.executemany(\n \"\"\"\n INSERT INTO temp_mapping(\n id, col1, col2) \n VALUES (?, ?, ?);\"\"\",\n mapping,\n)\nconn.execute(\n \"\"\"\n INSERT INTO Other_data(id, cola, colb)\n SELECT TM.id, ODT.cola, ODT.colb\n FROM Other_data_temp AS ODT\n JOIN temp_mapping AS TM\n ON TM.col1 = ODT.col1\n AND TM.col2 = ODT.col2;\n\"\"\"\n)\nconn.commit()\ncount = conn.execute(\"SELECT COUNT(*) FROM Other_data;\")\n\nprint(count.fetchall()[0][0])\n# 3\nconn.close()\n```\n\nAre there any alternatives to this in DuckDB, one where I can avoid fetching the mapping data into memory first?\n\n========================================\n\nCode:\n```text\nimport duckdb\n\nconn = duckdb.connect(\"duckdb.db\")\nconn.execute(\n    \"\"\" \n   CREATE SEQUENCE seq_id START 1;\n   CREATE TABLE Data(\n      id INTEGER DEFAULT nextval('seq_id') PRIMARY KEY,\n      col1 VARCHAR,\n      col2 INTEGER,\n      col3 VARCHAR,\n      col4 VARCHAR);\"\"\"\n)\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE Data_temp(\n      col1 VARCHAR,\n      col2 INTEGER,\n      col3 VARCHAR,\n      col4 VARCHAR);\"\"\"\n)\n\nconn.execute(\n    \"\"\"\n   CREATE TABLE Other_data(\n      id INTEGER REFERENCES DATA(id),\n      cola FLOAT,\n      colb FLOAT);\"\"\"\n)\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE Other_data_temp(\n      cola FLOAT,\n      colb FLOAT,\n      col1 VARCHAR,\n      col2 INTEGER);\"\"\"\n)\n\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE temp_mapping(\n      id   INTEGER,\n      col1 VARCHAR,\n      col2 INTEGER);\"\"\"\n)\n\ninput_data = [\n    [\"green\", 3, \"round\", \"sweet\"],\n    [\"red\", 3, \"square\", \"sweet\"],\n    [\"blue\", 2, \"square\", \"bitter\"],\n]\n\ninput_other_data = [\n    [1.43, 4.23, \"green\", 3],\n    [6.45, 9.0, \"red\", 3],\n    [4.8, 0.2, \"blue\", 2],\n]\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO Data_temp(col1, col2, col3, col4)\n   VALUES (?,?,?,?);\"\"\",\n    input_data,\n)\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO Other_data_temp\n   VALUES (?,?,?,?);\"\"\",\n    input_other_data,\n)\n\nmapping = conn.execute(\n    \"\"\"\n   INSERT INTO Data(\n      col1, col2, col3, col4)\n   SELECT \n      T.col1, T.col2, T.col3, T.col4\n   FROM Data_temp AS T\n   RETURNING id, col1, col2;\"\"\"\n)\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO temp_mapping(\n      id, col1, col2) \n   VALUES (?, ?, ?);\"\"\",\n    mapping,\n)\n\n### Exception: duckdb.duckdb.InvalidInputException: Invalid Input Error: executemany requires a list of parameter sets to be provided\n\n\nconn.execute(\n    \"\"\"\n   INSERT INTO Other_data(id, cola, colb)\n   SELECT TM.id, ODT.cola, ODT.colb\n   FROM Other_data_temp AS ODT\n   JOIN temp_mapping AS TM\n      ON TM.col1 = ODT.col1\n      AND TM.col2 = ODT.col2;\n\"\"\"\n)\nconn.commit()\ncount = conn.execute(\"SELECT COUNT(*) FROM Other_data;\")\n\nprint(count.fetchall()[0][0])\n\nconn.close()\n```\n\n```text\nimport sqlite3\n\nconn = sqlite3.connect(\"sqlite.db\")\nconn.execute(\n    \"\"\" \n   CREATE TABLE Data(\n      id INTEGER PRIMARY KEY AUTOINCREMENT,\n      col1 VARCHAR,\n      col2 INTEGER,\n      col3 VARCHAR,\n      col4 VARCHAR);\"\"\"\n)\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE Data_temp(\n      col1 VARCHAR,\n      col2 INTEGER,\n      col3 VARCHAR,\n      col4 VARCHAR);\"\"\"\n)\n\nconn.execute(\n    \"\"\"\n   CREATE TABLE Other_data(\n      id INTEGER REFERENCES DATA(id),\n      cola FLOAT,\n      colb FLOAT);\"\"\"\n)\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE Other_data_temp(\n      cola FLOAT,\n      colb FLOAT,\n      col1 VARCHAR,\n      col2 INTEGER);\"\"\"\n)\n\nconn.execute(\n    \"\"\"\n   CREATE TEMP TABLE temp_mapping(\n      id   INTEGER,\n      col1 VARCHAR,\n      col2 INTEGER);\"\"\"\n)\n\ninput_data = [\n    [\"green\", 3, \"round\", \"sweet\"],\n    [\"red\", 3, \"square\", \"sweet\"],\n    [\"blue\", 2, \"square\", \"bitter\"],\n]\n\ninput_other_data = [\n    [1.43, 4.23, \"green\", 3],\n    [6.45, 9.0, \"red\", 3],\n    [4.8, 0.2, \"blue\", 2],\n]\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO Data_temp(col1, col2, col3, col4)\n   VALUES (?,?,?,?);\"\"\",\n    input_data,\n)\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO Other_data_temp\n   VALUES (?,?,?,?);\"\"\",\n    input_other_data,\n)\n\nmapping = conn.execute(\n    \"\"\"\n   INSERT INTO Data(\n      col1, col2, col3, col4)\n   SELECT \n      T.col1, T.col2, T.col3, T.col4\n   FROM Data_temp AS T\n   RETURNING id, col1, col2;\"\"\"\n)\n\nconn.executemany(\n    \"\"\"\n   INSERT INTO temp_mapping(\n      id, col1, col2) \n   VALUES (?, ?, ?);\"\"\",\n    mapping,\n)\nconn.execute(\n    \"\"\"\n   INSERT INTO Other_data(id, cola, colb)\n   SELECT TM.id, ODT.cola, ODT.colb\n   FROM Other_data_temp AS ODT\n   JOIN temp_mapping AS TM\n      ON TM.col1 = ODT.col1\n      AND TM.col2 = ODT.col2;\n\"\"\"\n)\nconn.commit()\ncount = conn.execute(\"SELECT COUNT(*) FROM Other_data;\")\n\nprint(count.fetchall()[0][0])\n# 3\nconn.close()\n```\n\n```py\nmapping = conn.execute(\n    \"\"\"\n   INSERT INTO Data\n   …\n   RETURNING id, col1, col2;\"\"\"\n).fetchall()\n```\n\n```py\nmapping = conn.execute(\n    \"\"\"\n   INSERT INTO Data\n   …\n   RETURNING id, col1, col2;\"\"\"\n)\n```\n\n```sql\nSELECT * FROM (\n   INSERT INTO Data\n   …\n   RETURNING id, col1, col2\n) AS x;\n```\n\n```sql\nWITH i AS (\n   INSERT INTO Data\n   …\n   RETURNING id, col1, col2\n)\nSELECT * FROM i;\n```\n\n```text\n.fetchall()\n```\n\n```text\nconn.execute(\"\"\"INSERT INTO Data … RETURNING …;\"\"\")\n```\n\n```text\nSELECT\n```\n\n```text\nSELECT\n```\n\n```text\nINSERT … RETURNING\n```\n\n```text\nINSERT\n```\n\n```text\nSELECT\n```\n\n```text\nSELECT\n```\n\n```text\nINSERT … RETURNING\n```\n\n```text\nexecute\n```\n\n```text\nSELECT\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.975Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":17,"totalLines":531,"estimatedTokens":2344}}24{"id":"stack-75671499","source":"stackoverflow","questionId":75671499,"title":"DuckDB Binder Error: Referenced column not found in FROM clause","tags":["python","sql","duckdb"],"text":"Title: DuckDB Binder Error: Referenced column not found in FROM clause\nTags: python, sql, duckdb\nSource: Stack Overflow\n\nQuestion:\nI am working in DuckDB in a database that I read from json.\n\nHere is the json:\n\n```\n[{\n \"account\": \"abcde\",\n \"data\": [\n {\n \"name\": \"hey\",\n \"amount\":1,\n \"flow\":\"INFLOW\"\n },\n {\n \"name\": \"hello\",\n \"amount\":-2,\n \"flow\": null\n }\n ]\n},\n{\n \"account\": \"hijkl\",\n \"data\": [\n {\n \"name\": \"bonjour\",\n \"amount\":1,\n \"flow\":\"INFLOW\"\n },\n {\n \"name\": \"hallo\",\n \"amount\":-3,\n \"flow\":\"OUTFLOW\"\n }\n ]\n}\n]\n```\n\nI am opening it in Python as follows:\n\n```\nimport duckdb\n\nduckdb.sql(\"\"\"\nCREATE OR REPLACE TABLE mytable AS SELECT * FROM \"example2.json\"\n\"\"\")\n```\n\nThis all works fine and I get a copy of my table, but then I try to update it:\n\n```\nduckdb.sql(\"\"\"\nUPDATE mytable SET data = NULL WHERE account = \"abcde\"\n\"\"\")\n```\n\nwhich crashes with\n\n```\n---------------------------------------------------------------------------\nBinderException Traceback (most recent call last)\nCell In[109], line 1\n----> 1 duckdb.sql(\"\"\"\n 2 UPDATE mytable SET data = NULL WHERE account = \"abcde\"\n 3 \"\"\")\n 6 # duckdb.sql(\"\"\"\n 7 # DELETE FROM mytable WHERE account = \"abcde\"\n 8 # \"\"\")\n 10 duckdb.sql(\"\"\"\n 11 SELECT * FROM mytable\n 12 \"\"\")\n\nBinderException: Binder Error: Referenced column \"abcde\" not found in FROM clause!\nCandidate bindings: \"mytable.data\"\nLINE 2: ...mytable SET data = NULL WHERE account = \"abcde\"\n ^\n```\n\nI have searched the documentation and the error but I just can't find what I am doing wrong here.\n\n========================================\n\nTop Answer:\nWhen dealing with varchar in DuckDB, use single quotes '' instead of double quotes \"\".\n\n========================================\n\nCode:\n```json\n[{\n    \"account\": \"abcde\",\n    \"data\": [\n        {\n            \"name\": \"hey\",\n            \"amount\":1,\n            \"flow\":\"INFLOW\"\n        },\n        {\n            \"name\": \"hello\",\n            \"amount\":-2,\n            \"flow\": null\n        }\n    ]\n},\n{\n    \"account\": \"hijkl\",\n    \"data\": [\n        {\n            \"name\": \"bonjour\",\n            \"amount\":1,\n            \"flow\":\"INFLOW\"\n        },\n        {\n            \"name\": \"hallo\",\n            \"amount\":-3,\n            \"flow\":\"OUTFLOW\"\n        }\n    ]\n}\n]\n```\n\n```py\nimport duckdb\n\nduckdb.sql(\"\"\"\nCREATE OR REPLACE TABLE mytable AS SELECT * FROM \"example2.json\"\n\"\"\")\n```\n\n```py\nduckdb.sql(\"\"\"\nUPDATE mytable SET data = NULL WHERE account = \"abcde\"\n\"\"\")\n```\n\n```py\n---------------------------------------------------------------------------\nBinderException                           Traceback (most recent call last)\nCell In[109], line 1\n----> 1 duckdb.sql(\"\"\"\n      2 UPDATE mytable SET data = NULL WHERE account = \"abcde\"\n      3 \"\"\")\n      6 # duckdb.sql(\"\"\"\n      7 # DELETE FROM mytable WHERE account = \"abcde\"\n      8 # \"\"\")\n     10 duckdb.sql(\"\"\"\n     11 SELECT * FROM mytable\n     12 \"\"\")\n\nBinderException: Binder Error: Referenced column \"abcde\" not found in FROM clause!\nCandidate bindings: \"mytable.data\"\nLINE 2: ...mytable SET data = NULL WHERE account = \"abcde\"\n                                                  ^\n```\n\n```py\nduckdb.sql(\"\"\"\nUPDATE mytable SET data = NULL WHERE account = 'abcde'\n\"\"\")\n```\n\n```py\n┌─────────┬──────────────────────────────────────────────────────────────────────────────────────────────────┐\n│ account │                                               data                                               │\n│ varchar │                      struct(\"name\" varchar, amount bigint, flow varchar)[]                       │\n├─────────┼──────────────────────────────────────────────────────────────────────────────────────────────────┤\n│ hijkl   │ [{'name': bonjour, 'amount': 1, 'flow': INFLOW}, {'name': hallo, 'amount': -3, 'flow': OUTFLOW}] │\n│ abcde   │ NULL                                                                                             │\n└─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘\n```\n\n```text\n'\n```\n\n```text\n\"\n```\n\n========================================\n\nComments:\n- In SQL double quotes are for *delimited identifiers*, and single quotes for literals.\n- I was not aware of this anymore as my SQL skills are outdated, I was stuck because the error message did not help me find out about this.\n- Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":8,"totalLines":191,"estimatedTokens":1138}}25{"id":"stack-74352815","source":"stackoverflow","questionId":74352815,"title":"problem with reading partitioned parquet files created by Snowflake with pandas or arrow","tags":["python","snowflake-cloud-data-platform","parquet","pyarrow","duckdb"],"text":"Title: problem with reading partitioned parquet files created by Snowflake with pandas or arrow\nTags: python, snowflake-cloud-data-platform, parquet, pyarrow, duckdb\nSource: Stack Overflow\n\nQuestion:\n```\nArrowInvalid: Unable to merge: Field X has incompatible types: string vs dictionary\n\nArrowInvalid: Unable to merge: Field X has incompatible types: decimal vs int32\n```\n\nI am trying to write the result of a snowflake query on disk and then query that data using arrow and duckdb. I have created a partitioned parquet with the query bellow following this:\n\n```\nCOPY INTO 's3://path/to/folder/'\nFROM (\n SELECT transaction.TRANSACTION_ID, OUTPUT_SCORE, MODEL_NAME, ACCOUNT_ID, to_char(TRANSACTION_DATE,'YYYY-MM') as SCORE_MTH\n FROM transaction\n )\npartition by('SCORE_MTH=' || score_mth || '/ACCOUNT_ID=' || ACCOUNT_ID)\nfile_format = (type=parquet)\nheader=true\n```\n\nWhen I try to read the parquet files I get the following error:\n\n```\ndf = pd.read_parquet('path/to/parquet/') # same result using pq.ParquetDataset or pq.read_table as they all use the same function under the hood\n\nArrowInvalid: Unable to merge: Field SCORE_MTH has incompatible types: string vs dictionary\n```\n\nMoreover, following some google search I found this page. Following the instructions:\ndf = pd.read_parquet('path/to/parquet/', use_legacy_dataset=True)\n\n```\nValueError: Schema in partition[SCORE_MTH=0, ACCOUNT_ID=0] /path/to/parquet was different. \nTRANSACTION_ID: string not null\nOUTPUT_SCORE: double\nMODEL_NAME: string\nACCOUNT_ID: int32\nSCORE_MTH: string\n\nvs\n\nTRANSACTION_ID: string not null\nOUTPUT_SCORE: double\nMODEL_NAME: string\n```\n\nAlso based on what the data type is you may get this error:\n\n```\nArrowInvalid: Unable to merge: Field X has incompatible types: IntegerType vs DoubleType\n```\n\nor\n\n```\nArrowInvalid: Unable to merge: Field X has incompatible types: decimal vs int32\n```\n\nThis is a known issue.\n\nAny idea how I can read this parquet file?\n\n========================================\n\nTop Answer:\nI was just dealing with the same issue and for me it worked if I provided the pyarrow schema to the function:\n\n```\nimport pandas as pd\nimport pyarrow as pa\n\nschema = pa.schema([('SCORE_MTH', pa.string()), ('ACCOUNT_ID', pa.int32())])\npd.read_parquet('s3://path/to/folder//', schema=schema) # works also with filters\n```\n\n========================================\n\nCode:\n```text\nArrowInvalid: Unable to merge: Field X has incompatible types: string vs dictionary<values=string, indices=int32, ordered=0>\n\nArrowInvalid: Unable to merge: Field X has incompatible types: decimal vs int32\n```\n\n```text\nCOPY INTO 's3://path/to/folder/'\nFROM (\n    SELECT transaction.TRANSACTION_ID, OUTPUT_SCORE, MODEL_NAME, ACCOUNT_ID, to_char(TRANSACTION_DATE,'YYYY-MM') as SCORE_MTH\n    FROM transaction\n    )\npartition by('SCORE_MTH=' || score_mth || '/ACCOUNT_ID=' || ACCOUNT_ID)\nfile_format = (type=parquet)\nheader=true\n```\n\n```text\ndf = pd.read_parquet('path/to/parquet/') # same result using pq.ParquetDataset or pq.read_table as they all use the same function under the hood\n\nArrowInvalid: Unable to merge: Field SCORE_MTH has incompatible types: string vs dictionary<values=string, indices=int32, ordered=0>\n```\n\n```text\nValueError: Schema in partition[SCORE_MTH=0, ACCOUNT_ID=0] /path/to/parquet was different. \nTRANSACTION_ID: string not null\nOUTPUT_SCORE: double\nMODEL_NAME: string\nACCOUNT_ID: int32\nSCORE_MTH: string\n\nvs\n\nTRANSACTION_ID: string not null\nOUTPUT_SCORE: double\nMODEL_NAME: string\n```\n\n```text\nArrowInvalid: Unable to merge: Field X has incompatible types: IntegerType vs DoubleType\n```\n\n```text\nArrowInvalid: Unable to merge: Field X has incompatible types: decimal vs int32\n```\n\n```text\nimport pyarrow.dataset as ds\ndataset = ds.dataset('/path/to/parquet/', format=\"parquet\", partitioning=\"hive\")\n```\n\n```text\nimport duckdb\ncon = duckdb.connect()\npandas_df = con.execute(\"Select * from dataset\").df()\n```\n\n```text\ndataset.to_table().to_pandas()\n```\n\n```text\nduckdb\n```\n\n```text\nto_table()\n```\n\n```py\nimport pandas as pd\nimport pyarrow as pa\n\nschema = pa.schema([('SCORE_MTH', pa.string()), ('ACCOUNT_ID', pa.int32())])\npd.read_parquet('s3://path/to/folder//', schema=schema)  # works also with filters\n```\n\n```text\ndf = pd.read_parquet('path/to/parquet/', engine=\"fastparquet\")\n```\n\n```text\npip install fastparquet\n```\n\n========================================\n\nComments:\n- Yeah that works. However, you need to know the data types in the first place. Also, a bit uncomfortable if you have a lot of columns","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":14,"totalLines":171,"estimatedTokens":1126}}26{"id":"stack-79687828","source":"stackoverflow","questionId":79687828,"title":"How to do Upserts with DuckDb and Ducklake","tags":["duckdb"],"text":"Title: How to do Upserts with DuckDb and Ducklake\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nHow do I upsert data into Ducklake?\n\nIf I have a simple table definition:\n\n```\nCREATE TABLE ducklakeexample.demo (\n \"Date\" TIMESTAMP WITH TIME ZONE,\n \"Id\" UUID,\n \"Title\" TEXT,\n \"Quantity\" INTEGER\n);\n```\n\nAdd a row into it:\n\n```\nINSERT INTO ducklakeexample.demo\n(\"Date\",\"Id\",\"Title\", \"Quantity\")\nVALUES\n('2025-07-01 13:44:58.11+00','f3c21234-8e2b-4e1d-b9d2-a11122334455','Some Name',150),\n```\n\nThen want to add a new row and update the Quantity of the existing one:\n\n```\nINSERT INTO ducklakeexample.demo\n(\"Date\",\"Id\",\"Title\", \"Quantity\")\nVALUES\n -- New dummy row\n ('2025-07-02 09:00:00+00', 'abcd1234-5678-90ab-cdef-112233445566', 'Another Title', 75),\n\n -- Qty change for existing row\n('2025-07-01 13:44:58.11+00','f3c21234-8e2b-4e1d-b9d2-a11122334455','Some Name',0);\n```\n\nThis adds a new row, for 'Some Name', as Duck Lake does not support Primary Keys.\n\nSo I cannot use:\n\n- `INSERT OR REPLACE INTO`\n\n- `DO UPDATE`\n\nsuch as:\n\n```\nINSERT INTO tbl VALUES (1, 42);\nINSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;\n```\n\nIf I know ahead of time that the ID exists, I can do a rudimentary update, which works.\n\n```\nUPDATE ducklakeexample.demo \nSET \"Quantity\" = 10\nWHERE \"Id\" = 'f3c21234-8e2b-4e1d-b9d2-a11122334455'::UUID;\n```\n\nSo is the expectation that the application has to handle this check ahead of time, then produce two separate queries, one to bulk insert, and other to bulk update?\n\nOtherwise I have an accountancy/ledger style, with duplicate entries and I have to check for the max row or something to get the latest update. Which makes no sense, as this is what Time Travel is for?\n\n========================================\n\nTop Answer:\nMERGE INTO Statement (v1.4.0 or later)\n\nThe MERGE INTO statement is an alternative to INSERT INTO ... ON CONFLICT that doesn't need a primary key since it allows for a custom match condition. This is a very useful alternative for upserting use cases (INSERT + UPDATE) when the destination table does not have a primary key constraint.\n\n```\nMERGE INTO demo\nUSING (SELECT CAST('2025-07-01 13:44:58.11+00'AS TIMESTAMP) AS \"Date\",\n 'f3c21234-8e2b-4e1d-b9d2-a11122334455' AS Id,\n 'Some Name' AS Title,\n 150 AS Quantity\n) AS src ON src.id = demo.id\nWHEN MATCHED THEN UPDATE \n SET \"Date\"= src.\"Date\", Title = src.Title, Quantity = src.Quantity\nWHEN NOT MATCHED THEN INSERT BY NAME;\n```\n\n========================================\n\nCode:\n```sql\nCREATE TABLE ducklakeexample.demo (\n  \"Date\" TIMESTAMP WITH TIME ZONE,\n  \"Id\" UUID,\n  \"Title\" TEXT,\n  \"Quantity\" INTEGER\n);\n```\n\n```sql\nINSERT INTO ducklakeexample.demo\n(\"Date\",\"Id\",\"Title\", \"Quantity\")\nVALUES\n('2025-07-01 13:44:58.11+00','f3c21234-8e2b-4e1d-b9d2-a11122334455','Some Name',150),\n```\n\n```sql\nINSERT INTO ducklakeexample.demo\n(\"Date\",\"Id\",\"Title\", \"Quantity\")\nVALUES\n  -- New dummy row\n  ('2025-07-02 09:00:00+00', 'abcd1234-5678-90ab-cdef-112233445566', 'Another Title', 75),\n\n  -- Qty change for existing row\n('2025-07-01 13:44:58.11+00','f3c21234-8e2b-4e1d-b9d2-a11122334455','Some Name',0);\n```\n\n```sql\nINSERT INTO tbl VALUES (1, 42);\nINSERT INTO tbl VALUES (1, 52), (1, 62) ON CONFLICT DO UPDATE SET j = EXCLUDED.j;\n```\n\n```sql\nUPDATE ducklakeexample.demo \nSET \"Quantity\" = 10\nWHERE \"Id\" = 'f3c21234-8e2b-4e1d-b9d2-a11122334455'::UUID;\n```\n\n```text\nINSERT OR REPLACE INTO\n```\n\n```text\nDO UPDATE\n```\n\n```text\nMERGE\n```\n\n```text\nDELETE\n```\n\n```text\nINSERT\n```\n\n```text\nMERGE INTO demo\nUSING (SELECT CAST('2025-07-01 13:44:58.11+00'AS TIMESTAMP) AS \"Date\",\n              'f3c21234-8e2b-4e1d-b9d2-a11122334455' AS Id,\n              'Some Name' AS Title,\n              150 AS Quantity\n) AS src ON src.id = demo.id\nWHEN MATCHED THEN UPDATE \n     SET \"Date\"= src.\"Date\", Title = src.Title, Quantity = src.Quantity\nWHEN NOT MATCHED THEN INSERT BY NAME;\n```\n\n========================================\n\nComments:\n- Update: it seems that a couple of hours after this post that the DuckDB team has opened a pull request for adding `MERGE` statements: github.com/duckdb/duckdb/pull/18135\n- Many thanks, this has solved my problems or today and a possible solution in the future!","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":11,"totalLines":164,"estimatedTokens":1049}}27{"id":"stack-76597352","source":"stackoverflow","questionId":76597352,"title":"Can DuckDB be used as Document Database?","tags":["duckdb"],"text":"Title: Can DuckDB be used as Document Database?\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nAs far as I know, the DuckDB is columnar database and can process and store sparse data efficiently.\n\nSo, would it be possible to use it as \"tuple space\" or \"document database\"? I don't expect to get top performance from DuckDB in such use case, good enough performance would be enough.\n\nThere going to be one huge table with `type, title, author, duration, director, note, todo` columns, with index on each, storing following objects (10 millions of objects):\n\n```\n{ type: \"book\", title: \"Art of War\", author: \"Sun Tzu\" }\n{ type: \"book\", title: \"Fooled by Randomness\", author: \"Nassim Taleb\" },\n\n{ type: \"movie\", title: \"Total Recall\", duration: 1.5, director: \"Verh\" },\n\n{ type: \"note\", note: \"Earth moves around Sun\" }\n\n{ type: \"todo\", todo: \"Buy milk\" }\n```\n\nTypical queries will be: select list of objects:\n\n```\nselect * from objects where type = \"book\" and author in [\"Taleb\", \"Sun Tzi\"]\n```\n\nget group stats for given filter\n\n```\nselect type, count(*)\nfrom objects\nwhere author in [\"Taleb\", \"Sun Tzi\"]\ngroup by type\n```\n\n========================================\n\nTop Answer:\nThere's some info out there on using Sqlite as a document store, based on its support for JSON (for example see this hacker news discussion).\n\nDuckDB also has functions to work with JSON, so it could be used in a similar way (your table would have two columns: a key and a json blob). I think this setup could make sense, perhaps specially if you are already using DuckDB in your app but also need some document store.\n\nI haven't tried it but since indexes can be created on expressions, you could even add indexes that look into the JSON blob content! (Both SQlite and DuckDB support generated columns, that can be used to, say, extract the id of the doc from the JSON blob, or index some internal part of the table/document).\n\nFrom a practicality point of view, DuckDB is OLAP oriented, I suspect transactions are gonna be more expensive in there vs a OLTP oriented store like Postgres or Sqlite.\n\n========================================\n\nCode:\n```text\n{ type: \"book\", title: \"Art of War\", author: \"Sun Tzu\" }\n{ type: \"book\", title: \"Fooled by Randomness\", author: \"Nassim Taleb\" },\n\n{ type: \"movie\", title: \"Total Recall\", duration: 1.5, director: \"Verh\" },\n\n{ type: \"note\", note: \"Earth moves around Sun\" }\n\n{ type: \"todo\", todo: \"Buy milk\" }\n```\n\n```text\nselect * from objects where type = \"book\" and author in [\"Taleb\", \"Sun Tzi\"]\n```\n\n```text\nselect type, count(*)\nfrom objects\nwhere author in [\"Taleb\", \"Sun Tzi\"]\ngroup by type\n```\n\n```text\ntype, title, author, duration, director, note, todo\n```\n\n```text\nNULL\n```\n\n```text\nNULL\n```\n\n```text\ntype\n```\n\n```text\ntitle\n```\n\n```text\nauthor\n```\n\n```text\nduration\n```\n\n```text\ndirector\n```\n\n```text\nnote\n```\n\n```text\ntodo\n```\n\n```text\nINSERT INTO\n```\n\n```text\nCREATE TABLE\n```\n\n```text\nALTER TABLE\n```\n\n```text\nALTER TABLE\n```\n\n```text\nALTER TABLE\n```\n\n```text\nALTER TABLE\n```\n\n```text\nADD TABLE\n```\n\n```text\nNULL\n```\n\n```text\ntype\n```\n\n```text\ntype: 'note'\n```\n\n```text\nnote\n```\n\n```text\ntype: 'book'\n```\n\n```text\nauthor\n```\n\n```text\ntitle\n```\n\n```text\nCHECK\n```\n\n```text\nCHECK\n```\n\n========================================\n\nComments:\n- \"olumnar database and can process and store sparse data efficiently.\" - where are you seeing references to \"sparse\" data w.r.t. DuckDB?\n- @Dai a) process efficiently - I assume, that as soon as it's columnar and don't need to load the whole row, it should be able to process sparse data efficiently b) store efficiently, I found this github issue about storage of sparse data github.com/duckdb/duckdb/issues/632\n- I don't think column-store DBs have data-storage-efficiency advantages over traditional rowstores (but they do have significant performance advantages for *certain* workloads) - though I imagine columnstores *probably* compress better too, but table-compression is a double-edged sword. As for that github issue you linked-to: they describe their compression system working well for sparse-tables (i.e. tables where most \"cells\" are `NULL`), but that's to be expected: all major RDBMS today offer efficient storage of sparse-table and columnstore too...\n- I would try sqlite with Partial Indexes on `type` column.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":29,"totalLines":184,"estimatedTokens":1074}}28{"id":"stack-77541316","source":"stackoverflow","questionId":77541316,"title":"DuckDB - How to iterate over the result returned from duckdb.sql command","tags":["python","duckdb"],"text":"Title: DuckDB - How to iterate over the result returned from duckdb.sql command\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nUsing DuckDB Python client, I want to iterate over the results returned from query like\n\n```\nimport duckdb\nemployees = duckdb.sql(\"select * from 'employees.csv' \")\n```\n\nUsing **type(employees)** returns 'duckdb.duckdb.DuckDBPyRelation'. I'm able to get the number of rows with **len(employees)**, however for-loop iteration seems to not work.\n\nWhat is the correct way to process each row at a time?\n\n========================================\n\nTop Answer:\nHere is an example for batching the rows with `fetchmany`:\n\n```\nimport duckdb\nbatch_size = 10\n\nhandle = duckdb.sql(\"select * from 'employees.csv'\")\n\nwhile batch := handle.fetchmany(batch_size):\n print(batch)\n```\n\n========================================\n\nCode:\n```text\nimport duckdb\nemployees = duckdb.sql(\"select * from 'employees.csv' \")\n```\n\n```text\nimport duckdb\n\nemployees = duckdb.sql(\"select * from 'employees.csv'\")\nrows = employees.fetchall()\n\n# Iterate over the rows\nfor row in rows:\n    print(row)\n```\n\n```text\nimport duckdb\nbatch_size = 10\n\nhandle = duckdb.sql(\"select * from 'employees.csv'\")\n\nwhile batch := handle.fetchmany(batch_size):\n    print(batch)\n```\n\n```text\nfetchmany\n```\n\n========================================\n\nComments:\n- The mentioned approach to fetchall rows as a list will be fine when number of rows are limited. Can you also describe how to use fetchmany for processing in batches","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":4,"totalLines":68,"estimatedTokens":376}}29{"id":"stack-78496873","source":"stackoverflow","questionId":78496873,"title":"How to write CSV data directly from string (or bytes) to a duckdb database file in Python?","tags":["python","duckdb"],"text":"Title: How to write CSV data directly from string (or bytes) to a duckdb database file in Python?\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nI would like to write CSV data directly from a bytes (or string) object in memory to duckdb database file (i.e. I want to avoid having to write and read the temporary .csv files). This is what I've got so far:\n\n```\nimport io \nimport duckdb \n\ndata = b'a,b,c\\n0,1,2\\n3,4,5'\nrawtbl = duckdb.read_csv(\n io.BytesIO(data), header=True, sep=\",\"\n)\n\ncon = duckdb.connect('some.db')\ncon.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n```\n\nwhich throws following exception:\n\n```\n---------------------------------------------------------------------------\nIOException Traceback (most recent call last)\nCell In[1], line 10\n 5 rawtbl = duckdb.read_csv(\n 6 io.BytesIO(data), header=True, sep=\",\"\n 7 )\n 9 con = duckdb.connect('some.db')\n---> 10 con.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n\nIOException: IO Error: No files found that match the pattern \"DUCKDB_INTERNAL_OBJECTSTORE://2843be5a66472f9c\"\n```\n\nHowever, it is possible to do:\n\n```\n>>> duckdb.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n>>> duckdb.sql('show tables')\n┌─────────┐\n│ name │\n│ varchar │\n├─────────┤\n│ foo │\n└─────────┘\n>>> duckdb.sql('SELECT * from foo')\n┌───────┬───────┬───────┐\n│ a │ b │ c │\n│ int64 │ int64 │ int64 │\n├───────┼───────┼───────┤\n│ 0 │ 1 │ 2 │\n│ 3 │ 4 │ 5 │\n└───────┴───────┴───────┘\n```\n\nsince `rawtbl` is a `duckdb.duckdb.DuckDBPyRelation` object. But that is the in-memory duckdb database, not the 'some.db' file.\n\n### Question\n\nHow to read csv data directly from bytes (or a string) to duckdb database file, without using intermediate CSV files?\n\n### Versions\n\nduckdb 0.10.2 on Python 3.12.2 on Ubuntu\n\n========================================\n\nTop Answer:\nI'm not entirely sure on best practices, but I did manage to get it to work with ATTACH.\n\n```\nduckdb.sql(\"attach 'some.db'\")\n```\n\n`duckdb_databases()` gave me the names.\n\n```\nduckdb.sql(\"from duckdb_databases()\")\n```\n\n```\n┌───────────────┬──────────────┬─────────┬───┬──────────┬─────────┬──────────┐\n│ database_name │ database_oid │ path │ … │ internal │ type │ readonly │\n│ varchar │ int64 │ varchar │ │ boolean │ varchar │ boolean │\n├───────────────┼──────────────┼─────────┼───┼──────────┼─────────┼──────────┤\n│ memory │ 1080 │ NULL │ … │ false │ duckdb │ false │\n│ some │ 1489 │ some.db │ … │ false │ duckdb │ false │\n│ system │ 0 │ NULL │ … │ true │ duckdb │ false │\n│ temp │ 1479 │ NULL │ … │ true │ duckdb │ false │\n├───────────────┴──────────────┴─────────┴───┴──────────┴─────────┴──────────┤\n│ 4 rows 7 columns (6 shown) │\n└────────────────────────────────────────────────────────────────────────────┘\n```\n\nWe can then USE the file-backed db and select the data from rawtbl.\n\n```\nduckdb.sql(\"\"\"\nuse \"some\";\ncreate table foo as (from rawtbl)\n\"\"\")\n```\n\nCheck the result:\n\n```\ncon = duckdb.connect(\"some.db\")\ncon.sql(\"from foo\")\n```\n\n```\n┌───────┬───────┬───────┐\n│ a │ b │ c │\n│ int64 │ int64 │ int64 │\n├───────┼───────┼───────┤\n│ 0 │ 1 │ 2 │\n│ 3 │ 4 │ 5 │\n└───────┴───────┴───────┘\n```\n\n========================================\n\nCode:\n```py\nimport io \nimport duckdb \n\ndata = b'a,b,c\\n0,1,2\\n3,4,5'\nrawtbl = duckdb.read_csv(\n    io.BytesIO(data), header=True, sep=\",\"\n)\n\ncon = duckdb.connect('some.db')\ncon.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n```\n\n```py\n---------------------------------------------------------------------------\nIOException                               Traceback (most recent call last)\nCell In[1], line 10\n      5 rawtbl = duckdb.read_csv(\n      6     io.BytesIO(data), header=True, sep=\",\"\n      7 )\n      9 con = duckdb.connect('some.db')\n---> 10 con.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n\nIOException: IO Error: No files found that match the pattern \"DUCKDB_INTERNAL_OBJECTSTORE://2843be5a66472f9c\"\n```\n\n```py\n>>> duckdb.sql('CREATE TABLE foo AS SELECT * FROM rawtbl')\n>>> duckdb.sql('show tables')\n┌─────────┐\n│  name   │\n│ varchar │\n├─────────┤\n│ foo     │\n└─────────┘\n>>> duckdb.sql('SELECT * from foo')\n┌───────┬───────┬───────┐\n│   a   │   b   │   c   │\n│ int64 │ int64 │ int64 │\n├───────┼───────┼───────┤\n│     0 │     1 │     2 │\n│     3 │     4 │     5 │\n└───────┴───────┴───────┘\n```\n\n```text\nrawtbl\n```\n\n```text\nduckdb.duckdb.DuckDBPyRelation\n```\n\n```py\nimport io\nimport duckdb\nfrom pathlib import Path\n\n\ndata = b'a,b,c\\n0,1,2\\n3,4,5'\ndb_path = 'some.db'\nPath(db_path).unlink(missing_ok=True)\n\nwith duckdb.connect(db_path) as con:\n    rawtbl = duckdb.read_csv(\n        io.BytesIO(data), header=True, sep=\",\", connection=con,\n    )\n\n    con.execute('''\n        CREATE TABLE foo as select * from rawtbl\n    ''')\n\nwith duckdb.connect(db_path) as con:\n    res = con.sql('select * from foo')\n    print(res)\n\n# ┌───────┬───────┬───────┐\n# │   a   │   b   │   c   │\n# │ int64 │ int64 │ int64 │\n# ├───────┼───────┼───────┤\n# │     0 │     1 │     2 │\n# │     3 │     4 │     5 │\n# └───────┴───────┴───────┘\n```\n\n```text\nread_csv\n```\n\n```py\nduckdb.sql(\"attach 'some.db'\")\n```\n\n```py\nduckdb.sql(\"from duckdb_databases()\")\n```\n\n```py\n┌───────────────┬──────────────┬─────────┬───┬──────────┬─────────┬──────────┐\n│ database_name │ database_oid │  path   │ … │ internal │  type   │ readonly │\n│    varchar    │    int64     │ varchar │   │ boolean  │ varchar │ boolean  │\n├───────────────┼──────────────┼─────────┼───┼──────────┼─────────┼──────────┤\n│ memory        │         1080 │ NULL    │ … │ false    │ duckdb  │ false    │\n│ some          │         1489 │ some.db │ … │ false    │ duckdb  │ false    │\n│ system        │            0 │ NULL    │ … │ true     │ duckdb  │ false    │\n│ temp          │         1479 │ NULL    │ … │ true     │ duckdb  │ false    │\n├───────────────┴──────────────┴─────────┴───┴──────────┴─────────┴──────────┤\n│ 4 rows                                                 7 columns (6 shown) │\n└────────────────────────────────────────────────────────────────────────────┘\n```\n\n```py\nduckdb.sql(\"\"\"\nuse \"some\";\ncreate table foo as (from rawtbl)\n\"\"\")\n```\n\n```py\ncon = duckdb.connect(\"some.db\")\ncon.sql(\"from foo\")\n```\n\n```py\n┌───────┬───────┬───────┐\n│   a   │   b   │   c   │\n│ int64 │ int64 │ int64 │\n├───────┼───────┼───────┤\n│     0 │     1 │     2 │\n│     3 │     4 │     5 │\n└───────┴───────┴───────┘\n```\n\n```text\nduckdb_databases()\n```\n\n========================================\n\nComments:\n- Thanks! Did'nt realize that the `duckdb.read_csv()` takes a `connection` argument! Also noticed that you may use `con.read_csv()` directly.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":14,"totalLines":265,"estimatedTokens":1630}}30{"id":"stack-76478913","source":"stackoverflow","questionId":76478913,"title":"Polars is much slower than DuckDB in conditional join + group_by/agg context","tags":["python","python-polars","duckdb"],"text":"Title: Polars is much slower than DuckDB in conditional join + group_by/agg context\nTags: python, python-polars, duckdb\nSource: Stack Overflow\n\nQuestion:\nFor the following example, where it involves a self conditional join and a subsequent groupby/aggregate operation. It turned out that in such case, `DuckDB` gives much better performance than `Polars` (~10x on a 32-core machine).\n\nMy questions are:\n\n- What could be the potential reason(s) for the slowness (relative to `DuckDB`) of `Polars`?\n\n- Am I missing some other faster ways of doing the same thing in `Polars`?\n\n```\nimport time\n\nimport duckdb\nimport numpy as np\nimport polars as pl\n\n## example dataframe\nrng = np.random.default_rng(1)\n\nnrows = 5_000_000\ndf = pl.DataFrame(\n dict(\n id=rng.integers(1, 1_000, nrows),\n id2=rng.integers(1, 10, nrows),\n id3=rng.integers(1, 500, nrows),\n value=rng.normal(0, 1, nrows),\n )\n)\n\n## polars\nstart = time.perf_counter()\nres = (\n df.lazy()\n .join(df.lazy(), on=[\"id\", \"id2\"], how=\"left\")\n .filter(\n (pl.col(\"id3\") > pl.col(\"id3_right\"))\n & (pl.col(\"id3\") - pl.col(\"id3_right\") df2.id3\n AND df.id3 - df2.id3 < 30)\n \"\"\"\n )\n .aggregate(\n \"id2, id3, id3_right, corr(value, value_right) as value\",\n \"id2, id3, id3_right\",\n )\n .pl()\n)\ntime.perf_counter() - start\n# 18.472263277042657\n```\n\n========================================\n\nTop Answer:\nWhile DuckDB does have several non-equi-joins, the planner currently assumes that all equality predicates are more selective than inequalities an just generates a hash join here:\n\n```\nD EXPLAIN SELECT id2, id3, id3_right, corr(value, value_right) as value\n> FROM (\n> SELECT df.*, df2.id3 as id3_right, df2.value as value_right\n> FROM df JOIN df as df2\n> ON (df.id = df2.id\n> AND df.id2 = df2.id2\n> AND df.id3 > df2.id3\n> AND df.id3 - df2.id3 ) tbl\n> GROUP BY ALL\n> ;\n\n┌─────────────────────────────┐\n│┌───────────────────────────┐│\n││ Physical Plan ││\n│└───────────────────────────┘│\n└─────────────────────────────┘\n┌───────────────────────────┐ \n│ HASH_GROUP_BY │ \n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ \n│ #0 │ \n│ #1 │ \n│ #2 │ \n│ corr(#3, #4) │ \n└─────────────┬─────────────┘ \n┌─────────────┴─────────────┐ \n│ PROJECTION │ \n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ \n│ id2 │ \n│ id3 │ \n│ id3_right │ \n│ value │ \n│ value_right │ \n└─────────────┬─────────────┘ \n┌─────────────┴─────────────┐ \n│ PROJECTION │ \n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ \n│ #1 │ \n│ #2 │ \n│ #3 │ \n│ #4 │ \n│ #5 │ \n└─────────────┬─────────────┘ \n┌─────────────┴─────────────┐ \n│ FILTER │ \n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ \n│ ((id3 - id3) id3 │ │ \n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │ │ \n│ EC: 24727992087 │ │ \n│ Cost: 24727992087 │ │ \n└─────────────┬─────────────┘ │ \n┌─────────────┴─────────────┐┌─────────────┴─────────────┐\n│ SEQ_SCAN ││ SEQ_SCAN │\n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │\n│ df ││ df │\n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │\n│ id ││ id │\n│ id2 ││ id2 │\n│ id3 ││ id3 │\n│ value ││ value │\n│ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ││ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ │\n│ EC: 5000000 ││ EC: 5000000 │\n└───────────────────────────┘└───────────────────────────┘\n```\n\nWe plan to address this in a future release.\n\nNote also that the IEJoin algorithm requires two inequalities and the query only has one. Single inequalities could be handled by the PieceWiseMergeJoin operator, but PWMJ does not currently handle simple equalities (the logic would just have to be extended to handle `NULL`s correctly).\n\n========================================\n\nCode:\n```text\nimport time\n\nimport duckdb\nimport numpy as np\nimport polars as pl\n\n## example dataframe\nrng = np.random.default_rng(1)\n\nnrows = 5_000_000\ndf = pl.DataFrame(\n    dict(\n        id=rng.integers(1, 1_000, nrows),\n        id2=rng.integers(1, 10, nrows),\n        id3=rng.integers(1, 500, nrows),\n        value=rng.normal(0, 1, nrows),\n    )\n)\n\n## polars\nstart = time.perf_counter()\nres = (\n    df.lazy()\n    .join(df.lazy(), on=[\"id\", \"id2\"], how=\"left\")\n    .filter(\n        (pl.col(\"id3\") > pl.col(\"id3_right\"))\n        & (pl.col(\"id3\") - pl.col(\"id3_right\") < 30)\n    )\n    .group_by([\"id2\", \"id3\", \"id3_right\"])\n    .agg(pl.corr(\"value\", \"value_right\"))\n    .collect(streaming=True)\n)\ntime.perf_counter() - start\n# 120.93155245436355\n\n## duckdb\nstart = time.perf_counter()\nres2 = (\n    duckdb.sql(\n        \"\"\"\n        SELECT df.*, df2.id3 as id3_right, df2.value as value_right\n        FROM df JOIN df as df2\n        ON (df.id = df2.id\n        AND df.id2 = df2.id2\n        AND df.id3 > df2.id3\n        AND df.id3 - df2.id3 < 30)\n        \"\"\"\n    )\n    .aggregate(\n        \"id2, id3, id3_right, corr(value, value_right) as value\",\n        \"id2, id3, id3_right\",\n    )\n    .pl()\n)\ntime.perf_counter() - start\n# 18.472263277042657\n```\n\n```text\nDuckDB\n```\n\n```text\nPolars\n```\n\n```text\nDuckDB\n```\n\n```text\nPolars\n```\n\n```text\nPolars\n```\n\n```text\npolars v0.18.2  1125\npolars v0.18.3  140\nduckdb 0.8.2-dev1   75\n```\n\n```text\n0.18.3\n```\n\n```sql\nD EXPLAIN SELECT id2, id3, id3_right, corr(value, value_right) as value\n> FROM (\n>     SELECT df.*, df2.id3 as id3_right, df2.value as value_right\n>     FROM df JOIN df as df2\n>         ON (df.id = df2.id\n>         AND df.id2 = df2.id2\n>         AND df.id3 > df2.id3\n>         AND df.id3 - df2.id3 < 30)\n>     ) tbl\n> GROUP BY ALL\n> ;\n\n┌─────────────────────────────┐\n│┌───────────────────────────┐│\n││       Physical Plan       ││\n│└───────────────────────────┘│\n└─────────────────────────────┘\n┌───────────────────────────┐                             \n│       HASH_GROUP_BY       │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│             #0            │                             \n│             #1            │                             \n│             #2            │                             \n│        corr(#3, #4)       │                             \n└─────────────┬─────────────┘                                                          \n┌─────────────┴─────────────┐                             \n│         PROJECTION        │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│            id2            │                             \n│            id3            │                             \n│         id3_right         │                             \n│           value           │                             \n│        value_right        │                             \n└─────────────┬─────────────┘                                                          \n┌─────────────┴─────────────┐                             \n│         PROJECTION        │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│             #1            │                             \n│             #2            │                             \n│             #3            │                             \n│             #4            │                             \n│             #5            │                             \n└─────────────┬─────────────┘                                                          \n┌─────────────┴─────────────┐                             \n│           FILTER          │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│     ((id3 - id3) < 30)    │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│      EC: 24727992087      │                             \n└─────────────┬─────────────┘                                                          \n┌─────────────┴─────────────┐                             \n│         HASH_JOIN         │                             \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             \n│           INNER           │                             \n│          id = id          │                             \n│         id2 = id2         ├──────────────┐              \n│         id3 > id3         │              │              \n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │              │              \n│      EC: 24727992087      │              │              \n│     Cost: 24727992087     │              │              \n└─────────────┬─────────────┘              │                                           \n┌─────────────┴─────────────┐┌─────────────┴─────────────┐\n│         SEQ_SCAN          ││         SEQ_SCAN          │\n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │\n│             df            ││             df            │\n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │\n│             id            ││             id            │\n│            id2            ││            id2            │\n│            id3            ││            id3            │\n│           value           ││           value           │\n│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │\n│        EC: 5000000        ││        EC: 5000000        │\n└───────────────────────────┘└───────────────────────────┘\n```\n\n```text\nNULL\n```\n\n========================================\n\nComments:\n- It's also to do with the fact that the DuckDb version performs a range join. duckdb.org/2022/05/27/iejoin.html\n- v0.18.3 was just released. There is now a `1.8x` difference compared to a `15x` difference using `v0.18.2` when I test this.\n- Does it mean that the kind of operations in my example could be potentially faster in future?\n- Quite possibly. I did a little test forcing it to use IEJoin (after changing the last predicate to have one field on each side...) and it was 2-3x faster.\n- With a proper benchmark using a new pragma, the gain is more like 6x.\n- How can I access to this faster version?\n- It would be a prerelease (`pip install duckdb --pre --upgrade`) but it is still in code review.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":10,"totalLines":310,"estimatedTokens":2394}}31{"id":"stack-75475994","source":"stackoverflow","questionId":75475994,"title":"How many threads is DuckDB using?","tags":["r","duckdb"],"text":"Title: How many threads is DuckDB using?\nTags: r, duckdb\nSource: Stack Overflow\n\nQuestion:\nUsing duckDB from within R, e.g.\n\n```\nlibrary(duckdb)\ndbname how can I find out how many threads the (separate process) is using? I am aware of\n\n```\ndbExecute(con2, \"PRAGMA threads=4;\")\n```\n\nbut I am interested in figuring out reading this configuration detail, not setting it.\n\n========================================\n\nCode:\n```text\nlibrary(duckdb)\ndbname <- \"sparsemat.duckdb\"\ncon2 <- dbConnect(duckdb(), dbname)\ndbExecute(con2, \"PRAGMA memory_limit='1GB';\")\n```\n\n```text\ndbExecute(con2, \"PRAGMA threads=4;\")\n```\n\n```text\n-- show a list of all available settings\nSELECT * FROM duckdb_settings();\n\n-- return the current value of a specific setting\nSELECT current_setting('threads')\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":3,"totalLines":38,"estimatedTokens":195}}32{"id":"stack-79367198","source":"stackoverflow","questionId":79367198,"title":"DuckDB - Conversion Error: Could not convert Timestamp(MS) to Timestamp(US)","tags":["duckdb"],"text":"Title: DuckDB - Conversion Error: Could not convert Timestamp(MS) to Timestamp(US)\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nI am trying to convert a script from SQLite to DuckDB, but cannot seem to go around these timestamps and datetime formats...\n\nFor example, in SQLite, this code works:\n\n```\ndatetime((lastLogon / 10000000) - 11644473600, 'unixepoch') AS lastLogon\n```\n\nin DuckDB, I cannot get myself to find the proper functions...\n\n```\nSELECT epoch_ms(133782998237203223);\n```\n\nresults in error\n\nError: Conversion Error: Could not convert Timestamp(MS) to Timestamp(US)\n\nSQLState: null\n\nErrorCode: 0\n\nI tried different functions, like\n\n```\nSELECT to_timestamp(133782998237203223)::TIMESTAMPTZ AT TIME ZONE 'UTC'\n```\n\nbut still errors\n\nError: Conversion Error: Could not convert epoch seconds to TIMESTAMP WITH TIME ZONE\n\n========================================\n\nCode:\n```text\ndatetime((lastLogon / 10000000) - 11644473600, 'unixepoch') AS lastLogon\n```\n\n```text\nSELECT epoch_ms(133782998237203223);\n```\n\n```text\nSELECT to_timestamp(133782998237203223)::TIMESTAMPTZ AT TIME ZONE 'UTC'\n```\n\n```sql\nselect epoch_ms(133782998237203223 // 100_000);\n-- 2012-05-24 03:26:22.372\n```\n\n```sql\nselect to_timestamp(133782998237203223 / 10_000_000);\n-- 2012-05-23 20:26:22.372032-07\n```\n\n```text\nepoch_ms\n```\n\n```text\nTIMESTAMP\n```\n\n```text\nto_timestamp\n```\n\n```text\nDOUBLE\n```\n\n```text\nTIMESTAMP WITH TIME ZONE\n```\n\n```text\nAmerica/Los_Angeles\n```\n\n========================================\n\nComments:\n- Thanks for pointing it out. It does work similar to sqlite but I forgot to do the maths :) select to_timestamp((133782998237203223 / 10000000) - 11644473600)","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":11,"totalLines":90,"estimatedTokens":416}}33{"id":"stack-78961516","source":"stackoverflow","questionId":78961516,"title":"DuckDB - Unnest map that have different keys","tags":["struct","unpivot","duckdb"],"text":"Title: DuckDB - Unnest map that have different keys\nTags: struct, unpivot, duckdb\nSource: Stack Overflow\n\nQuestion:\nEDIT: as a new duckdb user, I misinterpretted the output syntax as a `struct` it turns out I had created a `map`\n\nI have a table that contains a `map`\n\nProduct\nSales\n\nWidget\n`{Jan=12, Feb=13}`\n\nDongle\n`{Feb=1, Apr=22}`\n\nIs there a way to unpivot this so that I get?\n\nProduct\nMonth\nQty\n\nWidget\nJan\n12\n\nWidget\nFeb\n13\n\nDongle\nFeb\n1\n\nDongle\nApr\n22\n\nI know the documenation states that structs must have the same keys, but I don't see a clear way to do this with `map`.\n\n========================================\n\nTop Answer:\nIn the original version of the question, the Sales column was\npresented as though the values were strings such as '{Jan=12, Feb=13}'.\nThe following accordingly addresses the question of how one would\nproceed if the Sales data were presented in this form.\n\nLet's therefore begin with a TSV file:\n\n```\nProduct Sales\nWidget {Jan=12, Feb=13}\nDongle {Feb=1, Apr=22}\n```\n\nIn the following, these JSON-like strings are first converted to valid JSON,\nand then the DuckDB JSON extension is used to produce the desired result.\n\nFor the sake of clarity, the main steps are shown as distinct SQL statements:\n\n```\nLOAD json;\n\n# Read in the data\nCREATE OR REPLACE TABLE t AS\n FROM read_csv('sales.tsv', sep=\"\\t\");\n\n# Convert to JSON:\nCREATE OR REPLACE TABLE u AS\n FROM t\n select Product, \n regexp_replace(regexp_replace(Sales, '=', ':', 'g'),\n '([A-Z][a-z][a-z])', '\"\\1\"', 'g')::JSON as Sales;\n\n# Use unnest to produce the Month and Qty columns:\nselect\n Product,\n unnest(json_keys(Sales)) as Month,\n unnest(list_transform(json_keys(Sales), x -> Sales[x]::INTEGER)) as Qty\nfrom u;\n```\n\nOutput:\n\n```\n┌─────────┬─────────┬───────┐\n│ Product │ Month │ Qty │\n│ varchar │ varchar │ int32 │\n├─────────┼─────────┼───────┤\n│ Widget │ Jan │ 12 │\n│ Widget │ Feb │ 13 │\n│ Dongle │ Feb │ 1 │\n│ Dongle │ Apr │ 22 │\n└─────────┴─────────┴───────┘\n```\n\nCaveat: the above \"translation\" of the original Sales data to JSON is of course potentially brittle.\n\n========================================\n\nCode:\n```text\nstruct\n```\n\n```text\nmap\n```\n\n```text\nmap\n```\n\n```text\n{Jan=12, Feb=13}\n```\n\n```text\n{Feb=1, Apr=22}\n```\n\n```text\nmap\n```\n\n```py\nduckdb.sql(\"\"\"\ncreate table tbl as (\n   select\n      unnest(['Widget', 'Dongle']) as Product,\n      unnest([\n         map(['Jan', 'Feb'], [12, 13]),\n         map(['Feb', 'Apr'], [1, 22])\n      ]) as Sales\n)   \n\"\"\")\n```\n\n```text\n┌─────────┬───────────────────────┐\n│ Product │         Sales         │\n│ varchar │ map(varchar, integer) │\n├─────────┼───────────────────────┤\n│ Widget  │ {Jan=12, Feb=13}      │\n│ Dongle  │ {Feb=1, Apr=22}       │\n└─────────┴───────────────────────┘\n```\n\n```py\nduckdb.sql(\"\"\"\nfrom tbl\nselect \n    Product,\n    unnest(map_keys(Sales)) as Month,\n    unnest(map_values(Sales)) as Qty\n\"\"\")\n```\n\n```text\n┌─────────┬─────────┬───────┐\n│ Product │  Month  │  Qty  │\n│ varchar │ varchar │ int32 │\n├─────────┼─────────┼───────┤\n│ Widget  │ Jan     │    12 │\n│ Widget  │ Feb     │    13 │\n│ Dongle  │ Feb     │     1 │\n│ Dongle  │ Apr     │    22 │\n└─────────┴─────────┴───────┘\n```\n\n```text\nunnest()\n```\n\n```text\n.map_keys()\n```\n\n```text\n.map_values()\n```\n\n```text\nProduct Sales\nWidget  {Jan=12, Feb=13}\nDongle  {Feb=1, Apr=22}\n```\n\n```text\nLOAD json;\n\n# Read in the data\nCREATE OR REPLACE TABLE t AS\n  FROM read_csv('sales.tsv', sep=\"\\t\");\n\n# Convert to JSON:\nCREATE OR REPLACE TABLE u AS\n  FROM t\n  select Product, \n    regexp_replace(regexp_replace(Sales, '=', ':', 'g'),\n      '([A-Z][a-z][a-z])', '\"\\1\"', 'g')::JSON as Sales;\n\n# Use unnest to produce the Month and Qty columns:\nselect\n  Product,\n  unnest(json_keys(Sales)) as Month,\n  unnest(list_transform(json_keys(Sales), x -> Sales[x]::INTEGER)) as Qty\nfrom u;\n```\n\n```text\n┌─────────┬─────────┬───────┐\n│ Product │  Month  │  Qty  │\n│ varchar │ varchar │ int32 │\n├─────────┼─────────┼───────┤\n│ Widget  │ Jan     │    12 │\n│ Widget  │ Feb     │    13 │\n│ Dongle  │ Feb     │     1 │\n│ Dongle  │ Apr     │    22 │\n└─────────┴─────────┴───────┘\n```\n\n========================================\n\nComments:\n- How do you have a Struct column like that in the first place if it is not possible with DuckDB? Can you a runnable example?\n- I have a Python UDF that parses text into key value pairs.\n- Can you a Python code example so that we can reproduce this?\n- Turns out I had mistake the datatype. It's a `map`, not a `struct`\n- Ah right - that makes sense. If you provide your sample data as code, it makes it much simpler to provide answers. I've added an example below.\n- Unfortunately, 8M rows, and about a 25 keys per row.... need to summarize.\n- Yup, that was it. Apparently fighting with the unpivot wasn't worth it either. Thank-you","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":16,"totalLines":235,"estimatedTokens":1190}}34{"id":"stack-76426317","source":"stackoverflow","questionId":76426317,"title":"Tips for extracting data from a JSON column in DuckDb","tags":["sql","json","duckdb"],"text":"Title: Tips for extracting data from a JSON column in DuckDb\nTags: sql, json, duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm looking for a duckdb function similar to redshift's JSON_EXTRACT_PATH_TEXT(). If I have a column that is a VARCHAR version of a JSON, I see that I can convert from the string to JSON by CAST(column_name as JSON), but how do I get at the attributes?\n\n========================================\n\nCode:\n```sql\nselect (' { \"family\": \"anatidae\", \"species\": [ \"duck\", \"goose\", \"swan\", null ] }')->'species'->>0;\n```\n\n========================================\n\nComments:\n- How did I miss that! Thanks so much.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":1,"totalLines":18,"estimatedTokens":156}}35{"id":"stack-78806010","source":"stackoverflow","questionId":78806010,"title":"Base 16 hexadecimal string to base 10 integer","tags":["duckdb"],"text":"Title: Base 16 hexadecimal string to base 10 integer\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm trying to convert a hash into an integer. The conversion works as expected in python and t-sql (with the same answer). I'm trying to replicate it in duckdb but can't quite.\n\nIn python\n\n```\ndef get_hash(customer_id):\n hash_object = hashlib.md5(customer_id.encode())\n return hash_object.hexdigest()[:15]\n\ndef get_integer_representation_of_hash(customer_id):\n hash_value = get_hash(customer_id)\n return int(hash_value, 16)\n```\n\nIn tsql\n\n```\nSELECT \nSUBSTRING(CONVERT(VARCHAR(900),\n HASHBYTES('MD5', CAST('Customer1' AS VARCHAR(36))), \n 1\n ),\n 3, 15\n ),\nCONVERT(bigint, CONVERT(VARBINARY(900), '0' + SUBSTRING(CONVERT(VARCHAR(900),\n HASHBYTES('MD5', CAST('Customer1' AS VARCHAR(36))), \n 1\n ),\n 3, 15\n ), 2))\n```\n\nIn duckdb\n\n```\nSELECT \nmd5('Customer1')[:15] AS hash_value,\nHASH(md5('Customer1')[:15]) AS hash_to_int\n```\n\nIn all three cases, the hash value is the same.\n\n```\nHash = BECFB907888C8D4\n```\n\nIn python and tsql I get the same integer value. In duckdb I get something completely different.\n\n```\nPython = TSQL = 859338226837014740\n\nDuckDB = 16188616960793580010\n```\n\nI think it's because the int type is incorrect in duckdb. It needs to be 16 instead of 64 but I don't quite know how to get to that.\n\n========================================\n\nCode:\n```py\ndef get_hash(customer_id):\n    hash_object = hashlib.md5(customer_id.encode())\n    return hash_object.hexdigest()[:15]\n\ndef get_integer_representation_of_hash(customer_id):\n    hash_value = get_hash(customer_id)\n    return int(hash_value, 16)\n```\n\n```sql\nSELECT \nSUBSTRING(CONVERT(VARCHAR(900),\n                        HASHBYTES('MD5', CAST('Customer1' AS VARCHAR(36))), \n                        1\n                    ),\n                    3, 15\n                ),\nCONVERT(bigint, CONVERT(VARBINARY(900), '0'  + SUBSTRING(CONVERT(VARCHAR(900),\n                        HASHBYTES('MD5', CAST('Customer1' AS VARCHAR(36))), \n                        1\n                    ),\n                    3, 15\n                ), 2))\n```\n\n```sql\nSELECT \nmd5('Customer1')[:15] AS hash_value,\nHASH(md5('Customer1')[:15]) AS hash_to_int\n```\n\n```text\nHash = BECFB907888C8D4\n```\n\n```text\nPython = TSQL = 859338226837014740\n\nDuckDB =        16188616960793580010\n```\n\n```text\nD select md5('Customer1')[:15] as hex, ('0x' || hex)::uint64 as uint64;\n┌─────────────────┬────────────────────┐\n│       hex       │       uint64       │\n│     varchar     │       uint64       │\n├─────────────────┼────────────────────┤\n│ becfb907888c8d4 │ 859338226837014740 │\n└─────────────────┴────────────────────┘\n```\n\n```text\n0x\n```\n\n```text\nuint64\n```\n\n========================================\n\nComments:\n- Thanks! This worked! Do you know we append 0x to the hex value?\n- `0x` is the notation for hexadecimal integers (en.wikipedia.org/wiki/Hexadecimal) in most languages and DuckDB supports parsing it. I couldn't find any other function in the documentation to parse integers in arbitrary base but this worked.\n- This is excellent, except I had 64 bit hexadecimals (eg 0x0123456789ABCDEF, with 8 byte pairs), so I had to cast to unsigned ints, eg `uint64`, because I was getting overflow errors with mere `int64`s. You may need to do this too.\n- @NickCrews thanks, uint64 makes more sense here. The reason int64 worked for OP is because the original code was only using 15 hex digits which would always fit int64. I have edited my answer to change the type to uint64.","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":8,"totalLines":131,"estimatedTokens":874}}36{"id":"stack-79348555","source":"stackoverflow","questionId":79348555,"title":"read csv file delimited by file separator control character (FS/ASCII 28/UTF-8 0x1C) into duckdb using the CLI","tags":["csv","duckdb"],"text":"Title: read csv file delimited by file separator control character (FS/ASCII 28/UTF-8 0x1C) into duckdb using the CLI\nTags: csv, duckdb\nSource: Stack Overflow\n\nQuestion:\nTrying to read from a CSV file using file separator control character (FS/ASCII 28/UTF-8 0x1C) as delimiter into duckdb from the CLI:\n\n```\nCREATE TABLE some_table AS SELECT * FROM read_csv('/some/file.csv', delim='****');\n```\n\nCan someone please suggest the proper way to pass this field delimiter?\n\nThank you!\n\n========================================\n\nTop Answer:\nDuckDB `sniff_csv` function may be able to help here. \n\nhttps://duckdb.org/docs/stable/data/csv/auto_detection\n\n```\nselect distinct\n delimiter\nFROM sniff_csv('/some/file.csv', sample_size = 1000)\n```\n\n========================================\n\nCode:\n```sql\nCREATE TABLE some_table AS SELECT * FROM read_csv('/some/file.csv', delim='**<what goes here????>**');\n```\n\n```bash\nXFS=$(printf '\\034')\ncat << EOF > test.csv\nheader1${XFS}header2${XFS}header3\nvalue1${XFS}value2${XFS}value3\ntest1${XFS}test2${XFS}test3\nEOF\n\nduckdb -c \"FROM read_csv('test.csv', delim = E'\\x1C', header = true)\"\n┌─────────┬─────────┬─────────┐\n│ header1 │ header2 │ header3 │\n│ varchar │ varchar │ varchar │\n├─────────┼─────────┼─────────┤\n│ value1  │ value2  │ value3  │\n│ test1   │ test2   │ test3   │\n└─────────┴─────────┴─────────┘\n```\n\n```sql\nselect distinct\n      delimiter\nFROM  sniff_csv('/some/file.csv', sample_size = 1000)\n```\n\n```text\nsniff_csv\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":4,"totalLines":62,"estimatedTokens":367}}37{"id":"stack-76513797","source":"stackoverflow","questionId":76513797,"title":"In DuckDB, how do I SELECT rows with a certain value in an array?","tags":["duckdb"],"text":"Title: In DuckDB, how do I SELECT rows with a certain value in an array?\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nI've got a table with a field `my_array VARCHAR[]`. I'd like to run a SELECT query that returns rows where the value ('My Term') I'm searching for is in \"my_array\" one or more times.\n\nThese (and a bunch more I tried) don't work:\n\n```\nSELECT * FROM my_table WHERE my_array='My Term';\nSELECT * FROM my_table WHERE 'My Term' IN my_array;\n```\n\n========================================\n\nCode:\n```text\nSELECT * FROM my_table WHERE my_array='My Term';\nSELECT * FROM my_table WHERE 'My Term' IN my_array;\n```\n\n```text\nmy_array VARCHAR[]\n```\n\n```text\nduckdb.sql(\"\"\"\n   with my_table as (\n      select unnest([\n         ['foo', 'bar', 'baz', 'one'], \n         ['omg', 'hello', 'hi'],\n         ['hi', 'hello', 'foo', 'two'], \n      ]) my_array\n   )\n   from my_table\n   where \n      list_contains(my_array, 'foo')\n\"\"\")\n```\n\n```text\n┌───────────────────────┐\n│       my_array        │\n│       varchar[]       │\n├───────────────────────┤\n│ [foo, bar, baz, one]  │\n│ [hi, hello, foo, two] │\n└───────────────────────┘\n```\n\n```text\n.list_contains()\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.976Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":5,"totalLines":54,"estimatedTokens":289}}38{"id":"stack-78738651","source":"stackoverflow","questionId":78738651,"title":"Creating random pairs from a column","tags":["sql","duckdb"],"text":"Title: Creating random pairs from a column\nTags: sql, duckdb\nSource: Stack Overflow\n\nQuestion:\n### The problem\n\nI'm trying to create random pairs from a column using DuckDB.\n\nI have a column of protein accession numbers which looks like this:\n\n```\n┌──────────────┐\n│ protein_upkb │\n│ varchar │\n├──────────────┤\n│ G1XNZ0 │\n│ G1XP19 │\n│ G1XP66 │\n│ G1XP70 │\n│ G1XPL1 │\n│ G1XPQ7 │\n│ G1XQ23 │\n│ G1XQ44 │\n│ G1XQ89 │\n│ G1XQH2 │\n├──────────────┤\n│ 10 rows │\n└──────────────┘\n```\n\nI'm trying to create random pairs of these protein ids such that they look like this:\n\n```\n┌────────────┬────────────┐\n│ p1 │ p2 │\n│ varchar │ varchar │\n├────────────┼────────────┤\n│ G1XNZ0 │ G1XP19 │\n│ G1XP19 │ G1XP66 │\n│ G1XP66 │ G1XP70 │\n│ G1XP70 │ G1XPL1 │\n│ G1XPL1 │ G1XPQ7 │\n│ G1XPQ7 │ G1XQ23 │\n│ G1XQ23 │ G1XQ44 │\n│ G1XQ44 │ G1XQ89 │\n│ G1XQ89 │ G1XQH2 │\n│ G1XQH2 │ G1XNZ0 │\n├────────────┴────────────┤\n│ 10 rows 2 columns │\n└─────────────────────────┘\n```\n\n**N.B**: This is just an example, I've thousands of IDs in the table in question.\n\n### Some things I've tried\n\n### Subqueries\n\nI began by scrambling the order of the proteins by assigning some random number to each row and sorting by it.\n\n```\nCREATE VIEW proteins AS\n SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet');\n\nSELECT * FROM proteins ORDER BY x DESC LIMIT 10;\n```\n\nWhich results in\n\n```\n┌──────────────┬────────────────────┐\n│ protein_upkb │ x │\n│ varchar │ double │\n├──────────────┼────────────────────┤\n│ A0A1H6HM63 │ 0.9999986232724041 │\n│ A0A1G6CK58 │ 0.9999978158157319 │\n│ A0A2C5XBA3 │ 0.9999923389405012 │\n│ A0A1H9T955 │ 0.9999855090864003 │\n│ Q05Q16 │ 0.9999655580613762 │\n│ R5PE70 │ 0.999956940067932 │\n│ R5GUN0 │ 0.9999453630298376 │\n│ A0A0L0UJ42 │ 0.9999357375781983 │\n│ W6ZJY1 │ 0.9999311361461878 │\n│ F6D0F2 │ 0.9999301459174603 │\n├──────────────┴────────────────────┤\n│ 10 rows 2 columns │\n└───────────────────────────────────┘\n```\n\nI then tried to create random pairs using subqueries. One column would be sorted by `x` descending, the other by `x` ascending.\n\nConfusingly (to me), this only creates one random pair rather than the 255,622\nI both expected and need.\n\n```\ncursor = duckdb.sql(\"\"\"\nSELECT\n(SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,\n(SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,\nLIMIT 10;\n\"\"\").show()\n```\n\n```\n┌─────────┬─────────┐\n│ p1 │ p2 │\n│ varchar │ varchar │\n├─────────┼─────────┤\n│ Q28RH7 │ D8LJ06 │\n└─────────┴─────────┘\n```\n\n### SELECTing FROM two VIEWs\n\nI figured that I can create two VIEWs, `proteins1` and `proteins2`. I can then independently randomly sort them using `random()` as I've done before.\n\nFinally, I can create pairs by selecting the `protein_upkb` column from each table.\n\nOnce more, I'm a bit surprised by the outcome.\n\n`p2` is a sequence of random proteins, while `p1` is just one of the proteins.\n\n```\ncursor = duckdb.sql(\"\"\"\nCREATE VIEW proteins1 AS\n SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') \n ORDER BY x ASC;\n\nCREATE VIEW proteins2 AS\n SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') \n ORDER BY x ASC;\n\nSELECT ps1.protein_upkb as p1, ps2.protein_upkb as p2,\nFROM proteins1 as ps1, proteins2 as ps2\nLIMIT 10;\n\"\"\").show()\n```\n\n```\n┌────────────┬────────────┐\n│ p1 │ p2 │\n│ varchar │ varchar │\n├────────────┼────────────┤\n│ A0A394DPL7 │ A0A1I3L166 │\n│ A0A394DPL7 │ A0A0Q3WJP1 │\n│ A0A394DPL7 │ A0A093SP34 │\n│ A0A394DPL7 │ A0A127EQY9 │\n│ A0A394DPL7 │ K6UP11 │\n│ A0A394DPL7 │ A0A1I6M9F9 │\n│ A0A394DPL7 │ A0A0Q3SWF8 │\n│ A0A394DPL7 │ A0A069RD68 │\n│ A0A394DPL7 │ S9ZHA8 │\n│ A0A394DPL7 │ Q5P5L0 │\n├────────────┴────────────┤\n│ 10 rows 2 columns │\n└─────────────────────────┘\n```\n\n### Notebook\n\nYou can test this out in this Colab notebook.\n\n========================================\n\nTop Answer:\nI don't know duckdb. But this would raise a runtime error in almost all RDBMS:\n\n```\nSELECT\n (SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,\n (SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,\nLIMIT 10\n```\n\nWell, first of all, there is a comma too many after p2. But what this query is doing is select one row, because you select from nothing (the main `SELECT` clause has no `FROM` clause). This one row has two columns: p1 and p2. Now everything is fine, as long as the two subqueries return only one row, but we know from your table, they do not. The query is trying to fill each of the two cells in the result row with all values in the table. That would usually produce an error. I see, though, that duckdb supports array data types, so I suppose that the two columns result in being some sort of arrays. (Then even the `ORDER BY` may have some effect, which is not the case in standard SQL where an `ORDER BY` in a subquery is superfluous, because subquery results are unordered data sets by definition.)\n\nAs to the views: In SQL everything is a table :-D There exist stored tables like your proteins table. There exists result tables, as the result from all our queries. There exist subquery results, which are also tables. And there are views, which are just the same. And tables are unordered data sets. An `ORDER BY` in a view makes no sense hence, as it can be ignored completely by the DBMS.\n\nAnyway, you cross join the two views, i.e. combine every row of the first view with every row of the second view. What you see is part of the result, namely the first row of the first view combined with the first ten rows of the second view. (So maybe the DBMS even took the hard work to please your `ORDER BY` clauses :-) You only see the first 10 results, because of the `LIMIT` clause you apply, of course.\n\nWhat you want to do instead is join random rows with other random rows from the same table. Then it depends how much randomness you want. If you just want to get some random pairs where you don't get any protein twice, you might do:\n\n```\nWITH \n data AS \n (\n SELECT protein_upkb, ROW_NUMBER() OVER (ORDER BY random()) AS sortkey \n FROM proteins \n ORDER BY random()\n )\nSELECT \n FIRST(protein_upkb ORDER BY sortkey) AS code1,\n LAST(protein_upkb ORDER BY sortkey) AS code2\nFROM data\nGROUP BY ((sortkey - 1) // 2) -- Integer division 0, 0, 1, 1, 2, 2, etc.\nLIMIT 10;\n```\n\nOr, if you are fine with duplicates, you could just\n\n```\nSELECT \n a.protein_upkb AS code1,\n b.protein_upkb AS code2\nFROM proteins a CROSS JOIN proteins b\nORDER BY random()\nLIMIT 10;\n```\n\nwhich can get you a result like\n\nCODE1\nCODE2\n\nA0A394DPL7\nA0A394DPL7\n\nS9ZHA8\nS9ZHA8\n\nA0A394DPL7\nS9ZHA8\n\nS9ZHA8\nA0A394DPL7\n\n...\n...\n\n========================================\n\nCode:\n```text\n┌──────────────┐\n│ protein_upkb │\n│   varchar    │\n├──────────────┤\n│ G1XNZ0       │\n│ G1XP19       │\n│ G1XP66       │\n│ G1XP70       │\n│ G1XPL1       │\n│ G1XPQ7       │\n│ G1XQ23       │\n│ G1XQ44       │\n│ G1XQ89       │\n│ G1XQH2       │\n├──────────────┤\n│   10 rows    │\n└──────────────┘\n```\n\n```text\n┌────────────┬────────────┐\n│     p1     │     p2     │\n│  varchar   │  varchar   │\n├────────────┼────────────┤\n│ G1XNZ0     │ G1XP19     │\n│ G1XP19     │ G1XP66     │\n│ G1XP66     │ G1XP70     │\n│ G1XP70     │ G1XPL1     │\n│ G1XPL1     │ G1XPQ7     │\n│ G1XPQ7     │ G1XQ23     │\n│ G1XQ23     │ G1XQ44     │\n│ G1XQ44     │ G1XQ89     │\n│ G1XQ89     │ G1XQH2     │\n│ G1XQH2     │ G1XNZ0     │\n├────────────┴────────────┤\n│ 10 rows       2 columns │\n└─────────────────────────┘\n```\n\n```sql\nCREATE VIEW proteins AS\n  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet');\n\nSELECT * FROM proteins ORDER BY x DESC LIMIT 10;\n```\n\n```text\n┌──────────────┬────────────────────┐\n│ protein_upkb │         x          │\n│   varchar    │       double       │\n├──────────────┼────────────────────┤\n│ A0A1H6HM63   │ 0.9999986232724041 │\n│ A0A1G6CK58   │ 0.9999978158157319 │\n│ A0A2C5XBA3   │ 0.9999923389405012 │\n│ A0A1H9T955   │ 0.9999855090864003 │\n│ Q05Q16       │ 0.9999655580613762 │\n│ R5PE70       │  0.999956940067932 │\n│ R5GUN0       │ 0.9999453630298376 │\n│ A0A0L0UJ42   │ 0.9999357375781983 │\n│ W6ZJY1       │ 0.9999311361461878 │\n│ F6D0F2       │ 0.9999301459174603 │\n├──────────────┴────────────────────┤\n│ 10 rows                 2 columns │\n└───────────────────────────────────┘\n```\n\n```text\ncursor = duckdb.sql(\"\"\"\nSELECT\n(SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,\n(SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,\nLIMIT 10;\n\"\"\").show()\n```\n\n```text\n┌─────────┬─────────┐\n│   p1    │   p2    │\n│ varchar │ varchar │\n├─────────┼─────────┤\n│ Q28RH7  │ D8LJ06  │\n└─────────┴─────────┘\n```\n\n```text\ncursor = duckdb.sql(\"\"\"\nCREATE VIEW proteins1 AS\n  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') \n  ORDER BY x ASC;\n\nCREATE VIEW proteins2 AS\n  SELECT protein_upkb, random() as x FROM read_parquet('mini_proteins.parquet') \n  ORDER BY x ASC;\n\nSELECT ps1.protein_upkb as p1, ps2.protein_upkb as p2,\nFROM proteins1 as ps1, proteins2 as ps2\nLIMIT 10;\n\"\"\").show()\n```\n\n```text\n┌────────────┬────────────┐\n│     p1     │     p2     │\n│  varchar   │  varchar   │\n├────────────┼────────────┤\n│ A0A394DPL7 │ A0A1I3L166 │\n│ A0A394DPL7 │ A0A0Q3WJP1 │\n│ A0A394DPL7 │ A0A093SP34 │\n│ A0A394DPL7 │ A0A127EQY9 │\n│ A0A394DPL7 │ K6UP11     │\n│ A0A394DPL7 │ A0A1I6M9F9 │\n│ A0A394DPL7 │ A0A0Q3SWF8 │\n│ A0A394DPL7 │ A0A069RD68 │\n│ A0A394DPL7 │ S9ZHA8     │\n│ A0A394DPL7 │ Q5P5L0     │\n├────────────┴────────────┤\n│ 10 rows       2 columns │\n└─────────────────────────┘\n```\n\n```text\nx\n```\n\n```text\nx\n```\n\n```text\nproteins1\n```\n\n```text\nproteins2\n```\n\n```text\nrandom()\n```\n\n```text\nprotein_upkb\n```\n\n```text\np2\n```\n\n```text\np1\n```\n\n```py\nduckdb.sql(\"\"\"\n  with cte as (\n    select protein_upkb from proteins using sample(10)\n  )\n  select *\n  from cte as c1\n    positional join cte as c2\n\"\"\")\n\n┌──────────────┬──────────────┐\n│ protein_upkb │ protein_upkb │\n│   varchar    │   varchar    │\n├──────────────┼──────────────┤\n│ A0A0F6TCU1   │ A0A4C1ULV9   │\n│ D4YJT4       │ A0A3Q3FTK5   │\n│ A0A319DTU8   │ C6LIN2       │\n│ A0A1Q3D9X9   │ A0A1B3BCY8   │\n│ M5F4R3       │ M1NUZ3       │\n│ A0A553PJQ2   │ A0A165P0W9   │\n│ G7M9F2       │ A0A182JZX3   │\n│ A0A0Q1CIG2   │ G3HMK9       │\n│ C7YU85       │ A0A3Q2E7T6   │\n│ A0A199VI77   │ A0A0R1JQR6   │\n├──────────────┴──────────────┤\n│ 10 rows           2 columns │\n└─────────────────────────────┘\n```\n\n```py\nduckdb.sql(\"\"\"\nwith cte1 as (\n  select ps1.protein_upkb, row_number() over(order by random()) as rn\n  from proteins as ps1\n), cte2 as (\n  select\n    protein_upkb,\n    rn % 2 as col,\n    rn // 2 as r\n  from cte1\n)\npivot cte2\non col\nusing any_value(protein_upkb)\nlimit 10\n\"\"\")\n\n───────┬────────────┬────────────┐\n│   r   │     0      │     1      │\n│ int64 │  varchar   │  varchar   │\n├───────┼────────────┼────────────┤\n│ 66322 │ A0A1N7AVA0 │ A0A175R4H7 │\n│ 66325 │ K9FKM7     │ D8QP02     │\n│ 66327 │ A0A1I5KRT3 │ W0V524     │\n│ 66328 │ A0A4U2YU79 │ A0A452RP46 │\n│ 66334 │ A8RCK1     │ A0A165U1L8 │\n│ 66335 │ A0A3Q3QVI9 │ C7MCJ1     │\n│ 66336 │ Q3SLR9     │ A0A3B4B0Q2 │\n│ 66338 │ A0A1W1XBB2 │ A0A0B7J5C1 │\n│ 66339 │ A0A1I4KH70 │ A0A3S4SEU1 │\n│ 66340 │ A0A1W0D573 │ Q4ZR49     │\n├───────┴────────────┴────────────┤\n│ 10 rows               3 columns │\n└─────────────────────────────────┘\n```\n\n```text\nwhere\n```\n\n```text\nsample()\n```\n\n```text\npositional join\n```\n\n```text\nrow_number()\n```\n\n```text\npivot\n```\n\n```text\nSELECT\n  (SELECT protein_upkb FROM proteins ORDER BY x DESC) as p1,\n  (SELECT protein_upkb FROM proteins ORDER BY x ASC) as p2,\nLIMIT 10\n```\n\n```text\nWITH \n  data AS \n  (\n    SELECT protein_upkb, ROW_NUMBER() OVER (ORDER BY random()) AS sortkey \n    FROM proteins \n    ORDER BY random()\n  )\nSELECT \n  FIRST(protein_upkb ORDER BY sortkey) AS code1,\n  LAST(protein_upkb ORDER BY sortkey) AS code2\nFROM data\nGROUP BY ((sortkey - 1) // 2) -- Integer division 0, 0, 1, 1, 2, 2, etc.\nLIMIT 10;\n```\n\n```text\nSELECT \n  a.protein_upkb AS code1,\n  b.protein_upkb AS code2\nFROM proteins a CROSS JOIN proteins b\nORDER BY random()\nLIMIT 10;\n```\n\n```text\nSELECT\n```\n\n```text\nFROM\n```\n\n```text\nORDER BY\n```\n\n```text\nORDER BY\n```\n\n```text\nORDER BY\n```\n\n```text\nORDER BY\n```\n\n```text\nLIMIT\n```\n\n```text\nwith\n     s as (select protein_upkb p from proteins using sample 8),\n     one as (select p p1 from s limit 4),\n     two as (select p p2 from s offset 4)\nfrom one positional join two;\n```\n\n```text\nprotein_upkb\n```\n\n========================================\n\nComments:\n- I don't understand how you get from 10 IDs all starting with G1 to pairs like (A0A147DQS2 │ A0A1X0GRM6) only by creating \"random pairs of these protein ids\". Where do these new IDs stem from? Is this some change in the protein access code when combined?\n- @ThorstenKettner Ah yes, this is super unclear from how I posed this question. There are many, many rows in this table and I'm just showing the first 10. When you scramble, the first 10 are new set of ids. I'll make an edit to make this clearer, thanks.\n- `from cte as c1 positional join cte as c2` does look weird :-) One would expect each row to be joined to itself, but unlike other DBMS(Oracle, SQL Server, PostgreSQL, MySQL), duckdb seems to produce a new CTE result each time the CTE is referenced. This feels strange, and who knows, if they are going to change this in some future release.\n- Yeah you can also create 2 different ctes for that :)\n- Thanks so much, that second response is exactly what I'm looking for :)\n- Regarding the weirdness noted above - the behavior might seem strange but it won't change as it's an advertised feature. If there's any risk here, it's that someone might inadvertently add the MATERIALIZED keyword :-)","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":35,"totalLines":553,"estimatedTokens":3390}}39{"id":"stack-78088776","source":"stackoverflow","questionId":78088776,"title":"SQL query on arrow duckdb workflow in R","tags":["sql","r","dplyr","apache-arrow","duckdb"],"text":"Title: SQL query on arrow duckdb workflow in R\nTags: sql, r, dplyr, apache-arrow, duckdb\nSource: Stack Overflow\n\nQuestion:\nI'm wondering if it is possible to send an SQL query on `duckdb` during `arrow` workflow in R. (https://duckdb.org/2021/12/03/duck-arrow.html)\n\nI know it is intended to use `dplyr` verbs, but there are some verbs that are not smoothly translated that codes that works on `dplyr` backend but not on either `duckdb` or `arrow`.\n\nI would like to use a direct `SQL` query during the call, something like below example:\n\n```\nlibrary(duckdb)\nlibrary(arrow)\nlibrary(dplyr)\n\n# Open dataset using year,month folder partition\nds %\n # Pass off to DuckDB\n to_duckdb() |>\n SQL_QUERY(\"SELECT * LIMIT 100\") |> # <- something like this\n collect()\n```\n\n========================================\n\nTop Answer:\nWouldn't it be like this since you're doing it `dplyr` style and skipping over the `SQL` style\n\n```\nds %>%\n to_duckdb() %>%\n head(100) %>%\n collect()\n```\n\n========================================\n\nCode:\n```r\nlibrary(duckdb)\nlibrary(arrow)\nlibrary(dplyr)\n\n# Open dataset using year,month folder partition\nds <- arrow::open_dataset(\"nyc-taxi\", partitioning = c(\"year\", \"month\"))\n\nds %>%\n  # Pass off to DuckDB\n  to_duckdb() |>\n  SQL_QUERY(\"SELECT * LIMIT 100\") |> # <- something like this\n  collect()\n```\n\n```text\nduckdb\n```\n\n```text\narrow\n```\n\n```text\ndplyr\n```\n\n```text\ndplyr\n```\n\n```text\nduckdb\n```\n\n```text\narrow\n```\n\n```text\nSQL\n```\n\n```text\nds |>\n  to_duckdb() |>\n  filter(sql(\"vendor_name = 'VTS' AND year = 2009 AND month = 1\")) |>\n  collect()\n```\n\n```text\ndb_tbl <-  ds |>\n      to_duckdb(table_name = \"nyc_tbl\")\n\ncon <- db_tbl$src$con\nDBI::dbGetQuery(con, \"SELECT * FROM 'nyc_tbl' LIMIT 100\")\n```\n\n```text\ndplyr\n```\n\n```text\nto_duckdb()\n```\n\n```text\nds %>%\n    to_duckdb() %>%\n    head(100) %>%\n    collect()\n```\n\n```text\ndplyr\n```\n\n```text\nSQL\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":15,"totalLines":119,"estimatedTokens":468}}40{"id":"stack-72310208","source":"stackoverflow","questionId":72310208,"title":"DuckDB: turn dataframe dictionary column into MAP column","tags":["python","pandas","dataframe","duckdb"],"text":"Title: DuckDB: turn dataframe dictionary column into MAP column\nTags: python, pandas, dataframe, duckdb\nSource: Stack Overflow\n\nQuestion:\nI have a Pandas dataframe with a column containing dictionary values. I'd like to query this dataframe using DuckDB and convert the result to another dataframe, and have the type preserved across the query.\n\nDuckDB has the `MAP` data type which looks like a good match for a dictionary, but when selecting the column it's turned into a `VARCHAR`, and results in a string-type column if I convert back to a dataframe.\n\nIs there some way to preserve the type, or at least a good way to convert the string back to a dictionary when generating the new dataframe?\n\n```\n>>> # Create a dataframe with a column containing a dictionary\n>>> df = pd.DataFrame([[{'some': 'dict', 'with': 'stuff'}]], columns=['mycol'])\n>>> df\n mycol\n0 {'some': 'dict', 'with': 'stuff'}\n>>> type(df['mycol'][0])\n\n>>> # Select that column using DuckDB - it becomes a VARCHAR\n>>> duckdb.query('select mycol from df')\n---------------------\n-- Expression Tree --\n---------------------\nSubquery\n\n---------------------\n-- Result Columns --\n---------------------\n- mycol (VARCHAR)\n\n---------------------\n-- Result Preview --\n---------------------\nmycol\nVARCHAR\n[ Rows: 1]\n{'some': 'dict', 'with': 'stuff'}\n\n>>> # Converting the query result to another dataframe results in a string-type column\n>>> df2 = duckdb.query('select mycol from df').to_df()\n>>> df2\n mycol\n0 {'some': 'dict', 'with': 'stuff'}\n>>> type(df2['mycol'][0])\n\n>>> # An explicit cast to MAP doesn't work\n>>> duckdb.query('select CAST(mycol as MAP(VARCHAR, VARCHAR)) from df')\n---------------------\n-- Expression Tree --\n---------------------\nSubquery\n\n---------------------\n-- Result Columns --\n---------------------\n- CAST(mycol AS MAP) (MAP)\n\n---------------------\n-- Result Preview --\n---------------------\nConversion Error: Conversion Error: Unimplemented type for cast (VARCHAR -> MAP)\n```\n\n========================================\n\nCode:\n```py\n>>> # Create a dataframe with a column containing a dictionary\n>>> df = pd.DataFrame([[{'some': 'dict', 'with': 'stuff'}]], columns=['mycol'])\n>>> df\n                               mycol\n0  {'some': 'dict', 'with': 'stuff'}\n>>> type(df['mycol'][0])\n<class 'dict'>\n\n>>> # Select that column using DuckDB - it becomes a VARCHAR\n>>> duckdb.query('select mycol from df')\n---------------------\n-- Expression Tree --\n---------------------\nSubquery\n\n---------------------\n-- Result Columns  --\n---------------------\n- mycol (VARCHAR)\n\n---------------------\n-- Result Preview  --\n---------------------\nmycol\nVARCHAR\n[ Rows: 1]\n{'some': 'dict', 'with': 'stuff'}\n\n\n>>> # Converting the query result to another dataframe results in a string-type column\n>>> df2 = duckdb.query('select mycol from df').to_df()\n>>> df2\n                               mycol\n0  {'some': 'dict', 'with': 'stuff'}\n>>> type(df2['mycol'][0])\n<class 'str'>\n\n>>> # An explicit cast to MAP doesn't work\n>>> duckdb.query('select CAST(mycol as MAP(VARCHAR, VARCHAR)) from df')\n---------------------\n-- Expression Tree --\n---------------------\nSubquery\n\n---------------------\n-- Result Columns  --\n---------------------\n- CAST(mycol AS MAP<VARCHAR, VARCHAR>) (MAP<VARCHAR, VARCHAR>)\n\n---------------------\n-- Result Preview  --\n---------------------\nConversion Error: Conversion Error: Unimplemented type for cast (VARCHAR -> MAP<VARCHAR, VARCHAR>)\n```\n\n```text\nMAP\n```\n\n```text\nVARCHAR\n```\n\n```py\nimport pyarrow as pa\nimport pandas as pd\nimport duckdb\n\ndf = pd.DataFrame([[{'some': 'dict', 'with': 'stuff'}]], columns=['mycol'])\ncon = duckdb.connect()\narrow_table = pa.Table.from_pandas(df)\n\ncon.execute(\"select * from arrow_table\").fetchall()\n```\n\n========================================\n\nComments:\n- Now it converts Map to Struct if keys are the same in all rows. And then for some reason it cannot cast it to Map.\n- This is a good suggestion, infact I was trying to learn duckdb and after stuggling with the documentation of I just completely switched to pyarrow.","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":148,"estimatedTokens":1011}}41{"id":"stack-76964556","source":"stackoverflow","questionId":76964556,"title":"arrow::to_duckdb coerces int64 columns to doubles","tags":["r","apache-arrow","duckdb"],"text":"Title: arrow::to_duckdb coerces int64 columns to doubles\nTags: r, apache-arrow, duckdb\nSource: Stack Overflow\n\nQuestion:\n`arrow::to_duckdb()` converts int64 columns to a double in the duckdb table. This happens if the `.data` being converted is an R data frame or a parquet file. How can I maintain the int64 data type?\n\n**Example**\n\n```\nlibrary(arrow, warn.conflicts = FALSE)\nlibrary(tidyverse, warn.conflicts = FALSE)\nlibrary(vroom, warn.conflicts = FALSE)\n\n# tibble with an int64 column\ndd # A tibble: 1 × 1\n#> id\n#> \n#> 1 9e15\n\n# it's coereced to a double\nto_duckdb(dd)\n#> # Source: table [1 x 1]\n#> # Database: DuckDB 0.8.1 [root@Darwin 22.5.0:R 4.3.1/:memory:]\n#> id\n#> \n#> 1 9.01e15\n```\n\n========================================\n\nCode:\n```text\nlibrary(arrow, warn.conflicts = FALSE)\nlibrary(tidyverse, warn.conflicts = FALSE)\nlibrary(vroom, warn.conflicts = FALSE)\n\n# tibble with an int64 column\ndd <- vroom(I(\"id\\n9007199254740993\\n\"), col_type = \"I\", delim = \",\")\ndd\n#> # A tibble: 1 × 1\n#>        id\n#>   <int64>\n#> 1    9e15\n\n# it's coereced to a double\nto_duckdb(dd)\n#> # Source:   table<arrow_001> [1 x 1]\n#> # Database: DuckDB 0.8.1 [root@Darwin 22.5.0:R 4.3.1/:memory:]\n#>        id\n#>     <dbl>\n#> 1 9.01e15\n```\n\n```text\narrow::to_duckdb()\n```\n\n```text\n.data\n```\n\n```r\non <- DBI::dbConnect(duckdb::duckdb())\n```\n\n```r\nda <- arrow::arrow_table(id = bit64::as.integer64(\"9007199254740993\"))\nda\n#> Table\n#> 1 rows x 1 columns\n#> $id <int64>\n\n# default for comparison\ncon1 <- DBI::dbConnect(duckdb::duckdb())\n# how we want it\ncon2 <- DBI::dbConnect(duckdb::duckdb(bigint = \"integer64\"))\n\n# using default connection\narrow::to_duckdb(da)\n#> # Source:   table<arrow_001> [1 x 1]\n#> # Database: DuckDB 0.8.1 [root@Darwin 22.6.0:R 4.3.1/:memory:]\n#>        id\n#>     <dbl>\n#> 1 9.01e15\n\n# comparison\narrow::to_duckdb(da, con = con1)\n#> # Source:   table<arrow_002> [1 x 1]\n#> # Database: DuckDB 0.8.1 [root@Darwin 22.6.0:R 4.3.1/:memory:]\n#>        id\n#>     <dbl>\n#> 1 9.01e15\n\n# how we want it\narrow::to_duckdb(da, con = con2)\n#> # Source:   table<arrow_003> [1 x 1]\n#> # Database: DuckDB 0.8.1 [root@Darwin 22.6.0:R 4.3.1/:memory:]\n#>        id\n#>   <int64>\n#> 1    9e15\n```\n\n```text\n?to_duckdb\n```\n\n```text\ncon\n```\n\n```text\narrow_duck_connection()\n```\n\n```text\n?duckdb::duckdb()\n```\n\n```text\nbigint\n```\n\n```text\n\"numeric\"\n```\n\n```text\ncon\n```\n\n```text\nto_duckdb()\n```\n\n```text\n\"integer64\"\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":14,"totalLines":138,"estimatedTokens":601}}42{"id":"stack-73383536","source":"stackoverflow","questionId":73383536,"title":"DuckDB - efficiently insert pandas dataframe to table with sequence","tags":["python","sql","pandas","dataframe","duckdb"],"text":"Title: DuckDB - efficiently insert pandas dataframe to table with sequence\nTags: python, sql, pandas, dataframe, duckdb\nSource: Stack Overflow\n\nQuestion:\n```\nCREATE TABLE temp (\n id UINTEGER,\n name VARCHAR,\n age UINTEGER\n);\nCREATE SEQUENCE serial START 1;\n```\n\nInsertion with series works just fine:\n\n```\nINSERT INTO temp VALUES(nextval('serial'), 'John', 13)\n```\n\nHow I can use the sequence with pandas dataframe?\n\n```\ndata = [['Alex',10],['Bob',12],['Clarke',13]]\ndf = pd.DataFrame(data,columns=['Name','Age'])\nprint(df)\n Name Age\n0 Alex 10\n1 Bob 12\n2 Clarke 13\n\ncon.execute(\"INSERT INTO temp SELECT * FROM df\")\nRuntimeError: Binder Error: table temp has 3 columns but 2 values were supplied\n```\n\nI don't want to iterate item by item. The goal is to efficiently insert 1000s of items from python to DB. I'm ok to change pandas to something else.\n\n========================================\n\nCode:\n```sql\nCREATE TABLE temp (\n    id UINTEGER,\n    name VARCHAR,\n    age UINTEGER\n);\nCREATE SEQUENCE serial START 1;\n```\n\n```sql\nINSERT INTO temp VALUES(nextval('serial'), 'John', 13)\n```\n\n```py\ndata = [['Alex',10],['Bob',12],['Clarke',13]]\ndf = pd.DataFrame(data,columns=['Name','Age'])\nprint(df)\n     Name  Age\n0    Alex   10\n1     Bob   12\n2  Clarke   13\n\ncon.execute(\"INSERT INTO temp SELECT * FROM df\")\nRuntimeError: Binder Error: table temp has 3 columns but 2 values were supplied\n```\n\n```text\ncon.execute(\"INSERT INTO temp SELECT nextval('serial'), Name, Age FROM df\")\n```\n\n========================================\n\nComments:\n- have you tried using pandas `to_sql` function?\n- @oskros DataFrame.to_sql()? How can make a dataframe with SEQUENCE in it? :)","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":4,"totalLines":75,"estimatedTokens":414}}43{"id":"stack-68546824","source":"stackoverflow","questionId":68546824,"title":"DuckDB python API: query composition","tags":["python","duckdb"],"text":"Title: DuckDB python API: query composition\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nSuppose I use DuckDB with python, for querying an Apache parquet file `test.pq` with a table containing two columns `f1` and `f2`.\n\n```\nr1 = duckdb.query(\"\"\"\nSELECT f1 FROM parquet_scan('test.pq') WHERE f2 > 1\n\"\"\")\n```\n\nNow I would like to use `r1` result in another query, like:\n\n```\nduckdb.query(\"\"\"SELECT * FROM r1 WHERE f1 > 10\"\"\")\n```\n\nHowever, last instruction gives: `RuntimeError: Catalog Error: Table with name r1 does not exist!`\n\nAm I missing a DuckDB method equivalent to Apache Spark `registerTempTable()` ?\n\n========================================\n\nCode:\n```text\nr1 = duckdb.query(\"\"\"\nSELECT f1 FROM parquet_scan('test.pq') WHERE f2 > 1\n\"\"\")\n```\n\n```text\nduckdb.query(\"\"\"SELECT * FROM r1 WHERE f1 > 10\"\"\")\n```\n\n```text\ntest.pq\n```\n\n```text\nf1\n```\n\n```text\nf2\n```\n\n```text\nr1\n```\n\n```text\nRuntimeError: Catalog Error: Table with name r1 does not exist!\n```\n\n```text\nregisterTempTable()\n```\n\n```py\nr1 = duckdb.query(\"\"\"\nSELECT f1 FROM parquet_scan('test.pq') WHERE f2 > 1\n\"\"\")\n```\n\n```py\nresult = r1.execute()\n```\n\n```py\nr1.create_view('table_name')\n```\n\n```py\nconn = duckdb.connect()\nconn.execute(\"create table t as SELECT f1 FROM parquet_scan('test.pq') where f2 > 1 \")\n```\n\n```py\nr2 = r1.filter(\"f1>10\")\n```\n\n========================================\n\nComments:\n- Isn't it exactly the other way around, creating a view as in 1 will read the parquet again and again for each query, and 2 will create and in-memory duckdb table and query that? Otherwise, this would completely counter to the intuition I have for a \"view\".","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":13,"totalLines":87,"estimatedTokens":409}}44{"id":"stack-79668006","source":"stackoverflow","questionId":79668006,"title":"DuckDB drop column: no column named that way","tags":["duckdb"],"text":"Title: DuckDB drop column: no column named that way\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nWhy DuckDB tells me there is no column named that way when I try to drop a column?\n\n```\nD DESCRIBE oa_pub;\n┌──────────────┬─────────────┬─────────┬─────────┬──────────────────────────┬─────────┐\n│ column_name │ column_type │ null │ key │ default │ extra │\n│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │\n├──────────────┼─────────────┼─────────┼─────────┼──────────────────────────┼─────────┤\n│ id │ INTEGER │ NO │ PRI │ nextval('oa_pub_id_seq') │ NULL │\n│ project_id │ INTEGER │ NO │ UNI │ NULL │ NULL │\n│ oa_author_id │ VARCHAR │ NO │ UNI │ NULL │ NULL │\n│ oa_pub_id │ VARCHAR │ NO │ UNI │ NULL │ NULL │\n│ oa_pub_json │ JSON │ YES │ NULL │ NULL │ NULL │\n│ oa_pub_id_2 │ VARCHAR │ YES │ NULL │ NULL │ NULL │\n└──────────────┴─────────────┴─────────┴─────────┴──────────────────────────┴─────────┘\nD ALTER TABLE oa_pub DROP COLUMN oa_pub_id;\nCatalog Error:\ntable \"oa_pub\" does not have a column named oa_pub_id\n```\n\n========================================\n\nCode:\n```text\nD DESCRIBE oa_pub;\n┌──────────────┬─────────────┬─────────┬─────────┬──────────────────────────┬─────────┐\n│ column_name  │ column_type │  null   │   key   │         default          │  extra  │\n│   varchar    │   varchar   │ varchar │ varchar │         varchar          │ varchar │\n├──────────────┼─────────────┼─────────┼─────────┼──────────────────────────┼─────────┤\n│ id           │ INTEGER     │ NO      │ PRI     │ nextval('oa_pub_id_seq') │ NULL    │\n│ project_id   │ INTEGER     │ NO      │ UNI     │ NULL                     │ NULL    │\n│ oa_author_id │ VARCHAR     │ NO      │ UNI     │ NULL                     │ NULL    │\n│ oa_pub_id    │ VARCHAR     │ NO      │ UNI     │ NULL                     │ NULL    │\n│ oa_pub_json  │ JSON        │ YES     │ NULL    │ NULL                     │ NULL    │\n│ oa_pub_id_2  │ VARCHAR     │ YES     │ NULL    │ NULL                     │ NULL    │\n└──────────────┴─────────────┴─────────┴─────────┴──────────────────────────┴─────────┘\nD ALTER TABLE oa_pub DROP COLUMN oa_pub_id;\nCatalog Error:\ntable \"oa_pub\" does not have a column named oa_pub_id\n```\n\n```text\nD CREATE TABLE test2 (id INT PRIMARY KEY, name TEXT, surname TEXT, age INT, UNIQUE(surname, age));\nD DESCRIBE test2;\n┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐\n│ column_name │ column_type │  null   │   key   │ default │  extra  │\n│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │\n├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤\n│ id          │ INTEGER     │ NO      │ PRI     │ NULL    │ NULL    │\n│ name        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │\n│ surname     │ VARCHAR     │ YES     │ UNI     │ NULL    │ NULL    │\n│ age         │ INTEGER     │ YES     │ UNI     │ NULL    │ NULL    │\n└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘\nD ALTER TABLE test2 DROP COLUMN surname;\nCatalog Error:\ntable \"test2\" does not have a column named \"surname\"\n```\n\n```text\noa_pub_id\n```\n\n```text\nkey\n```\n\n```text\nDESCRIBE\n```\n\n========================================\n\nComments:\n- Is it possible the `oa_pub_id` column name contains trailing whitespace? e.g. `SET colname = ' oa_pub_id ';`\n- @Dai that came to me also but it's not that. See my answer. But thank you very much for the help indeed!","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":5,"totalLines":80,"estimatedTokens":847}}45{"id":"stack-79692856","source":"stackoverflow","questionId":79692856,"title":"jOOQ dynamic aggregated types","tags":["jooq","duckdb"],"text":"Title: jOOQ dynamic aggregated types\nTags: jooq, duckdb\nSource: Stack Overflow\n\nQuestion:\nI consider jOOQ over DuckDB over Parquet files. The type of parquet columns are not know before hand. Let's consider some column may be an `integer` or a `double`.\n\nI want to `SUM` over given column, receiving an `int`/`long`/`BigInteger` if the column has `int-like` type, or a `float`/`double`/`BigDecimal` if the column has `float-like` type.\n\nThe following code always returns `BigDecimal`, which is fine on `kD` which holds `double`s, but not fine with `kI` which holds `int`.\n\nI would like to output type to be based on `DuckDB` output (which is `HUGEINT`), while it seems to rely on the type provided in the query (which is defaulted to `BigDecimal`).\n\nMy situation is synthetized by:\n\n```\n@Test\npublic void testAggregate() throws SQLException {\n DuckDBConnection c = (DuckDBConnection) DriverManager.getConnection(\"jdbc:duckdb:\");\n DSLContext dslContext = DSL.using(c, SQLDialect.DUCKDB);\n\n String tableName = \"someTable\";\n\n dslContext.createTable(tableName).column(\"kI\", SQLDataType.INTEGER).column(\"kD\", SQLDataType.DOUBLE).execute();\n dslContext.insertInto(DSL.table(tableName), DSL.field(\"kI\"), DSL.field(\"kD\")).values(123, 12.34).execute();\n dslContext.insertInto(DSL.table(tableName), DSL.field(\"kI\"), DSL.field(\"kD\")).values(234, 23.45).execute();\n\n try (Statement statement = c.createStatement()) {\n statement.execute(\"SELECT SUM(kI), SUM(kD) FROM someTable\");\n ResultSet resultSet = statement.getResultSet();\n if (resultSet.next()) {\n // Prints `class java.math.BigInteger`\n System.out.println(resultSet.getObject(1).getClass());\n // Prints `class Double`\n System.out.println(resultSet.getObject(2).getClass());\n }\n }\n\n // We need to proceed over `kI` and `kD` without knowing their type before hand\n onFieldName(dslContext, tableName, \"kI\");\n onFieldName(dslContext, tableName, \"kD\");\n\n onFieldNameCoerceBigInteger(dslContext, tableName, \"kI\");\n onFieldNameCoerceBigInteger(dslContext, tableName, \"kD\");\n\n // Fails with `Not supported by dialect : Type class java.lang.Number is not supported in dialect null`\n onFieldNameCoerceNumber(dslContext, tableName, \"kI\");\n onFieldNameCoerceNumber(dslContext, tableName, \"kD\");\n}\n\nprivate void onFieldName(DSLContext dslContext, String tableName, String fieldName) {\n Field field = (Field) DSL.field(fieldName);\n SelectJoinStep> queryInteger = dslContext.select(DSL.sum(field)).from(DSL.table(tableName));\n\n queryInteger.stream().findAny().ifPresent(row -> {\n // prints `class java.math.BigDecimal`\n System.out.println(row.get(0).getClass());\n });\n}\n\nprivate void onFieldNameCoerceBigInteger(DSLContext dslContext, String tableName, String fieldName) {\n Field field = (Field) DSL.field(fieldName);\n SelectJoinStep> queryInteger =\n dslContext.select(DSL.sum(field).coerce(BigInteger.class)).from(DSL.table(tableName));\n\n queryInteger.stream().findAny().ifPresent(row -> {\n // prints `class java.math.BigDecimal`\n System.out.println(row.get(0).getClass());\n });\n}\n\nprivate void onFieldNameCoerceNumber(DSLContext dslContext, String tableName, String fieldName) {\n Field field = (Field) DSL.field(fieldName);\n SelectJoinStep> queryInteger =\n dslContext.select(DSL.sum(field).coerce(Number.class)).from(DSL.table(tableName));\n\n queryInteger.stream().findAny().ifPresent(row -> {\n // prints `class java.math.BigDecimal`\n System.out.println(row.get(0).getClass());\n });\n}\n```\n\nRelates with:\n\n- https://github.com/jOOQ/jOOQ/issues/9555\n\n- https://github.com/jOOQ/jOOQ/issues/15439\n\n========================================\n\nCode:\n```java\n@Test\npublic void testAggregate() throws SQLException {\n    DuckDBConnection c = (DuckDBConnection) DriverManager.getConnection(\"jdbc:duckdb:\");\n    DSLContext dslContext = DSL.using(c, SQLDialect.DUCKDB);\n\n    String tableName = \"someTable\";\n\n    dslContext.createTable(tableName).column(\"kI\", SQLDataType.INTEGER).column(\"kD\", SQLDataType.DOUBLE).execute();\n    dslContext.insertInto(DSL.table(tableName), DSL.field(\"kI\"), DSL.field(\"kD\")).values(123, 12.34).execute();\n    dslContext.insertInto(DSL.table(tableName), DSL.field(\"kI\"), DSL.field(\"kD\")).values(234, 23.45).execute();\n\n    try (Statement statement = c.createStatement()) {\n        statement.execute(\"SELECT SUM(kI), SUM(kD) FROM someTable\");\n        ResultSet resultSet = statement.getResultSet();\n        if (resultSet.next()) {\n            // Prints `class java.math.BigInteger`\n            System.out.println(resultSet.getObject(1).getClass());\n            // Prints `class Double`\n            System.out.println(resultSet.getObject(2).getClass());\n        }\n    }\n\n    // We need to proceed over `kI` and `kD` without knowing their type before hand\n    onFieldName(dslContext, tableName, \"kI\");\n    onFieldName(dslContext, tableName, \"kD\");\n\n    onFieldNameCoerceBigInteger(dslContext, tableName, \"kI\");\n    onFieldNameCoerceBigInteger(dslContext, tableName, \"kD\");\n\n    // Fails with `Not supported by dialect : Type class java.lang.Number is not supported in dialect null`\n    onFieldNameCoerceNumber(dslContext, tableName, \"kI\");\n    onFieldNameCoerceNumber(dslContext, tableName, \"kD\");\n}\n\nprivate void onFieldName(DSLContext dslContext, String tableName, String fieldName) {\n    Field<Number> field = (Field) DSL.field(fieldName);\n    SelectJoinStep<Record1<BigDecimal>> queryInteger = dslContext.select(DSL.sum(field)).from(DSL.table(tableName));\n\n    queryInteger.stream().findAny().ifPresent(row -> {\n        // prints `class java.math.BigDecimal`\n        System.out.println(row.get(0).getClass());\n    });\n}\n\nprivate void onFieldNameCoerceBigInteger(DSLContext dslContext, String tableName, String fieldName) {\n    Field<Number> field = (Field) DSL.field(fieldName);\n    SelectJoinStep<Record1<BigInteger>> queryInteger =\n            dslContext.select(DSL.sum(field).coerce(BigInteger.class)).from(DSL.table(tableName));\n\n    queryInteger.stream().findAny().ifPresent(row -> {\n        // prints `class java.math.BigDecimal`\n        System.out.println(row.get(0).getClass());\n    });\n}\n\nprivate void onFieldNameCoerceNumber(DSLContext dslContext, String tableName, String fieldName) {\n    Field<Number> field = (Field) DSL.field(fieldName);\n    SelectJoinStep<Record1<Number>> queryInteger =\n            dslContext.select(DSL.sum(field).coerce(Number.class)).from(DSL.table(tableName));\n\n    queryInteger.stream().findAny().ifPresent(row -> {\n        // prints `class java.math.BigDecimal`\n        System.out.println(row.get(0).getClass());\n    });\n}\n```\n\n```text\ninteger\n```\n\n```text\ndouble\n```\n\n```text\nSUM\n```\n\n```text\nint\n```\n\n```text\nlong\n```\n\n```text\nBigInteger\n```\n\n```text\nint-like\n```\n\n```text\nfloat\n```\n\n```text\ndouble\n```\n\n```text\nBigDecimal\n```\n\n```text\nfloat-like\n```\n\n```text\nBigDecimal\n```\n\n```text\nkD\n```\n\n```text\ndouble\n```\n\n```text\nkI\n```\n\n```text\nint\n```\n\n```text\nDuckDB\n```\n\n```text\nHUGEINT\n```\n\n```text\nBigDecimal\n```\n\n```java\naggregate(systemName(\"sum\"), kI.getDataType(), kI);\n```\n\n```text\nDSL.aggregate()\n```\n\n```text\nBigDecimal\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":23,"totalLines":247,"estimatedTokens":1754}}46{"id":"stack-76493134","source":"stackoverflow","questionId":76493134,"title":"DuckDB slower than Polars in single table over + groupby context","tags":["python","duckdb"],"text":"Title: DuckDB slower than Polars in single table over + groupby context\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nFor the following toy example which involves both calculations `over` window and `groupby` aggregations, `DuckDB` performs nearly 3x slower than `Polars` in `Python`. Both give exactly the same results.\n\nIs this kind of benchmarking result as expected, because `DuckDB` is designed and should be used more for cross-dataframe/table operations?\n\nOr, is it just because the inefficiency comes from the way my SQL query is written?\n\n```\nimport time\n\nimport duckdb\nimport numpy as np\nimport polars as pl\n\n## example dataframe\nrng = np.random.default_rng(1)\n\nnrows = 10_000_000\ndf = pl.DataFrame(\n dict(\n id=rng.integers(1, 100, nrows),\n id2=rng.integers(1, 1_000, nrows),\n v1=rng.normal(0, 1, nrows),\n v2=rng.normal(0, 1, nrows),\n v3=rng.normal(0, 1, nrows),\n v4=rng.normal(0, 1, nrows),\n )\n)\n\n## polars\nstart = time.perf_counter()\nres = (\n df.select(\n [\n \"id\",\n \"id2\",\n pl.col(\"v1\") - pl.col(\"v1\").mean().over([\"id\", \"id2\"]),\n pl.col(\"v2\") - pl.col(\"v2\").mean().over([\"id\", \"id2\"]),\n pl.col(\"v3\") - pl.col(\"v3\").mean().over([\"id\", \"id2\"]),\n pl.col(\"v4\") - pl.col(\"v4\").mean().over([\"id\", \"id2\"]),\n ]\n )\n .groupby([\"id\", \"id2\"])\n .agg(\n [\n (pl.col(\"v1\") * pl.col(\"v2\")).sum().alias(\"ans1\"),\n (pl.col(\"v3\") * pl.col(\"v4\")).sum().alias(\"ans2\"),\n ]\n )\n)\ntime.perf_counter() - start\n# 1.0977217499166727\n\n## duckdb\nstart = time.perf_counter()\nres2 = (\n duckdb.sql(\n \"\"\"\n SELECT id, id2,\n v1 - mean(v1) OVER (PARTITION BY id, id2) as v1,\n v2 - mean(v2) OVER (PARTITION BY id, id2) as v2,\n v3 - mean(v3) OVER (PARTITION BY id, id2) as v3,\n v4 - mean(v4) OVER (PARTITION BY id, id2) as v4,\n FROM df\n \"\"\"\n )\n .aggregate(\n \"id, id2, sum(v1 * v2) as ans1, sum(v3 * v4) as ans2\",\n \"id, id2\",\n )\n .pl()\n)\ntime.perf_counter() - start\n# 3.549897135235369\n```\n\n========================================\n\nTop Answer:\nI'm not an SQL expert, but it looks like your query is equivalent to:\n\n```\nduckdb.sql(\"\"\"\n with mean as (\n from \n df \n select \n id,\n id2,\n mean(v1) v1, \n mean(v2) v2, \n mean(v3) v3, \n mean(v4) v4,\n group by id, id2\n )\n from \n df left join mean using (id, id2)\n select\n id,\n id2,\n sum((df.v1 - mean.v1) * (df.v2 - mean.v2)) ans1,\n sum((df.v3 - mean.v3) * (df.v4 - mean.v4)) ans2,\n group by id, id2\n\"\"\")\n```\n\nThe polars equivalent would be similar to:\n\n```\n(df.groupby('id', 'id2')\n .agg(\n ans1 = (pl.col('v1') - pl.col('v1').mean()) * (pl.col('v2') - pl.col('v2').mean()),\n ans2 = (pl.col('v3') - pl.col('v3').mean()) * (pl.col('v4') - pl.col('v4').mean()),\n )\n .with_columns(\n pl.col('ans1', 'ans2').list.sum()\n )\n)\n```\n\nThe timings are much closer in those cases:\n\nMethod\nTime\n\npolars v1\n1.978311300976202\n\nduckdb v1\n3.188773022033274\n\npolars v2\n1.434564991039224\n\nduckdb v2\n1.2010211820015684\n\nduckdb v3\n3.0945987859740853\n\n========================================\n\nCode:\n```text\nimport time\n\nimport duckdb\nimport numpy as np\nimport polars as pl\n\n## example dataframe\nrng = np.random.default_rng(1)\n\nnrows = 10_000_000\ndf = pl.DataFrame(\n    dict(\n        id=rng.integers(1, 100, nrows),\n        id2=rng.integers(1, 1_000, nrows),\n        v1=rng.normal(0, 1, nrows),\n        v2=rng.normal(0, 1, nrows),\n        v3=rng.normal(0, 1, nrows),\n        v4=rng.normal(0, 1, nrows),\n    )\n)\n\n## polars\nstart = time.perf_counter()\nres = (\n    df.select(\n        [\n            \"id\",\n            \"id2\",\n            pl.col(\"v1\") - pl.col(\"v1\").mean().over([\"id\", \"id2\"]),\n            pl.col(\"v2\") - pl.col(\"v2\").mean().over([\"id\", \"id2\"]),\n            pl.col(\"v3\") - pl.col(\"v3\").mean().over([\"id\", \"id2\"]),\n            pl.col(\"v4\") - pl.col(\"v4\").mean().over([\"id\", \"id2\"]),\n        ]\n    )\n    .groupby([\"id\", \"id2\"])\n    .agg(\n        [\n            (pl.col(\"v1\") * pl.col(\"v2\")).sum().alias(\"ans1\"),\n            (pl.col(\"v3\") * pl.col(\"v4\")).sum().alias(\"ans2\"),\n        ]\n    )\n)\ntime.perf_counter() - start\n# 1.0977217499166727\n\n## duckdb\nstart = time.perf_counter()\nres2 = (\n    duckdb.sql(\n        \"\"\"\n        SELECT id, id2,\n        v1 - mean(v1) OVER (PARTITION BY id, id2) as v1,\n        v2 - mean(v2) OVER (PARTITION BY id, id2) as v2,\n        v3 - mean(v3) OVER (PARTITION BY id, id2) as v3,\n        v4 - mean(v4) OVER (PARTITION BY id, id2) as v4,\n        FROM df\n        \"\"\"\n    )\n    .aggregate(\n        \"id, id2, sum(v1 * v2) as ans1, sum(v3 * v4) as ans2\",\n        \"id, id2\",\n    )\n    .pl()\n)\ntime.perf_counter() - start\n# 3.549897135235369\n```\n\n```text\nover\n```\n\n```text\ngroupby\n```\n\n```text\nDuckDB\n```\n\n```text\nPolars\n```\n\n```text\nPython\n```\n\n```text\nDuckDB\n```\n\n```sql\nload\nSELECT SETSEED(0.8675309);\nCREATE TABLE df AS\n    SELECT \n        (random() * 100)::INTEGER + 1 AS id,\n        (random() * 1000)::INTEGER + 1 AS id2,\n        random() AS v1,\n        random() AS v2,\n        random() AS v3,\n        random() AS v4,\n    FROM range(10000000);\n\nrun\nSELECT id, id2, sum(v1 * v2) as ans1, sum(v3 * v4) as ans2\nFROM (\n    SELECT id, id2,\n    v1 - mean(v1) OVER (PARTITION BY id, id2) as v1,\n    v2 - mean(v2) OVER (PARTITION BY id, id2) as v2,\n    v3 - mean(v3) OVER (PARTITION BY id, id2) as v3,\n    v4 - mean(v4) OVER (PARTITION BY id, id2) as v4,\n    FROM df\n)\nGROUP BY ALL\n```\n\n```text\nname    run timing\nbenchmark/micro/window/temp.benchmark   1   0.930582\nbenchmark/micro/window/temp.benchmark   2   0.914845\nbenchmark/micro/window/temp.benchmark   3   0.965103\nbenchmark/micro/window/temp.benchmark   4   0.951137\nbenchmark/micro/window/temp.benchmark   5   0.945187\n```\n\n```text\nname    run timing\nbenchmark/micro/window/temp.benchmark   1   0.823040\nbenchmark/micro/window/temp.benchmark   2   0.826832\nbenchmark/micro/window/temp.benchmark   3   0.851842\nbenchmark/micro/window/temp.benchmark   4   0.797956\nbenchmark/micro/window/temp.benchmark   5   0.861512\n```\n\n```text\nduckdb.sql(\"\"\"\n   with mean as (\n      from \n         df \n      select \n         id,\n         id2,\n         mean(v1) v1, \n         mean(v2) v2, \n         mean(v3) v3, \n         mean(v4) v4,\n      group by id, id2\n   )\n   from \n      df left join mean using (id, id2)\n   select\n      id,\n      id2,\n      sum((df.v1 - mean.v1) * (df.v2 - mean.v2)) ans1,\n      sum((df.v3 - mean.v3) * (df.v4 - mean.v4)) ans2,\n   group by id, id2\n\"\"\")\n```\n\n```text\n(df.groupby('id', 'id2')\n   .agg(\n      ans1 = (pl.col('v1') - pl.col('v1').mean()) * (pl.col('v2') - pl.col('v2').mean()),\n      ans2 = (pl.col('v3') - pl.col('v3').mean()) * (pl.col('v4') - pl.col('v4').mean()),\n   )\n   .with_columns(\n      pl.col('ans1', 'ans2').list.sum()\n   )\n)\n```\n\n========================================\n\nComments:\n- Indeed, your query is actually equivalent to mine, but it is actually faster. I am not sure why it is the case. But it is still 2s which is still 2x slower than Polars one.\n- By the way, I am only measuring the first time run speed. Because subsequent runs will be much faster due to caching.\n- Ah, I had all the tests in a single file - silly me. I've split them all into separate files and updated the timings. The duckdb v2 still runs faster for me. I added in @hawkfish's query also as v3.\n- For your duckdb v2, from my benchmarking results, the first time takes 1.98s, and subsequent runs take only 0.33s. So, seems like your timing is average time taken?\n- They're all first time run timings.\n- I am using the latest version - 0.8.1.\n- Is it because there are some overhead in my calculations as there are conversions from/to Polars DataFrame? And, what you are testing is pure SQL.\n- Also, the only difference between my query and yours is my query is separate into two parts (one for prep and one for final aggregation for clear and better understanding). Does this matter? I assume not because there should be query optimizations done under the hood anyways?\n- DuckDB uses the same internal in-memory table API to scan both internal tables and data frames, so I doubt there is any conversion difference. Also, I'm not sure what you mean by \"my query is separate into two parts\" - I believe the stacked Python calls will be collapsed into a single query. Incidentally, I used the benchmarking framework because it does a warm-up run precisely to avoid the 1.98/0.33 difference you observed, before performing 5 runs in quick succession using pre-built data. Benchmarking is hard.","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":12,"totalLines":333,"estimatedTokens":2074}}47{"id":"stack-78832305","source":"stackoverflow","questionId":78832305,"title":"DuckDB constructing a full GeoJSON feature collection","tags":["sql","node.js","geojson","duckdb"],"text":"Title: DuckDB constructing a full GeoJSON feature collection\nTags: sql, node.js, geojson, duckdb\nSource: Stack Overflow\n\nQuestion:\n### What I've done:\n\n- I'm using the nodeJS DuckDB client\n\n- I've loaded the spatial extension and JSON extension\n\n- I've read a parquet file with a WKB geometry column into duckDB\n\n- I want to perform a query on all of my data. I then want to return back the query results as GeoJSON (right now I'm using a LIMIT for debugging purposes)\n\n- The documentation for St_AsGeoJSON states \"This does not return a complete GeoJSON document, only the geometry fragment. To construct a complete GeoJSON document or feature, look into using the DuckDB JSON extension in conjunction with this function.\" I'm having a hard time figuring out how to do that\n\n### Code:\n\n```\nconst db = await Database.create(\":memory:\");\nawait db.run(`INSTALL spatial; LOAD spatial`);\nawait db.run(`INSTALL json; LOAD json`);\nawait db.run(`\n CREATE TABLE duckdata AS \n SELECT * EXCLUDE ${wkbColName}, ST_GeomFromWKB(${wkbColName}) AS geometry\n FROM read_parquet('${fileName}/*.parquet', hive_partitioning = true)`\n );\n\n const con = await db.connect();\n let rows = await con.all(`\n COPY (SELECT ST_AsGeoJSON(geometry) AS geometry FROM duckdata LIMIT 10) TO 'my.json' (ARRAY true)`\n );\n```\n\n### What I'm currently getting back as output (only showing two rows for simplicity sake):\n\n```\n[\n {\"geometry\":\n {\"type\":\"Point\",\n \"coordinates\":[-73.79132080078125,40.64582824707031]}\n },\n {\"geometry\":\n {\"type\":\"Point\",\n \"coordinates\":[-73.79132080078125,40.64582824707031]}\n },\n]\n```\n\n### What I'd like to get back as output:\n\n- I'd like to get back an array of dictionary object keys (I don't want to write to .json file) and I'd like to set another json dictionary to include column attributes that looks something like this:\n\n```\n[\n {\"type\" : \"Feature\", \n \"properties\" : { \n \"capacity\" : \"10\", \n \"type\" : \"U-Rack\",\n \"mount\" : \"Surface\"\n }, \n \"geometry\" : { \n \"type\" : \"Point\", \n \"coordinates\" : [ -71.073283, 42.417500 ] \n }\n},\n {\"type\" : \"Feature\", \n \"properties\" : { \n \"capacity\" : \"10\", \n \"type\" : \"U-Rack\",\n \"mount\" : \"Surface\"\n }, \n \"geometry\" : { \n \"type\" : \"Point\", \n \"coordinates\" : [ -71.073283, 42.417500 ] \n }\n},\n]\n```\n\n### What I've tried:\n\n```\nlet rows = await con.all(`\n COPY (SELECT * EXCLUDE geometry AS properties, ST_AsGeoJSON(geometry) AS geometry FROM duckdata LIMIT 10) TO 'my.json' (ARRAY true)`\n );\n```\n\n- I get an error `Duplicate struct entry name \"properties\"`. No matter what I change the name of `properties` too I still get this error\n\n- I can't figure out how to return the values as an array and not as .json file\n\nI've also tried this:\n\n```\nlet rows = await con.all(`\n COPY (\n SELECT \n json_object(\n 'type', 'Feature', \n 'properties', json_object(\n 'vendorId', VendorID\n ),\n 'geometry', ST_AsGeoJSON(geometry)\n ) \n FROM duckdata \n LIMIT 10 \n ) TO 'my.json' (ARRAY true)`\n );\n```\n\nwith the output of:\n\n```\n[\n {\"json_object('type', 'Feature', 'properties', json_object('vendorId', VendorID), 'geometry', st_asgeojson(geometry))\":{\"type\":\"Feature\",\"properties\":{\"vendorId\":\"2\"},\"geometry\":{\"type\":\"Point\",\"coordinates\":[-73.79132080078125,40.64582824707031]}}},\n {\"json_object('type', 'Feature', 'properties', json_object('vendorId', VendorID), 'geometry', st_asgeojson(geometry))\":{\"type\":\"Feature\",\"properties\":{\"vendorId\":\"1\"},\"geometry\":{\"type\":\"Point\",\"coordinates\":[-73.99661254882812,40.766761779785156]}}},\n]\n```\n\n- another option is I could just return all the data as an array and then use a for loop to build a geojson object from the data but this would take much longer and I want to utilize the performance boost that duckdb offers with SQL\n\n========================================\n\nTop Answer:\nI worked around it in python using GDAL Driver. If you want to export it as a file. I'm not sure it'll work as variable in node.js, but there it is:\nlet rows = await con.all(`COPY (SELECT geometry FROM duckdata LIMIT 10) TO 'my.json' WITH (FORMAT GDAL, DRIVER 'GeoJSON')`);\n\n========================================\n\nCode:\n```js\nconst db = await Database.create(\":memory:\");\nawait db.run(`INSTALL spatial; LOAD spatial`);\nawait db.run(`INSTALL json; LOAD json`);\nawait db.run(`\n        CREATE TABLE duckdata AS \n        SELECT * EXCLUDE ${wkbColName}, ST_GeomFromWKB(${wkbColName}) AS geometry\n        FROM read_parquet('${fileName}/*.parquet', hive_partitioning = true)`\n      );\n\n const con = await db.connect();\n let rows = await con.all(`\n      COPY (SELECT ST_AsGeoJSON(geometry) AS geometry FROM duckdata LIMIT 10) TO 'my.json' (ARRAY true)`\n );\n```\n\n```json\n[\n    {\"geometry\":\n        {\"type\":\"Point\",\n         \"coordinates\":[-73.79132080078125,40.64582824707031]}\n    },\n    {\"geometry\":\n        {\"type\":\"Point\",\n         \"coordinates\":[-73.79132080078125,40.64582824707031]}\n    },\n]\n```\n\n```json\n[\n       {\"type\" : \"Feature\", \n        \"properties\" : {  \n            \"capacity\" : \"10\", \n            \"type\" : \"U-Rack\",\n            \"mount\" : \"Surface\"\n        }, \n        \"geometry\" : { \n            \"type\" : \"Point\", \n            \"coordinates\" : [ -71.073283, 42.417500 ] \n        }\n},\n       {\"type\" : \"Feature\", \n        \"properties\" : {  \n            \"capacity\" : \"10\", \n            \"type\" : \"U-Rack\",\n            \"mount\" : \"Surface\"\n        }, \n        \"geometry\" : { \n            \"type\" : \"Point\", \n            \"coordinates\" : [ -71.073283, 42.417500 ] \n        }\n},\n]\n```\n\n```js\nlet rows = await con.all(`\n      COPY (SELECT * EXCLUDE geometry AS properties, ST_AsGeoJSON(geometry) AS geometry FROM duckdata LIMIT 10) TO 'my.json' (ARRAY true)`\n    );\n```\n\n```js\nlet rows = await con.all(`\n      COPY (\n        SELECT \n          json_object(\n            'type', 'Feature', \n            'properties', json_object(\n               'vendorId', VendorID\n            ),\n            'geometry', ST_AsGeoJSON(geometry)\n          ) \n          FROM duckdata \n          LIMIT 10 \n      ) TO 'my.json' (ARRAY true)`\n    );\n```\n\n```json\n[\n    {\"json_object('type', 'Feature', 'properties', json_object('vendorId', VendorID), 'geometry', st_asgeojson(geometry))\":{\"type\":\"Feature\",\"properties\":{\"vendorId\":\"2\"},\"geometry\":{\"type\":\"Point\",\"coordinates\":[-73.79132080078125,40.64582824707031]}}},\n    {\"json_object('type', 'Feature', 'properties', json_object('vendorId', VendorID), 'geometry', st_asgeojson(geometry))\":{\"type\":\"Feature\",\"properties\":{\"vendorId\":\"1\"},\"geometry\":{\"type\":\"Point\",\"coordinates\":[-73.99661254882812,40.766761779785156]}}},\n]\n```\n\n```text\nDuplicate struct entry name \"properties\"\n```\n\n```text\nproperties\n```\n\n```sql\nCREATE TABLE mytable AS \nSELECT \n    * EXCLUDE WKBColumn, \n    ST_GeomFromWKB(WKBColumn) AS geometry\nFROM \n    read_parquet('${sourceConfig.fileName}/*.parquet', hive_partitioning = true);\n```\n\n```sql\nSELECT \n    * EXCLUDE geometry, \n    ST_AsGeoJSON(geometry) AS geometry \nFROM \n    mytable\n```\n\n```js\nconst metadata = config.properties || {};\nconst columns = Object.keys(data[0]);\n\nreturn {\n  type: \"FeatureCollection\",\n  features: data.map((row) =>\n    formatFeature(row, columns, metadata.idField)\n  ),\n  properties: metadata,\n};\n  \n\nfunction formatFeature(values, columns, idField) {\n  let feature = {\n    type: \"Feature\",\n    properties: {},\n    geometry: {\n      type: \"Point\",\n      coordinates: [],\n    },\n  };\n\n  for (let i = 0; i < columns.length; i++) {\n    const value = values[columns[i]];\n\n    if (columns[i] === \"geometry\") {\n      let geom = values[columns[i]]\n      var geometry = JSON.parse(geom);\n      feature.geometry = geometry;\n    } else {\n      if (columns[i] == idField) {\n        feature[\"id\"] = value.toString();\n      }\n      feature.properties[columns[i]] = value;\n    }\n  }\n\n  return feature;\n}\n```\n\n```text\nCOPY (SELECT geometry FROM duckdata LIMIT 10) TO 'my.json' WITH (FORMAT GDAL, DRIVER 'GeoJSON')\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":12,"totalLines":288,"estimatedTokens":1954}}48{"id":"stack-79755866","source":"stackoverflow","questionId":79755866,"title":"How to alter the datatype of a Column?","tags":["c","segmentation-fault","duckdb"],"text":"Title: How to alter the datatype of a Column?\nTags: c, segmentation-fault, duckdb\nSource: Stack Overflow\n\nQuestion:\nI want to change the data type of a column of a table in a DuckDB Database.\n\nWith\n\n```\nquery2_c= ALTER TABLE populationShort ALTER Year SET DATA TYPE DATE;\n```\n\n(C Language Binding) I get `Segmentation fault (core dumped)` error.\n\nThe description of the segmentation fault error, obtained with the `gdb`debugger, is :\n\n```\nduckdb::DeprecatedMaterializeResult(duckdb_result*)\n```\n\nHow to correctly modify via query the data type of the column \"Year\"?\n\nI upgraded duckdb version from 1.2.0 to the latest 1.3.2.\nAnd the problem persists.\nSo it must be something in the code used\n\nThis is the complete code:\n\n```\nduckdb_database CSVDuckDB = NULL;\n duckdb_connection CSVDuckDBConnection = NULL;\n duckdb_result CSVDuckDBResult;\n duckdb_state CSVDuckDBState;\n\n const char* CSVDuckDBCompletePath = CSVDuckDBCompletePath_s.c_str();\n if (duckdb_open(CSVDuckDBCompletePath, &CSVDuckDB) == DuckDBError)\n {\n fprintf(stderr, \"Failed to open CSVDuckDB\\n\");\n // Clean-up\n duckdb_destroy_result(&CSVDuckDBResult);\n duckdb_disconnect(&CSVDuckDBConnection);\n duckdb_close(&CSVDuckDB);\n }\n\n if (duckdb_connect(CSVDuckDB, &CSVDuckDBConnection) == DuckDBError)\n {\n fprintf(stderr, \"Failed to open connection to CSVDuckDB\\n\");\n // Clean-up\n duckdb_destroy_result(&CSVDuckDBResult);\n duckdb_disconnect(&CSVDuckDBConnection);\n duckdb_close(&CSVDuckDB);\n }\n\n std::string justname_s_pure = justname_s_splitted[0];\n std::string gettablename = std::format(\"{}\", justname_s_pure);\n std::cout From the complete output:\n\n```\ngettablename= populationShort\nquery_c= CREATE OR REPLACE TABLE populationShort AS SELECT * FROM \nread_csv('/home/raphy/Downloads/CSVFiles/populationShort.csv', \nstrict_mode = false, ignore_errors = true);\nduckdb_query is ok\nCSVDuckDBReturnType= 2\nquery_c= SELECT column_name, ordinal_position, data_type FROM \ninformation_schema.columns;\nrow:0\nCountry Name VARCHAR \nrow:1\nCountry Code VARCHAR \nrow:2\nYear BIGINT IT HAS TO BE CHANGED TO DATE TYPE\nquery2_c= ALTER TABLE populationShort ALTER 'Year' SET DATA TYPE \nDATE;\nduckdb_query of altering the data type produced DuckDBError\n\nrow:3\nSegmentation fault (core dumped)\n\n(base) raphy@raohy:/var/crash$ gdb /home/raphy/MyPrj/builddir/\nMyPrj ./core_MyPrj.27982\n\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0 0x00007652aed97360 in \nduckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n from /home/raphy/MyPrj/./src/DuckDB/lib/libduckdb.so\n```\n\nit is clear that the code causing the segmentation fault is the following part:\n\n```\n// https://duckdb.org/docs/stable/sql/statements/alter_table.html\n std::string query2 = \"ALTER TABLE \";\n query2.append(gettablename);\n query2.append(\" ALTER '\");\n std::string getValIdx0s = std::format(\"{}\", val_idx_0_s);\n query2.append(getValIdx0s);\n query2.append(\"' SET DATA TYPE USING MAKE_DATE('year',1,1);\"); // N.B.: or with the alternative presented in Update 01, see below in the question.\n const char* query2_c = query2.c_str();\n std::cout This is the content of `populationShort.csv` file :\n\n```\nCountry Name,Country Code,Year,Value\nAruba,ABW,1960,54922\nAruba,ABW,1961,55578\nAruba,ABW,1962,56320\n```\n\nAnd these are the `date_csv_fields` and `duckdbNumericalDataTypes` vectors:\n\n```\nstd::vector date_csv_fields = {\n \"month\",\n \"year\",\n \"day\"\n };\n \n std::vector duckdbNumericalDataTypes = {\n \"BIGINT\",\n \"DECIMAL\",\n \"FLOAT\",\n \"DOUBLE\",\n \"HUGEINT\",\n \"SMALLINT\",\n \"UBIGINT\",\n \"UHUGEINT\",\n \"UINTEGER\",\n \"USMALLINT\",\n \"UTINYINT\"\n };\n```\n\nI get\n\n```\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0 0x000076efd0d97360 in \nduckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n```\n\nalso if the query executed is:\n\n```\nquery2_c= ALTER TABLE populationShort ALTER 'Year' SET DATA TYPE DATE\n```\n\n### Update 01\n\nI modified query2 as follows to avoid pointed out SQL syntax problem, and separate concerns (SQL code vs C `SIGSEGV`):\n\n```\nstd::string query2 = \"ALTER TABLE \";\n query2.append(gettablename);\n query2.append(\" ALTER \\\"\");\n std::string getValIdx0s = std::format(\"{}\", val_idx_0_s);\n query2.append(getValIdx0s);\n //query2.append(\"' SET DATA TYPE USING MAKE_DATE('year',1,1);\");\n query2.append(\"\\\" SET DATA TYPE DATE;\");\n const char* query2_c = query2.c_str();\n std::cout resulting in:\n\n```\nquery2_c= ALTER TABLE populationShort ALTER \"Year\" SET DATA TYPE DATE;\n```\n\nBut still get\n\n```\nSegmentation fault (core dumped)\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0 0x000072894dd97360 in duckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n```\n\n========================================\n\nTop Answer:\n### **Not a solution**, but an attempt to reproduce your problem\n\n```\n#include \n#include \n\nusing namespace std;\nusing namespace duckdb;\n\nvoid run(Connection * con, const string q)\n{\n auto r = con->Query(q); \n if(r->HasError())\n cerr GetError() ToString();\n}\n\nint main(int argc, char ** argv)\n{ \n DuckDB db(\"/tmp/1.duck\");\n Connection con(db);\n\n run(&con, \"create table populationShort (Year text)\");\n run(&con, \"insert into populationShort values ('2025'),('2025-01-01')\");\n run(&con, \"ALTER TABLE populationShort ALTER Year SET DATA TYPE DATE\");\n run(&con, \"select * from populationShort\");\n return 0;\n}\n```\n\nOn a FreeBSD 14.3 system, with clang++ 20.1.4, running:\n\n```\n( DUCK=$HOME/local/duckdb-1.1.3 ; export LD_LIBRARY_PATH=$DUCK/lib:$LD_LIBRARY_PATH ; rm -f /tmp/1.duck ; clang++ -g -O0 -o testduckdb -I$DUCK/include stackoverflow79755866.cpp -L$DUCK/lib -lduckdb && ./testduckdb ; echo $? )\n```\n\noutputs:\n\n```\nCount \nBIGINT \n[ Rows: 0]\n\nCount \nBIGINT \n[ Rows: 1]\n2\n\nConversion Error: date field value out of range: \"2025\", expected format is (YYYY-MM-DD)\nYear \nVARCHAR \n[ Rows: 2]\n2025\n2025-01-01\n\n0\n```\n\n(either with DuckDB 1.3.2 or 1.1.3; I could not got to older releases, whose C++ code have templating errors from clang++ 20 point of view)\n\nNote that **even with the data's incorrect format preventing the `ALTER`, the binary did not segfault** (and of course with good data it runs without hickups).\n\n========================================\n\nCode:\n```text\nquery2_c= ALTER TABLE populationShort ALTER Year SET DATA TYPE DATE;\n```\n\n```cpp\nduckdb::DeprecatedMaterializeResult(duckdb_result*)\n```\n\n```cpp\nduckdb_database CSVDuckDB = NULL;\n        duckdb_connection CSVDuckDBConnection = NULL;\n        duckdb_result CSVDuckDBResult;\n        duckdb_state CSVDuckDBState;\n\n        const char* CSVDuckDBCompletePath = CSVDuckDBCompletePath_s.c_str();\n        if (duckdb_open(CSVDuckDBCompletePath, &CSVDuckDB) == DuckDBError)\n        {\n            fprintf(stderr, \"Failed to open CSVDuckDB\\n\");\n            // Clean-up\n            duckdb_destroy_result(&CSVDuckDBResult);\n            duckdb_disconnect(&CSVDuckDBConnection);\n            duckdb_close(&CSVDuckDB);\n        }\n\n        if (duckdb_connect(CSVDuckDB, &CSVDuckDBConnection) == DuckDBError)\n        {\n            fprintf(stderr, \"Failed to open connection to CSVDuckDB\\n\");\n            // Clean-up\n            duckdb_destroy_result(&CSVDuckDBResult);\n            duckdb_disconnect(&CSVDuckDBConnection);\n            duckdb_close(&CSVDuckDB);\n        }\n\n\n        std::string justname_s_pure = justname_s_splitted[0];\n        std::string gettablename = std::format(\"{}\", justname_s_pure);\n        std::cout << \"gettablename= \" << gettablename << std::endl;\n        std::string query = \"CREATE OR REPLACE TABLE \";\n        query.append(gettablename); \n\n\n\n        query.append(\" AS SELECT * FROM read_csv('\");\n        std::string getfilename = std::format(\"{}\", fileName_s);\n        query.append(getfilename);\n        //query.append(\"', sample_size = -1);\");\n        query.append(\"', strict_mode = false, ignore_errors = true);\");\n        const char* query_c = query.c_str();\n\n        std::cout << \"query_c= \" << query_c << std::endl;\n\n        CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query_c, &CSVDuckDBResult);\n\n\n        if (CSVDuckDBState == DuckDBError)\n        {\n            std::cout << \"duckdb_query produced DuckDBError\" << std::endl;\n            // Clean-up\n            duckdb_destroy_result(&CSVDuckDBResult);\n            duckdb_disconnect(&CSVDuckDBConnection);\n            duckdb_close(&CSVDuckDB);\n        }\n        else\n        {\n            std::cout << \"duckdb_query is ok\" << std::endl;\n            duckdb_result_type CSVDuckDBReturnType = duckdb_result_return_type(CSVDuckDBResult);\n            std::cout << \"CSVDuckDBReturnType= \" << CSVDuckDBReturnType << std::endl;\n        }\n\n        query = \"SELECT column_name, ordinal_position, data_type FROM information_schema.columns;\";\n        query_c = query.c_str();\n\n        std::cout << \"query_c= \" << query_c << std::endl;\n\n        CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query_c, &CSVDuckDBResult);\n        if (CSVDuckDBState == DuckDBError)\n        {\n            std::cout << \"duckdb_query of information_schema.columns produced DuckDBError\" << std::endl;\n            // Clean-up\n            duckdb_destroy_result(&CSVDuckDBResult);\n            duckdb_disconnect(&CSVDuckDBConnection);\n            duckdb_close(&CSVDuckDB);\n        }        idx_t col_count = duckdb_column_count(&CSVDuckDBResult);\n        idx_t row_count = duckdb_row_count(&CSVDuckDBResult);\n\n        for (size_t row_idx = 0; row_idx < row_count; row_idx++)\n        {\n            std::cout << \"row:\" << row_idx << std::endl;\n            char* val_idx_0 = duckdb_value_varchar(&CSVDuckDBResult, 0, row_idx);\n            std::string val_idx_0_s (val_idx_0);\n            printf(\"%s \", val_idx_0);\n            char* val_idx_2 = duckdb_value_varchar(&CSVDuckDBResult, 2, row_idx);\n            printf(\"%s \", val_idx_2);\n            std::string val_idx_2_s (val_idx_2);\n            if (\n                 (Grasp::IsStringIntoVect(date_csv_fields, Grasp::StringToLower(val_idx_0_s)))\n                 &&\n                 (Grasp::IsStringIntoVect(duckdbNumericalDataTypes, val_idx_2_s))\n               )\n            {\n                std::cout << \"IT HAS TO BE CHANGED TO DATE TYPE\" << std::endl;\n                // https://duckdb.org/docs/stable/sql/statements/alter_table.html\n                std::string query2 = \"ALTER TABLE \";\n                query2.append(gettablename);\n                query2.append(\" ALTER '\");\n                std::string getValIdx0s = std::format(\"{}\", val_idx_0_s);\n                query2.append(getValIdx0s);\n                query2.append(\"' SET DATA TYPE USING MAKE_DATE('year',1,1);\");\n                const char* query2_c = query2.c_str();\n                std::cout << \"query2_c= \" << query2_c << std::endl;;\n\n                CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query2_c, &CSVDuckDBResult);\n                if (CSVDuckDBState == DuckDBError)\n                {\n                    std::cout << \"duckdb_query of altering the data type produced DuckDBError\" << std::endl;\n                    // Clean-up\n                    duckdb_destroy_result(&CSVDuckDBResult);\n                    duckdb_disconnect(&CSVDuckDBConnection);\n                    duckdb_close(&CSVDuckDB);\n                }\n\n            }\n            printf(\"\\n\");\n\n\n        }\n\n        // Clean-up\n        duckdb_destroy_result(&CSVDuckDBResult);\n        duckdb_disconnect(&CSVDuckDBConnection);\n        duckdb_close(&CSVDuckDB);\n```\n\n```text\ngettablename= populationShort\nquery_c= CREATE OR REPLACE TABLE populationShort AS SELECT * FROM  \nread_csv('/home/raphy/Downloads/CSVFiles/populationShort.csv',  \nstrict_mode = false, ignore_errors = true);\nduckdb_query is ok\nCSVDuckDBReturnType= 2\nquery_c= SELECT column_name, ordinal_position, data_type FROM \ninformation_schema.columns;\nrow:0\nCountry Name VARCHAR \nrow:1\nCountry Code VARCHAR \nrow:2\nYear BIGINT IT HAS TO BE CHANGED TO DATE TYPE\nquery2_c= ALTER TABLE populationShort ALTER 'Year' SET DATA TYPE \nDATE;\nduckdb_query of altering the data type produced DuckDBError\n\nrow:3\nSegmentation fault (core dumped)\n\n(base) raphy@raohy:/var/crash$ gdb /home/raphy/MyPrj/builddir/\nMyPrj ./core_MyPrj.27982\n\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0  0x00007652aed97360 in    \nduckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n   from /home/raphy/MyPrj/./src/DuckDB/lib/libduckdb.so\n```\n\n```cpp\n// https://duckdb.org/docs/stable/sql/statements/alter_table.html\n                std::string query2 = \"ALTER TABLE \";\n                query2.append(gettablename);\n                query2.append(\" ALTER '\");\n                std::string getValIdx0s = std::format(\"{}\", val_idx_0_s);\n                query2.append(getValIdx0s);\n                query2.append(\"' SET DATA TYPE USING MAKE_DATE('year',1,1);\"); // N.B.: or with the alternative presented in Update 01, see below in the question.\n                const char* query2_c = query2.c_str();\n                std::cout << \"query2_c= \" << query2_c << std::endl;;\n\n                CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query2_c, &CSVDuckDBResult);\n                if (CSVDuckDBState == DuckDBError)\n                {\n                    std::cout << \"duckdb_query of altering the data type produced DuckDBError\" << std::endl;\n                    // Clean-up\n                    duckdb_destroy_result(&CSVDuckDBResult);\n                    duckdb_disconnect(&CSVDuckDBConnection);\n                    duckdb_close(&CSVDuckDB);\n                }\n```\n\n```none\nCountry Name,Country Code,Year,Value\nAruba,ABW,1960,54922\nAruba,ABW,1961,55578\nAruba,ABW,1962,56320\n```\n\n```cpp\nstd::vector<std::string> date_csv_fields = {\n            \"month\",\n            \"year\",\n            \"day\"\n        };\n    \n        std::vector<std::string> duckdbNumericalDataTypes = {\n            \"BIGINT\",\n            \"DECIMAL\",\n            \"FLOAT\",\n            \"DOUBLE\",\n            \"HUGEINT\",\n            \"SMALLINT\",\n            \"UBIGINT\",\n            \"UHUGEINT\",\n            \"UINTEGER\",\n            \"USMALLINT\",\n            \"UTINYINT\"\n        };\n```\n\n```text\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0  0x000076efd0d97360 in     \nduckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n```\n\n```text\nquery2_c= ALTER TABLE populationShort ALTER 'Year' SET DATA TYPE DATE\n```\n\n```cpp\nstd::string query2 = \"ALTER TABLE \";\n                query2.append(gettablename);\n                query2.append(\" ALTER \\\"\");\n                std::string getValIdx0s = std::format(\"{}\", val_idx_0_s);\n                query2.append(getValIdx0s);\n                //query2.append(\"' SET DATA TYPE USING MAKE_DATE('year',1,1);\");\n                query2.append(\"\\\" SET DATA TYPE DATE;\");\n                const char* query2_c = query2.c_str();\n                std::cout << \"query2_c= \" << query2_c << std::endl;;\n```\n\n```text\nquery2_c= ALTER TABLE populationShort ALTER \"Year\" SET DATA TYPE DATE;\n```\n\n```text\nSegmentation fault (core dumped)\nProgram terminated with signal SIGSEGV, Segmentation fault.\n#0  0x000072894dd97360 in duckdb::DeprecatedMaterializeResult(duckdb_result*) ()\n```\n\n```text\nSegmentation fault (core dumped)\n```\n\n```text\ngdb\n```\n\n```text\npopulationShort.csv\n```\n\n```text\ndate_csv_fields\n```\n\n```text\nduckdbNumericalDataTypes\n```\n\n```text\nSIGSEGV\n```\n\n```text\nduckdb_result CSVDuckDBResult;\n    […]\n    CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query_c, &CSVDuckDBResult);\n    […]\n    for (size_t row_idx = 0; row_idx < row_count; row_idx++)\n    {\n        char* val_idx_0 = duckdb_value_varchar(&CSVDuckDBResult, 0, row_idx);\n        […]\n        if ([…])\n        {\n            […]\n            CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query2_c, &CSVDuckDBResult);\n            […]\n        }\n    }\n    duckdb_destroy_result(&CSVDuckDBResult);\n```\n\n```text\nduckdb_result CSVDuckDBResult;\n    […]\n    CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query_c, &CSVDuckDBResult);\n    […]\n    for (size_t row_idx = 0; row_idx < row_count; row_idx++)\n    {\n        char* val_idx_0 = duckdb_value_varchar(&CSVDuckDBResult, 0, row_idx);\n        […]\n        if ([…])\n        {\n            duckdb_result CSVDuckDBResult2; // ← Declare here\n            […]\n            CSVDuckDBState = duckdb_query(CSVDuckDBConnection, query2_c, &CSVDuckDBResult2); // ← Change to 2\n            […]\n            duckdb_destroy_result(&CSVDuckDBResult2); // ← Get out of the \"if (CSVDuckDBState == DuckDBError)\", and change to 2\n        }\n    }\n    duckdb_destroy_result(&CSVDuckDBResult);\n```\n\n```text\nCSVDuckDBResult\n```\n\n```text\nCSVDuckDBResult\n```\n\n```text\nquery_c\n```\n\n```text\nquery2_c\n```\n\n```text\nCSVDuckDBResult\n```\n\n```text\nif([…]).\n```\n\n```text\nduckdb_query(CSVDuckDBConnection, query_c, &CSVDuckDBResult)\n```\n\n```text\nrow_idx = 0\n```\n\n```text\nduckdb_value_varchar(&CSVDuckDBResult, 0, 0)\n```\n\n```text\nif\n```\n\n```text\nrow_idx = 1\n```\n\n```text\nduckdb_value_varchar(&CSVDuckDBResult, 0, 1)\n```\n\n```text\nif\n```\n\n```text\nrow_idx = 2\n```\n\n```text\nduckdb_value_varchar(&CSVDuckDBResult, 0, 2)\n```\n\n```text\nif\n```\n\n```text\nduckdb_query(CSVDuckDBConnection, query2_c, &CSVDuckDBResult)\n```\n\n```text\nCSVDuckDBResult\n```\n\n```text\nrow_idx = 3\n```\n\n```text\nduckdb_value_varchar(&CSVDuckDBResult, 0, 3)\n```\n\n```text\nCSVDuckDBResult\n```\n\n```text\nduckdb_query()ing\n```\n\n```text\nduckdb_destroy_result()\n```\n\n```text\nALTER\n```\n\n```cpp\n#include <iostream>\n#include <duckdb.hpp>\n\nusing namespace std;\nusing namespace duckdb;\n\nvoid run(Connection * con, const string q)\n{\n    auto r = con->Query(q);  \n    if(r->HasError())\n        cerr << r->GetError() << endl;\n    else\n        cout << r->ToString();\n}\n\nint main(int argc, char ** argv)\n{   \n    DuckDB db(\"/tmp/1.duck\");\n    Connection con(db);\n\n    run(&con, \"create table populationShort (Year text)\");\n    run(&con, \"insert into populationShort values ('2025'),('2025-01-01')\");\n    run(&con, \"ALTER TABLE populationShort ALTER Year SET DATA TYPE DATE\");\n    run(&con, \"select * from populationShort\");\n    return 0;\n}\n```\n\n```bash\n( DUCK=$HOME/local/duckdb-1.1.3 ; export LD_LIBRARY_PATH=$DUCK/lib:$LD_LIBRARY_PATH ; rm -f /tmp/1.duck ; clang++ -g -O0 -o testduckdb -I$DUCK/include stackoverflow79755866.cpp -L$DUCK/lib -lduckdb && ./testduckdb ; echo $? )\n```\n\n```text\nCount   \nBIGINT  \n[ Rows: 0]\n\nCount   \nBIGINT  \n[ Rows: 1]\n2\n\nConversion Error: date field value out of range: \"2025\", expected format is (YYYY-MM-DD)\nYear    \nVARCHAR \n[ Rows: 2]\n2025\n2025-01-01\n\n0\n```\n\n```text\nALTER\n```\n\n```text\nALTER 'Year' SET DATA TYPE USING MAKE_DATE('year',1,1);\n```\n\n```text\nduckdb_result_error(&CSVDuckDBResult)\n```\n\n```text\n'Year'\n```\n\n```text\n\" ALTER '\"\n```\n\n```text\n\"' SET DATA TYPE …\"\n```\n\n```text\n\" ALTER \\\"\"\n```\n\n```text\n\"\\\" SET DATA TYPE …\"\n```\n\n```text\n\"Year\"\n```\n\n```text\nALTER TABLE \"Country Name\"\n```\n\n```text\nUSING MAKE_DATE('year',1,1)\n```\n\n```text\n'year'\n```\n\n```text\n\"Year\"\n```\n\n```text\nALTER\n```\n\n```text\n'year'\n```\n\n```text\ngetValIdx0s\n```\n\n```text\nyear\n```\n\n```text\nduckdb_query\n```\n\n========================================\n\nComments:\n- As DuckDB is rapidly evolving, could you post 1. your DuckDB version and distribution (OS, package manager)? My CLI duckdb 1.3.2 compiled on FreeBSD 14 is unable to reproduce it (`create table populationShort (Year text); insert into populationShort values ('2025-02-03'),('2025-01-01'); ALTER TABLE populationShort ALTER Year SET DATA TYPE DATE; select * from populationShort;`). And 2. If the problem is not related to the version but rather to the binding, could you post the full C code calling the query?\n- Note that in may 2024, DeprecatedMaterializeResult got a \"fix crash in deprecated c api code\"; an hypothesis would be that DuckDB, trying to alert you of a conversion error during the `ALTER` (e.g. you're asking it to `ALTER` a column containing `2025`, which is not convertible to date, instead of `2025-01-01`), calls this code and triggers the crash.\n- Hi @GuillaumeOutters ! Thank for you helping. I updated my question above with the complete code I've used. I upgraded the duckdb from 1.2.0 to the latest 1.3.2 and the problem persists.\n- Hi @Guillaume Outters. Thank you for helping. May be the problem lies in the code I've used. Please give a look at it on the question above\n- Hi @Guillaume Outters . I modified query2 with `query2.append(\" ALTER \\\"\");` and with `query2.append(\"\\\" SET DATA TYPE DATE;\");` resulting in `query2_c= ALTER TABLE populationShort ALTER \"Year\" SET DATA TYPE DATE;` . But still get the `SIGSEGV segmentation fault` with `duckdb::DeprecatedMaterializeResult(duckdb_result*)` . I updated my question above with Update 01)\n- @Raphael10 There's still `\"… USING MAKE_DATE('year',1,1);\");` to change to `\"… USING MAKE_DATE(\\\"Year\\\",1,1);\");`, or better, `\"… USING MAKE_DATE(\\\"\").append(getValIdx0s).append(\"\\\",1,1);\");`\n- @Raphael10 Ooops, I didn't see that the `'year'` was commented, so ignored… Anyway, having replaced the `TYPE USING MAKE_DATE(…)` with a simple `TYPE DATE` will still get an SQL cast error, as it cannot cast an integer to a date.\n- Yes!! That was the error: using the same duckdb_state and the same duckdb_result for both the \"external\" and the \"internal\" of the for loop. And the query that works is: `query2.append(\" ALTER \\\"\");` +`query2.append(\"\\\" SET DATA TYPE USING MAKE_DATE(\\\"year\\\",1,1);\");`","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":65,"totalLines":784,"estimatedTokens":5280}}49{"id":"stack-59671329","source":"stackoverflow","questionId":59671329,"title":"How does DuckDB handle Sparse tables?","tags":["duckdb"],"text":"Title: How does DuckDB handle Sparse tables?\nTags: duckdb\nSource: Stack Overflow\n\nQuestion:\nWe are evaluating embedding duckdb in our applications. We deal with a lot of tables where the columns will be around 60-70 % sparse most of the time. Does duckdb fill them with default null values or does it support sparsity internally?\n\n========================================\n\nComments:\n- This response is more than a year old. Any updates on this?\n- We are working on compressed storage which should address this right now. No ETA though.\n- more years have passed, this question pops up a lot. Any updates?\n- We have compression now that efficiently suppresses NULL storage using only a single bit. So this should now be greatly improved.","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":0,"totalLines":14,"estimatedTokens":184}}50{"id":"stack-79175533","source":"stackoverflow","questionId":79175533,"title":"Rolling sum using DuckDB's Python relational API","tags":["python","duckdb"],"text":"Title: Rolling sum using DuckDB's Python relational API\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nSay I have\n\n```\ndata = {'id': [1, 1, 1, 2, 2, 2],\n 'd': [1, 2, 3, 1, 2, 3],\n 'sales': [1, 4, 2, 3, 1, 2]}\n```\n\nI want to compute a rolling sum with window of 2 partitioned by 'id' ordered by 'd'\n\nUsing SQL I can do:\n\n```\nduckdb.sql(\"\"\"\nselect *, sum(sales) over w as rolling_sales\nfrom df\nwindow w as (partition by id order by d rows between 1 preceding and current row)\n\"\"\")\nOut[21]:\n┌───────┬───────┬───────┬───────────────┐\n│ id │ d │ sales │ rolling_sales │\n│ int64 │ int64 │ int64 │ int128 │\n├───────┼───────┼───────┼───────────────┤\n│ 1 │ 1 │ 1 │ 1 │\n│ 1 │ 2 │ 4 │ 5 │\n│ 1 │ 3 │ 2 │ 6 │\n│ 2 │ 1 │ 3 │ 3 │\n│ 2 │ 2 │ 1 │ 4 │\n│ 2 │ 3 │ 2 │ 3 │\n└───────┴───────┴───────┴───────────────┘\n```\n\nThis works great, but how can I do it using the Python Relational API?\n\nI've got as far as\n\n```\nrel = duckdb.sql('select * from df')\nrel.sum(\n 'sales',\n projected_columns='*',\n window_spec='over (partition by id order by d rows between 1 preceding and current row)'\n)\n```\n\nwhich gives\n\n```\n┌───────────────────────────────────────────────────────────────────────────────────────┐\n│ sum(sales) OVER (PARTITION BY id ORDER BY d ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) │\n│ int128 │\n├───────────────────────────────────────────────────────────────────────────────────────┤\n│ 3 │\n│ 4 │\n│ 3 │\n│ 1 │\n│ 5 │\n│ 6 │\n└───────────────────────────────────────────────────────────────────────────────────────┘\n```\n\nThis is close, but it's not quite right - how do I get the name of the last column to be `rolling_sales`?\n\n========================================\n\nCode:\n```py\ndata = {'id': [1, 1, 1, 2, 2, 2],\n 'd': [1, 2, 3, 1, 2, 3],\n 'sales': [1, 4, 2, 3, 1, 2]}\n```\n\n```py\nduckdb.sql(\"\"\"\nselect *, sum(sales) over w as rolling_sales\nfrom df\nwindow w as (partition by id order by d rows between 1 preceding and current row)\n\"\"\")\nOut[21]:\n┌───────┬───────┬───────┬───────────────┐\n│  id   │   d   │ sales │ rolling_sales │\n│ int64 │ int64 │ int64 │    int128     │\n├───────┼───────┼───────┼───────────────┤\n│     1 │     1 │     1 │             1 │\n│     1 │     2 │     4 │             5 │\n│     1 │     3 │     2 │             6 │\n│     2 │     1 │     3 │             3 │\n│     2 │     2 │     1 │             4 │\n│     2 │     3 │     2 │             3 │\n└───────┴───────┴───────┴───────────────┘\n```\n\n```py\nrel = duckdb.sql('select * from df')\nrel.sum(\n    'sales',\n    projected_columns='*',\n    window_spec='over (partition by id order by d rows between 1 preceding and current row)'\n)\n```\n\n```text\n┌───────────────────────────────────────────────────────────────────────────────────────┐\n│ sum(sales) OVER (PARTITION BY id ORDER BY d ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) │\n│                                        int128                                         │\n├───────────────────────────────────────────────────────────────────────────────────────┤\n│                                                                                     3 │\n│                                                                                     4 │\n│                                                                                     3 │\n│                                                                                     1 │\n│                                                                                     5 │\n│                                                                                     6 │\n└───────────────────────────────────────────────────────────────────────────────────────┘\n```\n\n```text\nrolling_sales\n```\n\n```py\nrel.sum(\n    'sales',\n    projected_columns='*',\n    window_spec='over (partition by id order by d rows between 1 preceding and current row) as rolling_sales'\n)\n```\n\n```text\n┌───────┬───────┬───────┬───────────────┐\n│  id   │   d   │ sales │ rolling_sales │\n│ int64 │ int64 │ int64 │    int128     │\n├───────┼───────┼───────┼───────────────┤\n│     1 │     1 │     1 │             1 │\n│     1 │     2 │     4 │             5 │\n│     1 │     3 │     2 │             6 │\n│     2 │     1 │     3 │             3 │\n│     2 │     2 │     1 │             4 │\n│     2 │     3 │     2 │             3 │\n└───────┴───────┴───────┴───────────────┘\n```","metadata":{"transformedAt":"2026-08-18T18:32:26.977Z","totalAnswersIncluded":0,"totalCodeBlocksIncluded":7,"totalLines":145,"estimatedTokens":1067}}51{"id":"stack-76120441","source":"stackoverflow","questionId":76120441,"title":"duckdb unnest keep empty rows","tags":["sql","duckdb"],"text":"Title: duckdb unnest keep empty rows\nTags: sql, duckdb\nSource: Stack Overflow\n\nQuestion:\nWhat is the syntax to unnest the following table `my_table` in duckdb\n\nid\nx\n\n1\n\n2\n[{a:1,b:2},{a:3,b:4}]\n\ninto the following\n\nid\nx\n\n1\n\n2\n{a:1,b:2}\n\n2\n{a:3,b:4}\n\nThe query `select unnest(x) from mytable;` simply removes the empty row. A query like\n`select id, val from mytable left join unnest(x) as val on true;` is not possible in duckdb. I also tried in combination with coalesce for example.\n\n========================================\n\nTop Answer:\nTry this:\n\n```\nselect\n case when x is not null\n then unnest(x)\n else unnest([null])\n end\nfrom mytable;\n```\n\n========================================\n\nCode:\n```text\nmy_table\n```\n\n```text\nselect unnest(x) from mytable;\n```\n\n```text\nselect id, val from mytable left join unnest(x) as val on true;\n```\n\n```text\nselect id, unnest(x)\nfrom mytable\nunion all\nselect id, null\nfrom mytable\nwhere x is null\norder by id\n```\n\n```text\nx\n```\n\n```text\nnull\n```\n\n```text\nunion all\n```\n\n```text\norder by\n```\n\n```text\nselect id, unnest(x) from mytable\n```\n\n```text\nselect\n  case when x is not null\n    then unnest(x)\n    else unnest([null])\n  end\nfrom mytable;\n```\n\n========================================\n\nComments:\n- What's the datatype of column `x`\n- this would be 'struct(struct(\"a varchar, b varchar))[]\n- does not work unfortunately.\n- what's the output? I don't have access to any duckdb platform.\n- Try shell.duckdb.org\n- thanks, came up with the same solution, seems that no \"one-liner\" exists..\n- There is one caveat here: `select unnest([])` returns nothing which is not covered by this approach. Also, this solution can be shortened into `select unnest(coalesce(x, [null])) from mytable`.","metadata":{"transformedAt":"2026-08-18T18:32:26.978Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":10,"totalLines":109,"estimatedTokens":431}}52{"id":"stack-76473134","source":"stackoverflow","questionId":76473134,"title":"How can I use duckdb.read_json_auto in Python without creating a temporary file?","tags":["python","duckdb"],"text":"Title: How can I use duckdb.read_json_auto in Python without creating a temporary file?\nTags: python, duckdb\nSource: Stack Overflow\n\nQuestion:\nI have a simple function that inserts a Python dictionary into DuckDB. How can I insert it into my table without creating a temporary file?\n\n```\ndef save_to_duckdb(data):\n # Connect to the Duckdb database\n conn = duckdb.connect('nodes_log_duck.db')\n # Get the table name from the \"name\" field in the dictionary\n table_name = data.get('name')\n # Create a temp file\n file_name = table_name + str(int(time.time()))\n with open( file_name,\"w\") as file:\n json.dump(data,file)\n # Create the table if it doesn't exist\n conn.execute(f\" CREATE TABLE IF NOT EXISTS {table_name} as SELECT * FROM read_json_auto({file_name});\")\n\n # Insert the dictionary data into the table\n conn.execute(f\"INSERT INTO {table_name} FROM (SELECT * FROM read_json_auto({file_name}))\")\n\n # Commit the changes to the database and close the connection\n conn.commit()\n conn.close()\n```\n\n========================================\n\nTop Answer:\nIf the python object can be converted into pandas dataframe then you can also use DuckDBPyConnection.register() function to convert the python object into table:\n\n```\n# Connect to DuckDB\ncon = duckdb.connect()\n\n# Create a DataFrame from the data\ndf = pd.DataFrame(data)\n\n# Register the DataFrame as a virtual table in DuckDB\ntry:\n con.register(\"vw_df\", df)\n print(\"Table registered successfully.\")\nexcept Exception as e:\n print(f\"Error registering table: {e}\")\n exit(1) # Exit the program if there's an error\n\n# # Query the registered table\nresult = con.execute(\"SELECT * FROM vw_df\").fetchall()\nprint(result)\n\n# Optionally, create a physical table from the virtual table\ntry:\n con.execute(\"CREATE TABLE tbl_df AS SELECT * FROM vw_df\")\n print(\"Physical table created successfully.\")\nexcept Exception as e:\n print(f\"Error creating physical table: {e}\")\n```\n\nSource : https://duckdb.org/docs/api/python/data_ingestion\n\n========================================\n\nCode:\n```text\ndef save_to_duckdb(data):\n    # Connect to the Duckdb database\n    conn = duckdb.connect('nodes_log_duck.db')\n    # Get the table name from the \"name\" field in the dictionary\n    table_name = data.get('name')\n    # Create a temp file\n    file_name = table_name + str(int(time.time()))\n    with open( file_name,\"w\") as file:\n        json.dump(data,file)\n    # Create the table if it doesn't exist\n    conn.execute(f\" CREATE TABLE IF NOT EXISTS {table_name} as SELECT * FROM read_json_auto({file_name});\")\n\n    # Insert the dictionary data into the table\n    conn.execute(f\"INSERT INTO {table_name} FROM (SELECT * FROM read_json_auto({file_name}))\")\n\n    # Commit the changes to the database and close the connection\n    conn.commit()\n    conn.close()\n```\n\n```text\ndef save_to_duckdb(data, db_name):\n    with duckdb.connect(db_name) as conn:\n    \n        # Get the table name from the \"name\" field in the dictionary\n        table_name = data.get('name')\n        if not table_name:\n            return\n\n        # Create a memory filesystem and write the dictionary data to it\n        with fsspec.filesystem('memory').open(f'{table_name}.json', 'w') as file:\n            file.write(json.dumps(data))\n\n        # Register the memory filesystem and create the table\n        conn.register_filesystem(fsspec.filesystem('memory'))\n        conn.execute(f\"CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM read_json_auto('memory://{table_name}.json')\")\n\n        # Insert the data into the table\n        conn.execute(f\"INSERT INTO {table_name} SELECT * FROM read_json_auto('memory://{table_name}.json')\")\n```\n\n```text\ndef save_to_duckdb(data, db_name):\n    # Get the table name from the \"name\" field in the dictionary\n    table_name = data.get('name')\n    if table_name is None:\n        return\n\n    # Create a polars DataFrame from the data dictionary\n    df = pl.DataFrame(data)\n\n    # Connect to the Duckdb database and insert the DataFrame into the database\n    with duckdb.connect(db_name) as con:\n        con.execute(f\"CREATE TABLE IF NOT EXISTS {table_name} AS SELECT * FROM df\")\n        con.execute(f\"INSERT INTO {table_name} SELECT * FROM df\")\n        con.commit()\n```\n\n```text\n**fsspec**\n```\n\n```text\n**Plors**\n```\n\n```text\n# Connect to DuckDB\ncon = duckdb.connect(<db_path>)\n\n# Create a DataFrame from the data\ndf = pd.DataFrame(data)\n\n# Register the DataFrame as a virtual table in DuckDB\ntry:\n    con.register(\"vw_df\", df)\n    print(\"Table registered successfully.\")\nexcept Exception as e:\n    print(f\"Error registering table: {e}\")\n    exit(1)  # Exit the program if there's an error\n\n# # Query the registered table\nresult = con.execute(\"SELECT * FROM vw_df\").fetchall()\nprint(result)\n\n# Optionally, create a physical table from the virtual table\ntry:\n    con.execute(\"CREATE TABLE tbl_df AS SELECT * FROM vw_df\")\n    print(\"Physical table created successfully.\")\nexcept Exception as e:\n    print(f\"Error creating physical table: {e}\")\n```\n\n========================================\n\nComments:\n- Why do you insert the dictionary twice into the table when the table doesn't exist?\n- I know that, I did not fix that before asking my question. (I did not want to create table manually, I wanted to create table with SELECT)","metadata":{"transformedAt":"2026-08-18T18:32:26.978Z","totalAnswersIncluded":1,"totalCodeBlocksIncluded":6,"totalLines":164,"estimatedTokens":1312}}53