Amazon Redshift database is getting popular day by day specially for its super fast performance. During database development or administration everyone needs to create table either permanently or temporarily. Developers usually create tables using DDL like “CREATE TABLE” statement. But sometimes they need to create table quickly, without issuing the long, restricted DDL statements for different purposes. In this post I will try to show some techniques to create new tables in Redshift database.
DDL is the most common approach to create tables in Redshift database. A typical DDL command is as follows:
CREATE TABLE testtable ( id INT NOT NULL identity(1,1), name VARCHAR(50) );
Using SELECT INTO:
SELECT INTO is another way to create new tables. SELECT INTO facilitates fast bulk data copy to new tables from an existing table in the most easiest way. This approach also keeps the column names and data type same as the source table. Let us see an example:
SELECT patiendId, patientName, dischargedate INTO adultpatients FROM patients WHERE age >18
The above statement creates a new table named “adultpatients” by selecting records from patients table considering the where clause.
Using CREATE TABLE LIKE:
When you want to create a table by copying columns names, data type, default constraint, NOT NULL attribute from an existing table, CREATE TABLE LIKE is the your best choice. The following statement creates a “product_backup” table by copying settings from the exiting “product” table.
CREATE TABLE product_backup (LIKE product)
Please be noted, CREATE TABLE LIKE does not copy data to the newly created table as SELECT INTO and CREATE TABLE AS command does.
CREATE TABLE AS (CATS):
Alike SELECT INTO, CREATE TABLE AS (CATS) statements also create a new table along with data. In order to create a table using CATS command, you need to provided a query. The newly created table is populated with data as per the query defined in the CREATE TABLE AS (CATS) statement. The statement below can can create a table using CATS syntax.
CREATE TABLE product_backup AS ( SELECT product_id, product_name FROM product )
The above query creates “product_backup” table based on the query provided inside the “AS” clause.
So, these are the commonly used approaches to create tables in Redshift based on different scenario. Hope this information will be helpful. In my future posts, I have a plan to describe about the in depth functionality, similarities and unique behaviors of each CREATE TABLE approach.