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:
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 firstname.lastname@example.org.