Skip to main content

Edit this page

CREATE TABLE

Create a new table.

Syntax​

Column definition​

Table constraints​

Parameters​

ParameterDescription
IF NOT EXISTSDo not return an error if a table with the same name already exists
table_nameThe name of the table to create
column_nameThe name of a column
data_typeThe data type of the column
PRIMARY KEYMark the column as the primary key
NOT NULLThe column cannot contain null values
DEFAULT expressionDefault value for the column
CHECK (expression)Check constraint — validates that the expression is true for every row
REFERENCES table(column)Foreign key constraint
USING EXTERNALCreate a table backed by an external file
file_pathPath to the external file (e.g., Parquet)

Examples​

Basic table​

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT,
    views INTEGER DEFAULT 0
);

Table with foreign keys​

CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    article_id INTEGER REFERENCES articles(id),
    author TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

External table (Parquet)​

CREATE TABLE movies_external (
    title TEXT,
    year INTEGER,
    rating FLOAT
) USING EXTERNAL WITH (
    PATH = '/data/movies.parquet'
);

External table on S3​

CREATE TABLE movies_s3 (
    title TEXT,
    year INTEGER,
    rating FLOAT
) USING EXTERNAL WITH (
    PATH = 's3://my-bucket/data/movies.parquet',
    FORMAT = 'Parquet',
    S3_ACCESS_KEY = 'your-access-key',
    S3_SECRET_KEY = 'your-secret-key',
    S3_REGION = 'us-east-1'
);

External table options​

External tables support the same storage and format options as COPY. Use WITH (HELP) to see all available options:

CREATE TABLE t (id INT) USING EXTERNAL WITH (HELP);
OptionDefaultDescription
PATH(required)Path to the external file (local or s3://)
FORMATTextFile format: Text, Csv, Parquet, Dwrf, Orc
DELIMITER, (CSV) or \t (Text)Column delimiter
HEADERFALSEFirst line is a header row

See COPY — S3 options for S3 authentication and connection parameters.

See also​