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.