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