CREATE TABLE statement

Overview

The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types, and optional constraints. In DuckDB, this statement follows standard SQL syntax while offering some additional flexibility and features.

Basic Syntax

The simplest form creates a table with defined columns:

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

With Constraints

Tables often need rules to maintain data integrity. Common constraints include primary keys, foreign keys, and value checks:

Copy code
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    department_id INTEGER,
    salary DECIMAL(10,2) CHECK (salary >= 0),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Creating From Query Results

DuckDB supports creating tables from query results using CREATE TABLE AS (CTAS):

Copy code
CREATE TABLE high_salary_employees AS 
SELECT * FROM employees 
WHERE salary > 100000;

Temporary Tables

For session-scoped tables that automatically clean up:

Copy code
CREATE TEMPORARY TABLE temp_results (
    calculation_date DATE,
    result_value INTEGER
);

DuckDB-Specific Features

DuckDB adds several helpful variants to the standard syntax:

• IF NOT EXISTS prevents errors if the table already exists:

Copy code
CREATE TABLE IF NOT EXISTS users (
    id INTEGER,
    username VARCHAR
);

• CREATE OR REPLACE TABLE drops and recreates an existing table:

Copy code
CREATE OR REPLACE TABLE metrics (
    timestamp TIMESTAMP,
    value DOUBLE
);

Best Practices

When creating tables in DuckDB, consider using appropriate data types for optimal performance (like using INTEGER instead of VARCHAR for numeric IDs), and always define constraints that help maintain data integrity. For large-scale data operations, consider using the CREATE TABLE AS syntax with a query that includes any necessary data transformations.