DuckDB 'PIVOT ON' SQL statement

stschiff · February 27, 2025, 8:23am

Hi. I tried to use an SQL Pivot-statement (PIVOT ... ON ... USING) within an SQL Cell in a Notebook. I get Error: Parser Error: syntax error at or near "PIVOT", and it looks like perhaps that Statement is not supported by the DuckDB-Wasm that is included in Notebooks?

I checked the stdlib, which says that DuckDB-Wasm v 1.24 is included in Observable. I then checked with the DuckDB-Wasm documentation and the Browser demo-Prompt, which runs on v1.2.0 does in fact support PIVOT (I just hacked in the PIVOT-example from here and it works).

What’s a bit strange is that when I run SELECT version() AS version; inside an SQL cell in a notebook, I get v0.0.1-dev0, whereas in the demo-Prompt I get v1.2.0, so perhaps indeed the Notebook-DuckDB-Wasm-Version is somewhat outdated?

Any pointers?

mootari · February 27, 2025, 1:38pm

We’re unfortunately still in the process of resolving an issue where range requests are incorrectly served from compressed files, which causes errors in Safari when file attachments are used with a newer version of DuckDB.

The reported version that you see is due to PRAGMA version reports a wrong version in NPM version · Issue #1234 · duckdb/duckdb-wasm · GitHub

stschiff · February 28, 2025, 11:04am

Ah OK. And what is the current version of DuckDB-Wasm included with Notebooks then?

mootari · February 28, 2025, 12:08pm

It’s 1.24.0:

github.com/observablehq/stdlib

src/dependencies.js

v5.8.8


      
          export const vegalite = dependency("vega-lite", "5.6.0", "build/vega-lite.min.js");
          export const vegaliteApi = dependency("vega-lite-api", "5.0.0", "build/vega-lite-api.min.js");
          export const arrow4 = dependency("apache-arrow", "4.0.1", "Arrow.es2015.min.js");
          export const arrow9 = dependency("apache-arrow", "9.0.0", "+esm");
          export const arrow11 = dependency("apache-arrow", "11.0.0", "+esm");
          export const arquero = dependency("arquero", "4.8.8", "dist/arquero.min.js");
          export const topojson = dependency("topojson-client", "3.1.0", "dist/topojson-client.min.js");
          export const exceljs = dependency("exceljs", "4.3.0", "dist/exceljs.min.js");
          export const mermaid = dependency("mermaid", "9.2.2", "dist/mermaid.min.js");
          export const leaflet = dependency("leaflet", "1.9.3", "dist/leaflet.js");
          export const duckdb = dependency("@duckdb/duckdb-wasm", "1.24.0", "+esm");

stschiff · March 1, 2025, 1:01pm

OK, so then it is indeed 1.24, so I don’t get why the PIVOT command then doesn’t work… I tried this in Chrome, so I don’t see a link to the Safari error that you quote. Thanks.

mootari · March 1, 2025, 6:22pm

Because duckdb-wasm versions don’t follow DuckDB. Version 1.24.0 bundles DuckDB version 0.7.1:

You can follow this issue for updates:

github.com/observablehq/feedback

Update duckdb-wasm to latest

opened 01:37AM - 09 Aug 23 UTC

jzavala-gonzalez

Bug Data Access

**Is your feature request related to a problem? Please describe.** I'm writing …a notebook that uses the native Observable DuckDB client. One query attempts using DuckDB's UNPIVOT statement introduced in DuckDB 0.8.0 (released around mid-May 2023, see [announcement](https://duckdb.org/2023/05/17/announcing-duckdb-080.html) and [docs](https://duckdb.org/docs/sql/statements/unpivot.html#unpivot-into-multiple-value-columns)). The query fails with a syntax error (see screenshot) which I'm guessing is due to an outdated duckdb-wasm version in Observable. Checking the stdlib [dependencies file](https://github.com/observablehq/stdlib/blob/main/src/dependencies.js) shows Observable is using version 1.24.0 of duckdb-wasm, which was published in March [according to npm](https://www.npmjs.com/package/@duckdb/duckdb-wasm?activeTab=versions). That months-long difference between 1.24.0 and 1.27.0 could explain why UNPIVOT isn't working within yet Observable. **Describe the solution you'd like** Update to the latest version of duckdb-wasm, which at time of writing is 1.27.0 and was published mid-June. **Describe alternatives you've considered** If updating isn't possible, one could update the intro notebook ["Hello, DuckDB"](https://observablehq.com/@observablehq/duckdb) to mention what version of duckdb Observable is currently running so users can look at the correct documentation. **Additional context** ![unpivot syntax error](https://github.com/observablehq/feedback/assets/7855609/aaa840e4-e154-4d7f-9f2c-d8ff9f5a2c1d) ![stdlib dependencies](https://github.com/observablehq/feedback/assets/7855609/af204603-4379-452f-ad54-8d230b859aa8) ![npm duckdb-wasm versions](https://github.com/observablehq/feedback/assets/7855609/a830a811-450c-4b53-8a1d-95dfe3031080)

stschiff · March 3, 2025, 10:31am

Aahh, OK, got it. Sorry, and thanks for pointing me to the GitHub issue on the matter. Very helpful!

mootari · March 3, 2025, 3:32pm

@stschiff Would you mind giving this a try?

Edit: I had messed up the original description - it’s duckdb-wasm 1.29.0 and duckdb 1.1.1.

stschiff · March 5, 2025, 4:24pm

Great. Your patch worked for me! I tested on Chrome and Safari, and it seemed to work on both browsers!

mootari · March 6, 2025, 10:05am

Thanks for checking! I think the problem in Safari only shows itself with file attachments that get compressed at rest (which isn’t the case for every MIME type and requires a certain effective reduction in size).

stschiff · October 7, 2025, 9:08pm

Just a quick update here: I noticed that with your latest DuckCBClient, when I try to run: db = DuckDBClient.of({query_data: FileAttachment(“my_file.tsv”)}) I get en error db = Error: invalid source: [object Object]. The same code works with the native (and old-version) DuckDBClient.

And this happens on both Chrome and Safari, so not sure whether it’s related to the issue you mentioned. I can try to make a minimal example, but I’m traveling the next days, so might take me a bit.

mootari · October 8, 2025, 9:34am

@stschiff There was an instanceof check against FileAttachment that I’ve now replaced with a test of the constructor name. Can you give it another try?

stschiff · October 21, 2025, 8:02am

Hmm, with json data it doesn’t seem to work. See here: https://observablehq.com/d/e524c4c8116e3af9

mootari · October 21, 2025, 2:27pm

@stschiff I can’t access that.

stschiff · October 21, 2025, 3:36pm

Sorry, now you should be able to.

mootari · October 25, 2025, 9:19am

You need to update your import. Imports from other workspaces are locked by default.

stschiff · October 27, 2025, 7:04am

Aaaah, thank you. OK. Sorry, this was the first time I came across the import-locking feature. Very cool. And thanks for the continuous help! It works now.

Topic		Replies	Views
Unpivot Operations with DuckDBClient Community Help	6	79	September 4, 2024
DuckDB isn't working Community Help	2	246	December 13, 2022
We're beta testing database clients! Announcements	8	1404	July 13, 2019
Peirce Quincunxial notebook broken?	5	799	July 11, 2018
DuckDB is not returning rows for Parquet files Community Help	15	1269	July 31, 2024

DuckDB 'PIVOT ON' SQL statement

Related topics