Table of Contents:
  1. What you need to know about Redshift Create Table
  2. The Two Most Important Parameters for the CREATE TABLE command
  3. How Integrate.io Helps

Amazon’s official documentation on Redshift’s CREATE TABLE commands has a lot of information in it. Too much, in fact. It’s hard to sort the necessary from the unimportant, and that’s why we wrote this blog post. In this guide, we’re going to keep things simple. First up, we break down how to use the Redshift Create Table command as quickly and succinctly as possible, leaving out all the extra stuff you probably don’t need to know. Then, we examine some of that extra stuff and more or less tear it to pieces. It should be fun! Let’s dive in . . .

What you need to know about Redshift Create Table

There are three main variations on the Redshift Create table command. They are:

CREATE TABLE

Creates a new table from scratch. Allows you to specify a table name, column names, and column data types.

CREATE TABLE table_name (
column_name1 data_type1,
column_name2 data_typ2
)

CREATE TABLE AS

Allows you to create a new table by running the SELECT command against a different table. Data, column names, and data types are copied over to the new table.

CREATE TABLE AS table_name SELECT * from old_table

CREATE TABLE LIKE

Allows you to specify a separate table whose structure you can copy without copying any of the data.

CREATE TABLE table_name LIKE previous_table_name

We’ll dive into the parameters in a minute, but each of these variations can be for either temporary or permanent tables. For the most part, you’ll want to stick with the simple CREATE TABLE command. Here’s a great blog post that dives into the differences between CREATE TABLE AS and CREATE TABLE LIKE further.

Worry-free replication from source to Redshift & Snowflake

The Two Most Important Parameters for the CREATE TABLE command

TEMP or TEMPORARY

Temporary tables are automatically discarded at the end of the session.

CREATE TEMP TABLE table_name

IF NOT EXISTS

Only create the table if it doesn’t exist. A very useful parameter if you want to avoid errors when a table may already exist.

CREATE TABLE IF NOT EXISTS table_name

There is a lot more you can learn by perusing Amazon’s documentation, but ours should handle about 60% of cases. The one thing we haven’t covered that is really important is DISTKEY & SORTKEY, - that's because we have a separate guide on that here.

How Integrate.io Helps

Integrate.io Sync provides continuous, real-time database replication to Amazon Redshift. It offers a reliable, powerful way to simplify your data analytics pipeline in a single interface without manual scripting.