DuckDB 'PIVOT ON' SQL statement

Notebooks
1

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?

2

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

3

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

4

It’s 1.24.0:

5

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.

6

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:

1 Like
7

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

8

@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.

9

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

10

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).