So the SELECT * command will not return any rows. CREATE TABLE LIKE does not copy data from source table. The above statements creates a table named “product_new_like” using CREATE TABLE LIKE statement and later command select all records from the newly created table. Let’s execute the following scripts: CREATE TABLE product_new_like ( LIKE product) Now we will notice what happens when we create table using “CREATE TABLE LIKE” statement. But all columns of parent “product” table were declared as “NOT NULL” (Figure 02).Īlright, so far we have an idea about how “CREATE TABLE AS” command behaves. Here, all columns of product_new_cats table are created as NULL(see Figure 03). But one thing needs to point out here, CREATE TABLE AS command does not inherits “NOT NULL” setting from the parent table. This command also inherits these settings from parent table.

The above two commands returns two results below: Figure 02: product table settingsįigure 03: product_new_cats table settings.įrom the above tow images, we found CREATE TABLE AS successfully created new sort and distribution keys. But what about sort key, distribution key and other settings? Let’s execute the following two commands: SELECT tablename, Now, we become sure, CATS statements copied all records from product table into the product_new_cats table. The result is as follows: Figure 01: All records in product_new_cats Now to the following command is used to get the records of the new “product_new_cats” table. Hence the statement portion will be as follows: DISTKEY (product_id),Īs Redshift does not offer any ALTER TABLE statement to modify the existing table, the only way to achieve this goal either by using CREATE TABLE AS or LIKE statement. The below CREATE TABLE AS statement creates a new table named product_new_cats. Now to serve the business we will need to include “category” along with existing sort key product_name and also want to change the distribution key as product_id. INSERT INTO product(product_name,category,store_location_id,reorder_level) VALUES Product_id INT NOT NULL IDENTITY( 1,1) PRIMARY KEY,Ĭategory VARCHAR(20) NOT NULL DEFAULT ' Byke',

The following command creates a new table with Sort Key, Distribution Key and inserts three rows into the table.

Using both CREATE TABLE AS and CREATE TABLE LIKE commands, a table can be created with these table properties. The only way is to create a new table with required sort key, distribution key and copy data into the that table. In Redshift, there is no way to include sort key, distribution key and some others table properties on an existing table. Sort key, distribution key and column null/not null behavior during table creation using CREATE TABLE AS and CREATE TABLE LIKE. Both commands can be used in following scenario. In other words, CREATE TABLE AS, CREATE TABLE LIKE command can create a table by copying column settings and records (CATS only) from and existing table. In this post, the differences, usage scenario and similarities of both commands will be discussed.īoth CREATE TABLE AS (CATS) and CREATE TABLE LIKE command can not create table independently. Among these approaches, CREATE TABLE AS (CATS) and CREATE TABLE LIKE are two widely used create table command. In one of my earlier posts, I have discussed about different approaches to create tables in Amazon Redshift database.

Ilike redshift