How to Create a Table in Redshift

Here's an example of creating a users table in Redshift:

CREATE TABLE users (
  id INTEGER primary key, -- Auto incrementing IDs
  name character varying, -- String column without specifying a length
  created_at timestamp without time zone -- Always store time in UTC
);

This is also a chance to specify not null constraints and default values in Redshift:

create table users (
  id BIGINT primary key,
  name character varying not null,
  active boolean default true
);

Redshift supports the following data types:

• SMALLINT (INT2)
• INTEGER (INT, INT4)
• BIGINT (INT8)
• DECIMAL (NUMERIC)
• REAL (FLOAT4)
• DOUBLE PRECISION (FLOAT8)
• BOOLEAN (BOOL)
• CHAR (CHARACTER)
• VARCHAR (CHARACTER VARYING)
• DATE
• TIMESTAMP
• TIMESTAMPTZ

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

-- Create a temporary table called `scratch_users` with just an `id` column
create temporary table scratch_users (id integer);

-- Or create a temporary table based on the output of a select
create temp table active_users
as
select * from users where active is true;

This concludes the basics of creating tables in Redshift. In How to Use DISTKEY, SORTKEY and Define Column Compression Encoding in Redshift we will cover more advanced, Redshift-specific table creation options.