DuckDB 1.4 LTS

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


"version"()


v1.4.5


create table customer (id int primary key, name varchar not null);
create table product (id int primary key, name varchar not null, price decimal(6,2) not null);
create table orders (id int primary key, customer_id int not null references customer, ordered_on date not null);
create table order_line (order_id int references orders, product_id int references product, qty int not null, primary key (order_id, product_id));
insert into customer values (1,'ann'), (2,'bob'), (3,'cat');
insert into product values (1,'widget',2.50), (2,'gadget',7.00), (3,'gizmo',12.00);
insert into orders select n, 1+n%3, date '2026-01-01' + n::int*7 from range(52) t(n);
insert into order_line select o.id, p.id, 1+(o.id*p.id)%5 from orders o cross join product p where (o.id+p.id)%2=0 or p.id=1;
-- new in DuckDB 1.4: MERGE INTO
merge into customer c
using (values (3, 'cath'), (4, 'dan')) s(id, name) on c.id = s.id
when matched then update set name = s.name
when not matched then insert values (s.id, s.name);
from customer order by id;


id
name


1
ann

2
bob

3
cath

4
dan


# python demo
import duckdb
con = duckdb.connect('/data/fiddle.duckdb')
sql = "from customer c join orders o on o.customer_id = c.id join order_line l on l.order_id = o.id join product p on p.id = l.product_id select c.name, sum(l.qty * p.price) group by all order by 2 desc"
for name, revenue in con.sql(sql).fetchall():
    print(f"{name:>4} {revenue:8.2f}  {chr(9608) * int(revenue // 40)}")
 ann   648.00  ████████████████
 bob   639.00  ███████████████
cath   575.50  ██████████████

// node demo
const { DuckDBInstance } = require('@duckdb/node-api');
(async () => {
  const db = await DuckDBInstance.create('/data/fiddle.duckdb');
  const con = await db.connect();
  const r = await con.runAndReadAll("from product p join order_line l on l.product_id = p.id select p.name, sum(l.qty)::int as units group by all order by units desc");
  console.table(r.getRowObjects());
})();
┌─────────┬──────────┬───────┐
│ (index) │ name     │ units │
├─────────┼──────────┼───────┤
│ 0       │ 'widget' │ 153   │
│ 1       │ 'gizmo'  │ 79    │
│ 2       │ 'gadget' │ 76    │
└─────────┴──────────┴───────┘

-- echarts demo
with
  mon as (select m::date as m from range(date '2026-01-01', date '2027-01-01', interval 1 month) t(m))
, rev as
    ( select p.name, mon.m, coalesce(sum(l.qty*p.price),0) r
      from
        product p cross join
        mon left join
        (orders o join order_line l on l.order_id = o.id) on date_trunc('month', o.ordered_on) = mon.m and l.product_id = p.id
      group by all )
select
  to_json
  ( { 'tooltip': {'trigger': 'axis'}
    , 'legend': {'show': true}
    , 'xAxis': {'type': 'category', 'data': (select list(strftime(m, '%b') order by m) from mon)}
    , 'yAxis': {'type': 'value'}
    , 'series': (select list({'name': name, 'type': 'bar', 'stack': 'revenue', 'data': vals} order by name) from (select name, list(r order by m) as vals from rev group by all))
    }
  ) as option;


option


{"tooltip":{"trigger":"axis"},"legend":{"show":true},"xAxis":{"type":"category","data":["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]},"yAxis":{"type":"value"},"series":[{"name":"gadget","type":"bar","stack":"revenue","data":[70.00,35.00,42.00,49.00,21.00,63.00,42.00,63.00,35.00,42.00,49.00,21.00]},{"name":"gizmo","type":"bar","stack":"revenue","data":[108.00,36.00,84.00,96.00,84.00,72.00,60.00,108.00,36.00,144.00,36.00,84.00]},{"name":"widget","type":"bar","stack":"revenue","data":[37.50,25.00,27.50,37.50,30.00,32.50,37.50,35.00,25.00,37.50,27.50,30.00]}]}


-- mermaid demo
select
  concat_ws
  ( chr(10)
  , 'erDiagram'
  , ( select string_agg('  ' || t.table_name || ' {' || chr(10) || x.cols || chr(10) || '  }', chr(10) order by t.table_name)
      from
        duckdb_tables() t join
        ( select
            c.table_name
          , string_agg(concat_ws(' ', '   ', lower(c.data_type).regexp_replace('\(.*', ''), c.column_name, nullif(concat_ws(', ', case when k.pk then 'PK' end, case when k.fk then 'FK' end), '')), chr(10) order by c.column_index) cols
          from
            duckdb_columns() c cross join lateral
            ( select
                bool_or(constraint_type = 'PRIMARY KEY') pk
              , bool_or(constraint_type = 'FOREIGN KEY') fk
              from duckdb_constraints() d
              where d.table_name = c.table_name and list_contains(d.constraint_column_names, c.column_name)
            ) k
          where c.schema_name = 'main'
          group by c.table_name
        ) x on x.table_name = t.table_name
      where t.schema_name = 'main' )
  , ( select string_agg('  ' || table_name || ' }o--|| ' || referenced_table || ' : "' || array_to_string(constraint_column_names, ', ') || '"', chr(10) order by table_name, referenced_table)
      from duckdb_constraints()
      where constraint_type = 'FOREIGN KEY' and schema_name = 'main' )
  ) as diagram;


diagram


erDiagram
  customer {
    integer id PK
    varchar name
  }
  order_line {
    integer order_id PK, FK
    integer product_id PK, FK
    integer qty
  }
  orders {
    integer id PK
    integer customer_id FK
    date ordered_on
  }
  product {
    integer id PK
    varchar name
    decimal price
  }
  order_line }o--|| orders : "order_id"
  order_line }o--|| product : "product_id"
  orders }o--|| customer : "customer_id"