Tim M.

thx

which I believe only copies metadata and presumably a snapshot of it at the time of the COPY

It seems like the best approach is to do this: COPY FROM DATABASE my_ducklake TO iceberg_datalake; as shown here https://ducklake.select/2025/09/17/ducklake-03/

we need it to join the results with other dbs

Trino

yeah, we like our databases and SQL. just wish everyone used more similar syntax when possible :)

Brandon I can definitely do what I want by rewriting the SQL, but since I have thousands of files of SQL that run against Postgres and I would like to use the same SQL against Duck, the challenge was really finding a way to wrap it all up in a function that I could call from both cleanly. (I didn’t). I’ll read through the above and see if there is a way to do that with your ideas (thanks!), but in general, I do think we will switch to json_table for our postgres code because that is supported in Trino, PG, MySQL, and pretty much most dbs it seems now. I personally think it would be great if Duck supported it too, and I’ll put in a Issue as suggested.

after trying lots of different ways to do this elegantly, I didn’t really find one. Also having looked into Trino, PG, and others, it seems like it would be best if Duck supported json_table since that is the standard and seems like a very flexible function that is not far from json_transform. but I’m new to Duck, so maybe there is some reason they didn’t want to do the SQL standard here.

turns out PG does require the final schema, but of course they don’t do a stringified JSON format. It looks like this in PG land:

select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);

but unfortunately Duck json_transform (which is very similar) wants a structure with different formatting like this:

SELECT json_transform(j, '{"family": "TINYINT", "coolness": "DECIMAL(4, 2)"}') FROM example;

it would be great if json_transform took a struct type or whatever it is called in Duck instead of a stringified schema.