Announcing Data Outpost - The conference for data people in a world being rebuilt by AINov 4-5 | San Francisco

START FREE
← Back to Glossary

CREATE TABLE statement

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,…

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.

Related terms

table →

A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.

CREATE TABLE AS SELECT (CTAS) →

CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.

ALTER TABLE statement →

The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.

INSERT statement →

The INSERT statement is a fundamental SQL command used to add new rows of data into a table.

DELETE statement →

The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.

UPDATE statement →

The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.

FAQS

It defines a new table in a database, specifying its columns, their data types, and optional constraints. DuckDB follows standard SQL syntax with some extra flexibility.

Declare them inline—PRIMARY KEY, NOT NULL, CHECK (...), and FOREIGN KEY (...) REFERENCES ...—to enforce data integrity as rows are inserted.

Yes, with CREATE TABLE AS (CTAS): CREATE TABLE new_tbl AS SELECT ... FROM ... creates and populates a table from query results in one statement.

READ NEXT