How to CREATE TABLE in Amazon Redshift

Why is CREATE TABLE essential in Redshift?

CREATE TABLE materializes a dataset inside your Redshift cluster, letting you control column types, compression, distribution, and sort order for fast analytics.

What is the basic CREATE TABLE syntax?

The command starts with CREATE TABLE, an optional IF NOT EXISTS clause, column definitions, and table-level options such as DISTKEY and SORTKEY.

How do I define columns correctly?

Specify column_name followed by Redshift data types (INTEGER, VARCHAR(n), BOOLEAN, etc.). Add DEFAULT, IDENTITY, or ENCODE options to optimize storage and loading.

When should I set DISTKEY and SORTKEY?

Use DISTKEY on columns heavily joined across large tables (e.g., customer_id). Choose SORTKEY columns frequently filtered or ordered (e.g., order_date) to speed up scans.

Can I copy PostgreSQL DDL directly?

Mostly yes, but adjust Redshift-specific features (DISTKEY, SORTKEY, ENCODE). Also replace serial with IDENTITY.

Example: Creating an Orders table

CREATE TABLE IF NOT EXISTS public.orders (
id INT IDENTITY(1,1),
customer_id INT NOT NULL,
order_date DATE DEFAULT CURRENT_DATE,
total_amount NUMERIC(10,2) ENCODE az64,
DISTKEY(customer_id),
SORTKEY(order_date)
);


Best practices for CREATE TABLE

Choose compression via ANALYZE COMPRESSION on sample data. Keep SORTKEYs under four columns. Use VARCHAR lengths just large enough for data.

What are common mistakes?

Omitting distribution style, creating wide VARCHARs, forgetting BACKUP NO for transient tables, and mixing DISTKEY with EVEN distribution are typical issues.