Manoj V.

Cloudflare + MD is a great match. Everything scales from 0.

To clarify: Q1 was if this will be the default in another release or always need a session flag? Q2: to ensure that nested type queries in motherduck pg endpoint behave the same as postgres by default for array_agg and object_agg. Including timestamp types and decimals. JSON only has a few types which don’t fully map to sql types and lose precision. So nested json feels like a hack. Not the elegant solution.

Thanks Samantha L., any plans to make pgcompat_nested_types_as json in an upcoming release? I guess deeper ask is adding to the test suite of the golang proxy driver so queries in postgres and similar ones in motherduck return the same column types in responses for the same underlying table data. i.e motherduck quacks like postgres. Love that duckdb was built on postgres sql dialogue so there's a large overlap of queries 'just working'. The last 1% is map_from_entries / list_agg / struct_pack working like array_agg / object_agg in postgres.

It shouldn't need to be turned to json strings.

I guess what I meant is in the postgres binary format for the protocol, the duckdb nested types with lists and structs can be turned into equivalent postgres nested types.

PG protocol is a really big deal. Ha! I think you're missing a big marketing opportunity. If it walks like a postgres duck and quacks like a postgres duck, it's probably a motherduck. One would expect to see a tacky San Francisco or Seattle billboard somewhere.

Timestamps/datetime should be 2000-01-07T00:00:00Z instead of 2000-01-07 00:00:00

but the way timestamps are formatted is wrong though. Should at-least stick to ISO date strings

I see so you're turning the nested types to json in postgres. That sort of works

Wheee! it worked.

WITH
    bars_t AS (
      SELECT
        date_trunc('day', ts_sess) AS time,
        trunc(arg_min(open, ts), 2) AS open,
        trunc(arg_max(close, ts), 2) AS close,
        trunc(min(low), 2) AS low,
        trunc(max(high), 2) AS high,
      FROM ts_minute_bars
      WHERE
        symbol = 'NQ'
      GROUP BY time
      ORDER BY time
      LIMIT 5
    ),
    range_t AS (
      SELECT
        date_trunc('day', min(ts_sess)) AS minTs,
        date_trunc('day', max(ts_sess)) AS maxTs,
      FROM ts_minute_bars
      WHERE symbol = 'NQ'
    )
    SELECT
      (SELECT list(bars_t) FROM bars_t) AS bars,
      (SELECT {'min': minTs, 'max': maxTs} FROM range_t) AS range
  
{
  bars: [
    {
      time: '2000-01-03 00:00:00',
      open: 7493.18,
      close: 7604.68,
      low: 7448.18,
      high: 7662.18
    },
    {
      time: '2000-01-04 00:00:00',
      open: 7602.18,
      close: 7327.18,
      low: 7292.18,
      high: 7609.68
    },
    {
      time: '2000-01-05 00:00:00',
      open: 7287.18,
      close: 7275.18,
      low: 7162.18,
      high: 7385.18
    },
    {
      time: '2000-01-06 00:00:00',
      open: 7232.18,
      close: 7082.68,
      low: 7049.68,
      high: 7320.18
    },
    {
      time: '2000-01-07 00:00:00',
      open: 7037.18,
      close: 7329.18,
      low: 7035.18,
      high: 7341.68
    }
  ],
  range: { min: '2000-01-03 00:00:00', max: '2026-04-10 00:00:00' }
}