Redshift Create Table | How to Create a Redshift Table

in Redshift
 • Updated on 

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.

# 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 FlyData Helps

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

With a free 14-day trial, you can get your data synced in just minutes. For questions about FlyData and how we can help accelerate your use-case and journey on Amazon Redshift, connect with us at support@flydata.com.

Worry-free replication from source to Redshift & Snowflake
Unlimited sync during trial
No credit-card required
World class support
Try FlyData for free
Amazon Partner Logo Certified AWS
Redshift partner
Get started. Try FlyData.
Quick setup. No credit card required. Unlimited sync during trial.
Fast and secure Enterprise-grade security and near real-time sync.
World-class support Proactive monitoring from technical experts, 24/7.
Straightforward pricing Pay for the rows you use, and nothing you don’t.