Smol bug for the postgres adapter to Motherduck.

Tseegi (.
·
·
Hello Noj! Thanks for the report. We'll look into it, and get back to you with updates!
Tseegi (.
·
·
What client are you on? Python?
Manoj V.
·
·
Using the pg endpoint from Cloudflare workers
Tseegi (.
·
·
Thanks for confirming! Since this is going through the Cloudflare/thin-client path rather than a native DuckDB client, DuckDB-specific nested types like STRUCT don’t come through as native objects. They need to be converted into a portable type like JSON in the query if the client expects nested array/object results.
Manoj V.
·
·
More context: I am hitting the pg.us-east-1-aws.motherduck.com endpoint. The user agent in query_history shows up as a golang client. So I assume there is a golang client in the middle that is serving pg.us-east-1-aws.motherduck.com endpoint and interfacing with motherduck servers. I belive there's a bug in that golang proxy server. Because both postgres and duckdb have object/array types. The golang layer that is translating duckdb-speak to postgres-speak has a bug.
Tseegi (.
·
·
We just rolled out a release on our end that enables this! Could you try again with SET pgcompat_nested_types_as = 'json' in your session?
Manoj V.
·
·
Wow! Thats fast. I’ll test in a bit after dinner
1

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' }
}

Manoj V.
·
·
I see so you're turning the nested types to json in postgres. That sort of works
Manoj V.
·
·
but the way timestamps are formatted is wrong though. Should at-least stick to ISO date strings
Manoj V.
·
·
Timestamps/datetime should be 2000-01-07T00:00:00Z instead of 2000-01-07 00:00:00
Samantha L.
·
·
Hey Manoj, glad this is working now! For the timestamp/date time, you should be able to cast this to the format you need. Currently filing a ticket with our team to see if we can have ISO date format.
Manoj V.
·
·
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.
Manoj V.
·
·
It shouldn't need to be turned to json strings.
Samantha L.
·
·
Hey Manoj, This behavior is inherited from the way that DuckDB handles timestamp::JSON cast, so submitted a feature request on this for you. Also sharing some DuckDB functions you can use for formatting the timestamp.
👍1

Smol bug for the postgres adapter to Motherduck.

19 comments

Smol bug for the postgres adapter to Motherduck.

19 comments