---
title: "CREATE TABLE statement"
description: "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,…"
canonical: "https://motherduck.com/glossary/create-table-statement/"
related:
  - title: "CREATE TABLE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-table/"
  - title: "TEMPORARY TABLES | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/temporary-tables/"
  - title: "DuckDB statements | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/"
---

# 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.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Basic Syntax
The simplest form creates a table with defined columns:

```sql
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](https://motherduck.com/glossary/foreign-key/), and value checks:

```sql
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):

```sql
CREATE TABLE high_salary_employees AS 
SELECT * FROM employees 
WHERE salary > 100000;
```

## Temporary Tables
For session-scoped tables that automatically clean up:

```sql
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:
```sql
CREATE TABLE IF NOT EXISTS users (
    id INTEGER,
    username VARCHAR
);
```

- `CREATE OR REPLACE TABLE` drops and recreates an existing table:
```sql
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.