In one of my earlier posts, I have discussed about different approaches to create tables in Amazon Redshift database. Among these approaches, CREATE TABLE AS (CATS) and CREATE TABLE LIKE are two widely used create table command. Each command has its own significance. In this post, the differences, usage scenario and similarities of both commands will be discussed.

Both CREATE TABLE AS (CATS) and CREATE TABLE LIKE command can not create table independently. 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. Both commands can be used in following scenario.

Sort key, distribution key and column null/not null behavior during table creation using CREATE TABLE AS and CREATE TABLE LIKE.

In Redshift, there is no way to include sort key, distribution key and some others table properties on an existing table. The only way is to create a new table with required sort key, distribution key and copy data into the that table. Using both CREATE TABLE AS and CREATE TABLE LIKE commands, a table can be created with these table properties. For an example:

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

CREATE TABLE product(
 product_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(20) NOT NULL DEFAULT 'Byke',
 store_location_id INT NOT NULL,
 reorder_level INT NULL 
)
DISTKEY(store_location_id)
SORTKEY(product_name);

INSERT INTO product(product_name,category,store_location_id,reorder_level) VALUES
('Phonix',default,1,10),
('Terix','Easy Byke',1,10),
('Hon','Motor Byke',2,20);

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. Hence the statement portion will be as follows:

DISTKEY (product_id),
SORTKEY(product_name,category)

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

CREATE TABLE product_new_cats 
SORTKEY(product_name,category)
DISTKEY(product_id) AS
(SELECT * FROM product)

Now to the following command is used to get the records of the new “product_new_cats” table.

SELECT * FROM product_new_cats

The result is as follows:Figure 01: All records in product_new_cats

Now, we become sure, CATS statements copied all records from product table into the product_new_cats table. But what about sort key, distribution key and other settings? Let’s execute the following two commands:

SELECT tablename,
 "column",
 TYPE,
 distkey,
 sortkey,
 "notnull"
FROM pg_table_def
WHERE tablename = 'product';

SELECT tablename,
 "column",
 TYPE,
 distkey,
 sortkey,
 "notnull"
FROM pg_table_def
WHERE tablename = 'product_new_cats';

The above two commands returns two results below:Figure 02: product table settings

Figure 03: product_new_cats table settings.

From the above tow images, we found CREATE TABLE AS successfully created new sort and distribution keys. This command also inherits these settings from parent table. But one thing needs to point out here, CREATE TABLE AS command does not inherits “NOT NULL” setting from the parent table. Here, all columns of product_new_cats table are created as NULL(see Figure 03). But all columns of parent “product” table were declared as “NOT NULL” (Figure 02).

Alright, so far we have an idea about how “CREATE TABLE AS” command behaves. Now we will notice what happens when we create table using “CREATE TABLE LIKE” statement. Let’s execute the following scripts:

CREATE TABLE product_new_like (LIKE product);
SELECT * FROM product_new_like;

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. CREATE TABLE LIKE does not copy data from source table. So the SELECT * command will not return any rows. But it inherits columns settings. Let’s execute the SQL statement below and have a look the result:

SELECT tablename,
 "column",
 TYPE,
 distkey,
 sortkey,
 "notnull"
FROM pg_table_def
WHERE tablename = 'product_new_like';

Result:Figure 04: Create table like settings

By comparing output of “Figure 02” and “Figure 04” we see CREATE TABLE LIKE statement also inherits sort key, distribution key. But the main point to to note here that, CREATE TABLE LIKE command additionally inherits “NOT NULL” settings from the source table that CREATE TABLE AS does not.

Primary key, identity and default constraint behavior when table is created

using CREATE TABLE AS and CREATE TABLE LIKE:

In order to check whether CREATE TABLE AS and CREATE TABLE LIKE statement inherits primary key, default constraint and identity settings from source table or not.the following scripts can be executed.

 To check Primary key:

SELECT DISTINCT
     NAME AS tablename,
     pc.conname AS constraint_name
FROM stv_tbl_perm tp
     LEFT OUTER JOIN pg_constraint pc
          ON pc.conrelid = tp.id
WHERE name IN ('product', 'product_new_cats', 'product_new_like')

Figure 05: CATS and LIKE does not inherits primary key.

From the above image, we can see both CREATE TABLE AS, CREATE TABLE LIKE do not inherit primary key constraint from source table.

To check Default and Identity:

SELECT DISTINCT p.name AS tablename,
       attname AS columnname,
       a.atthasdef t
FROM pg_attribute a
     INNER JOIN stv_tbl_perm p
          ON p.id = attrelid
WHERE name IN ('product','product_new_cats','product_new_like')
      AND a.atthasdef = 1

Figure 06: CATS and LIKE does not inherits default constraint and identity.

The above query is used to select default constraint and identity column from all  three tables (product, product_new_cats,product_new_like). But we found only the source table , product is returned here. CREATE TABLE AS, CREATE TABLE LIKE does not inherit default value as well as identity settings. CREATE TABLE LIKE has an option to copy “DEFAULT” expression from the source table by using “INCLUDING DEFAULTS”.