Built-in Functions

Overview of DuckDB’s built-in functions including aggregates, scalar, and table functions

DuckDB provides a comprehensive set of built-in functions for data manipulation, aggregation, and analysis. Functions are organized into categories based on their purpose and return type.

Function Categories

DuckDB functions fall into three main categories:

• Aggregate Functions: Compute a single result from a set of input values
• Scalar Functions: Return a single value for each input row
• Table Functions: Return a table (set of rows) as output

Aggregate Functions

Aggregate functions operate on a set of values and return a single result.

Common Aggregates

COUNT

-- Count all rows
SELECT COUNT(*) FROM test;

-- Count non-NULL values
SELECT COUNT(i) FROM integers;

-- Count distinct values
SELECT COUNT(DISTINCT depname) FROM empsalary;

Statistical Aggregates

SELECT 
  STDDEV(salary) AS std_dev,
  VARIANCE(salary) AS variance,
  MEDIAN(salary) AS median,
  MODE(salary) AS mode
FROM empsalary;

String Aggregates

-- Concatenate strings
SELECT STRING_AGG(name, ', ') AS all_names
FROM employees;

-- With ordering
SELECT STRING_AGG(name, ', ' ORDER BY name) AS sorted_names
FROM employees;

Boolean Aggregates

SELECT 
  BOOL_AND(salary > 3000) AS all_above_3000,
  BOOL_OR(salary > 6000) AS any_above_6000
FROM empsalary;

Advanced Aggregates

FIRST/LAST

SELECT 
  FIRST(empno ORDER BY salary) AS lowest_paid_empno,
  LAST(empno ORDER BY salary) AS highest_paid_empno
FROM empsalary;

Aggregate functions ignore NULL values except for COUNT(*).

Scalar Functions

Scalar functions operate on individual values and return one result per input.

String Functions

Concatenation

SELECT 
  s || ' ' || s AS concat,
  CONCAT(s, ' ', '🦆') AS concat_func
FROM strings;

Numeric Functions

Basic Math

SELECT 
  ABS(-42) AS absolute,
  ROUND(3.14159, 2) AS rounded,
  CEIL(3.14) AS ceiling,
  FLOOR(3.14) AS floor;

Date and Time Functions

Current Date/Time

SELECT 
  CURRENT_DATE AS today,
  CURRENT_TIME AS now_time,
  CURRENT_TIMESTAMP AS now;

Type Conversion Functions

SELECT 
  CAST(salary AS VARCHAR) AS salary_string,
  TRY_CAST('123' AS INTEGER) AS parsed_int,
  salary::DECIMAL(10,2) AS decimal_salary
FROM empsalary;

Conditional Functions

COALESCE

-- Return first non-NULL value
SELECT 
  COALESCE(i, 0) AS value_or_zero
FROM integers;

Table Functions

Table functions return a table (set of rows) and are used in the FROM clause.

Range Function

Generate sequences of numbers:

Simple Range

SELECT * FROM range(5);
-- Returns: 0, 1, 2, 3, 4

Generate Series

Generate series with dates or timestamps:

-- Date series
SELECT * FROM generate_series(
  DATE '2024-01-01',
  DATE '2024-12-31',
  INTERVAL '1 month'
);

-- Timestamp series
SELECT * FROM generate_series(
  TIMESTAMP '2024-01-01 00:00:00',
  TIMESTAMP '2024-01-01 23:59:59',
  INTERVAL '1 hour'
);

Read Functions

read_csv

SELECT * FROM read_csv('data.csv');

SELECT * FROM read_csv(
  'data.csv',
  delim = '|',
  header = TRUE
);

Unnest Function

Expand arrays into rows:

SELECT UNNEST([1, 2, 3, 4, 5]) AS value;

SELECT 
  id,
  UNNEST(tags) AS tag
FROM articles;

Array and List Functions

SELECT 
  LIST_VALUE(1, 2, 3, 4, 5) AS list,
  [1, 2, 3] AS array_literal,
  LIST_CONTAINS([1, 2, 3], 2) AS contains,
  LIST_LENGTH([1, 2, 3]) AS length,
  LIST_SORT([3, 1, 2]) AS sorted;

Struct Functions

SELECT 
  {'name': 'Alice', 'age': 30} AS person,
  STRUCT_PACK(name := 'Alice', age := 30) AS person_struct,
  ROW('Alice', 30) AS person_row;

Pattern Matching Functions

LIKE

SELECT * FROM users 
WHERE name LIKE 'A%';

SELECT * FROM users 
WHERE email ILIKE '[e-mail]';

Utility Functions

-- Generate UUIDs
SELECT UUID() AS unique_id;

-- Hash functions
SELECT 
  MD5(name) AS md5_hash,
  SHA256(name) AS sha256_hash
FROM users;

-- Type checking
SELECT 
  TYPEOF(42) AS int_type,
  TYPEOF('hello') AS string_type;

Use DESCRIBE SELECT ... to see the schema and types of a query result, including function return types.

Function Examples

Combining Multiple Functions

WITH processed_data AS (
  SELECT 
    empno,
    UPPER(TRIM(depname)) AS clean_dept,
    ROUND(salary * 1.1, 2) AS raised_salary,
    DATE_TRUNC('year', enroll_date) AS enroll_year,
    COALESCE(bonus, 0) AS bonus_amount
  FROM empsalary
)
SELECT 
  clean_dept,
  COUNT(*) AS emp_count,
  AVG(raised_salary) AS avg_new_salary,
  SUM(bonus_amount) AS total_bonuses
FROM processed_data
GROUP BY clean_dept, enroll_year
ORDER BY clean_dept;

Working with NULLs

SELECT 
  empno,
  -- Replace NULL with default
  COALESCE(bonus, 0) AS bonus,
  -- NULL if condition met
  NULLIF(salary, 0) AS safe_salary,
  -- Check for NULL
  CASE WHEN bonus IS NULL THEN 'No bonus' ELSE 'Has bonus' END AS bonus_status
FROM empsalary;