table

Definition

A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet. Each column has a defined data type (like text, numbers, or dates) and a name, while each row contains the actual data values. Tables are the primary way to store and organize data in databases like DuckDB.

Structure

In DuckDB, you create a table using the CREATE TABLE statement, which defines the table's schema (structure). The schema includes column names, their data types, and any constraints like primary keys or not-null requirements. For example:

Copy code
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    hire_date DATE,
    salary DECIMAL(10,2)
);

Temporary vs Persistent Tables

DuckDB supports both temporary tables that exist only for your current session and persistent tables that are saved to disk. Temporary tables are created using CREATE TEMPORARY TABLE and are automatically dropped when your connection closes.

Virtual Tables and Views

DuckDB also supports virtual tables through its view system, which appear like regular tables but are actually stored queries. While not physical tables, they behave similarly in queries:

Copy code
CREATE VIEW employee_salaries AS 
SELECT name, salary 
FROM employees 
WHERE salary > 50000;

Table Functions

DuckDB extends the traditional table concept with table functions that can generate tables dynamically. These include functions like range() and generate_series() which create tables on the fly:

Copy code
SELECT * FROM range(1, 5);

This feature distinguishes DuckDB from traditional databases by making table generation more flexible and dynamic.