Creating Tables

Once your database is ready, the next step is to create tables to store your data. In DuckDB, tables are created using the CREATE TABLE command.

Basic CREATE TABLE Syntax

Here is the basic syntax for creating a table:

CREATE TABLE table_name (
    column_name data_type,
    other_column data_type
);

Example of creating a students table:

CREATE TABLE students (
    id INTEGER,
    name VARCHAR,
    major VARCHAR,
    intake_year INTEGER
);

The command above creates a table with four columns: id, name, major, and intake_year.

Data Types Supported by DuckDB

DuckDB supports a variety of standard SQL data types. Here are some commonly used ones:

Creating Tables with Column Comments

As in the official DuckDB documentation, you can add comments directly inside the SQL command using --:

CREATE TABLE weather (
    city    VARCHAR,    -- city name
    temp_lo INTEGER,    -- minimum temperature of the day
    temp_hi INTEGER,    -- maximum temperature of the day
    prcp    FLOAT,      -- precipitation
    date DATE        -- recording date
);

Whitespace (spaces, tabs, and newlines) can be used freely in SQL commands. You can align the command or write it all on one line.

Creating a Table from a Query Result (CTAS)

DuckDB supports creating a table directly from the result of a query, also known as CREATE TABLE AS:

CREATE TABLE sales_summary AS
SELECT product, sum(quantity * price) AS revenue
FROM read_csv('sales.csv')
GROUP BY product;

The sales_summary table is created automatically with the appropriate columns and data types from the query result.

Dropping Tables

If a table is no longer needed, or you want to recreate it with a different structure, use DROP TABLE:

DROP TABLE students;

To avoid errors if the table doesn't exist, use the IF EXISTS clause:

DROP TABLE IF EXISTS students;

Summary

In this chapter, we learned how to create tables with CREATE TABLE, choose the right data types, add column comments, and create tables from query results using CREATE TABLE AS. We also learned how to drop tables with DROP TABLE.

In the next chapter, we'll learn how to import data from CSV and Parquet files into DuckDB tables.