How to Create table in Redshift

Amazon Redshift is a PostgreSQL-based data warehousing platform used by many businesses around the world. Many times you may need to make a table in Redshift. In this article, we will look at how to create table in Redshift.

How to Create table in Redshift

It is very easy to make table in Redshift. We will use CREATE TABLE statement to create table in Redshift. However, there are three variations to this command.

CREATE TABLE

Here is the syntax to create table in Redshift using CREATE TABLE statement

CREATE TABLE [IF NOT EXISTS] table_name ( 
column_name1 data_type1,
column_name2 data_type2,
...
 )

CREATE TABLE statement allows you to create new table by specifying everything from scratch – table name, column names, data types.

Please note, if the table already exists, then you will see an error. Therefore, it is advisable to use IF NOT EXISTS keywords while creating tables in redshift.

Here is an example to create table using CREATE TABLE statement.

# create table if not exists sales(
  id int,
  order_date date,
  amount int
);

Also read : How to Insert Data into Redshift Table

CREATE TABLE AS

Here is the second variation, using CREATE TABLE AS statement.

CREATE TABLE AS table_name SELECT * from old_table

In the above statement, we create and populate the table from the result of a select query. In this case, the column names, data types and data will be copied from source to destination tables.

Here is an example to create sales table from orders.

# create table as sales select * from orders;

Also read : How to Calculate Running Total in Redshift

CREATE TABLE LIKE

If you want to create table from another table by copying its structure (column names & data type), but not its data, then use CREATE TABLE LIKE statement. Here is its syntax.

CREATE TABLE table_name LIKE old_table_name

Here is the example to create sales table by copying orders table’s structure.

# create table sales like orders;