DuckDB Statements

2024-12-08 — SQL, MySQL, Statement — 2 min read

struct and unnest

1select UNNEST([1,2,3,4,5]) as a;
2select UNNEST([({'i': 3, 's': 'string'}), ({'i': 3, 's': 'string'})]) as a;
3SELECT unnest([1, 2, 3]) as a, unnest([10, 11]) as b;
4SELECT unnest(l) + 10 FROM (VALUES ([1, 2, 3]), ([4, 5])) tbl(l);

recursive unnest

1SELECT unnest([[1, 2, 3], [4, 5]], recursive := true);

RECURSIVE

1create table test as 
2WITH RECURSIVE cte (n) AS (
3    SELECT 1
4    UNION ALL
5    SELECT n + 1
6    FROM cte
7    WHERE n < 5
8)
9SELECT
10    *
11FROM cte;

split

1SELECT unnest(SPLIT('2024-01-23,2024-01-22,2024-01-21,2024-01-20', ',')) AS split_result;

write csv

1COPY test TO '/tmp/de-test.csv' WITH (FORMAT CSV, HEADER);

read csv

1SELECT *
2FROM read_csv(
3    '/tmp/de-test.csv',
4    delim = ',',
5    header = true
6  );

read text

1SELECT regexp_split_to_table(content, '\n') as line FROM read_text('/tmp/de-test.csv');

xlsx

1INSTALL spatial;
2LOAD spatial;

write xlsx

1COPY test TO '/tmp/de-test.xlsx' WITH (FORMAT GDAL, DRIVER 'xlsx');

read xlsx

1SELECT * FROM st_read('/tmp/de-test.xlsx');

create table

1CREATE TABLE employees (
2    id INT PRIMARY KEY NOT NULL,
3    name VARCHAR(100) NOT NULL,
4    manager_id INT NULL
5);

insert into

1INSERT INTO employees  (id, name, manager_id)
2VALUES
3(333, 'Yasmina', NULL),
4(198, 'John', 333),
5(692, 'Tarek', 333),
6(29, 'Pedro', 198),
7(4610, 'Sarah', 29),
8(72, 'Pierre', 29),
9(123, 'Adil', 692);

recursive query

1WITH RECURSIVE employee_paths (id, name, path) AS (
2    SELECT
3        id,
4        name,
5        CAST(id AS CHAR(200))
6    FROM
7        employees
8    WHERE
9        manager_id IS NULL
10    UNION ALL
11    SELECT
12        e.id,
13        e.name,
14        CONCAT(ep.path, ',', e.id)
15    FROM
16        employee_paths AS ep
17    JOIN employees AS e
18      ON ep.id = e.manager_id
19)
20SELECT
21    *
22FROM
23    employee_paths
24ORDER BY path;

regexp_matche

1select regexp_matches(unnest(['2024-01-213','2024-0122','2024-01-21','202401-20']), '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
2
3select regexp_matches(day, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$') from (
4  SELECT unnest(SPLIT('2024-01-213,2024-0122,2024-01-21,202401-20', ',')) AS day
5)

date/time

1select TIMESTAMP '1992-03-02 20:32:45';
2SELECT strftime(DATE '1992-03-02', '%d/%m/%Y');
3SELECT strftime(DATE '1992-03-02', '%Y-%m-%d %H:%M:%S');
4SELECT strptime('02-03-1992', '%d-%m-%Y');
5SELECT strftime(TIMESTAMP '1992-03-02 20:32:45', '%A, %-d %B %Y - %I:%M:%S %p');

information_schema

1select * from information_schema.tables;
2select * from information_schema.columns;
3SELECT * FROM duckdb_views;

utility functions

1SELECT random();
2SELECT floor(random() * 10) AS random_integer;
3SELECT uuid();