Updated March 4, 2023
Introduction of Redshift Create Table
Redshift Create Table is similar to creation of tables as we do in the other database. Create table syntax has the constraints defined in it as table constraints, column constraints, and attributes as such column attributes and table attributes. Defining the constraints maintains more reliable, and makes the data to be inserted as per our need. Example, column shouldn’t contain any NULL values. We can show it as <column_name> NOT NULL. They are other constraints specified as well, we can look into it in deep.
In this session let us learn more about the Create Table usage, syntax along with example:
Syntax:
Syntax of the Create Table function is as below:
Create [temp | temporary | IF NOT EXISTS ] Table table_name
( column_name data type [ column_constraint ] | [ Table Constraint ] |
[ column attribute] | [ table attribute] | LIKE
);
Where Column attributes defined as DEFAULT, IDENTITY, GENERATED BY DEFAULT AS IDENTITY, ENCODE, SORTKEY, and DISTKEY.
Where Column constraints are mentioned as NOT NULL, NULL, UNIQUE, PRIMARY KEY, and REFERENCE.
Where table constraints are mentioned as UNIQUE, PRIMARY KEY, and FOREIGN KEY [ column name ] REFERENCE TABLE ( COLUMN ).
Where table attributes are mentioned as DISTSTYLE, DISTKEY.
How does Redshift Create Table works?
Below let us look at how the Create Table works in the redshift. Let us create a basic table as below with columns NOT NULL and PRIMARY KEY defined.
Create table sample_table (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20)
)
Screenshot for the same is below:
Let us insert few rows data into table:
INSERT INTO SAMPLE_TABLE VALUES ( 1, 'Rose' );
INSERT INTO SAMPLE_TABLE VALUES ( 2, 'BTS' );
Now let us select the data from the table:
SELECT * FROM SAMPLE_TABLE;
Let us see the Screenshot for the same:
Now let us try to insert NULL into the ‘ID’ column. As we have mentioned the column as NOT NULL. The statement throws an error as below:
INSERT INTO SAMPLE_TABLE Values (NULL, 'BTS' );
Let us see the Screenshot for the same:
CREATE TABLE AS :
It allows to create table as similar as mentioned one with a column name, data types and copies the data as well.
CREATE TABLE SAMPLE_TABLE_DUP AS SELECT * FROM SAMPLE_TABLE;
Let us see the Screenshot for the same:
Select * from sample_table_dup;
It has similar data to the sample_table.
CREATE TABLE LIKE :
It allows to create a table that has a similar structure to mentioned table without any data copied.
CREATE TABLE LIKE_SAMPLE_TABLE (LIKE SAMPLE_TABLE);
Screenshot for the same:
Select * from LIKE_SAMPLE_TABLE;
It has a similar structure of the sample_table.
Temp or Temporary:
Table created as temporary and gets deleted at the end of the session.
Create temp table < Table name>;
IF NOT EXISTS :
This is used to create the table only if is not existing in the database. Using this we can avoid ‘Table name already exists error’.
Create table IF NOT EXISTS < table name >;
Example to create the existing table name to create the table.
Create table IF NOT EXISTS sample_table (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20)
)
Example:
Now let us see a sample Example to create the table in the Redshift.
Create table U_Data (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20),
SALARY DECIMAL
)
Screenshot for the same:
Let us insert the data into the table:
INSERT INTO U_data values ( 1, 'Rak' , 20000);
INSERT INTO U_data values ( 2, 'Rak' , 34000);
INSERT INTO U_data values ( 3, 'Rak' , 56000);
INSERT INTO U_data values ( 4, 'Rak', 76000);
INSERT INTO U_data values ( 5, 'Rak', 78000);
INSERT INTO U_data values ( 6, 'Rak', 56000);
INSERT INTO U_data values ( 7, 'Rak', 23000);
INSERT INTO U_data values ( 8, 'Rak', 29000);
select * from U_data;
Screenshot for the same:
Sort Key:
Create table Sort_Key_Data (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20),
SALARY DECIMAL,
Country Varchar(20),
PINCODE INT
) SORTKEY ( Country, pincode );
Screenshot for the same:
DISTSTYLES:
Three different style we have here:
- EVEN: uses round- robin method.
- KEY: matching rows will be inserted in same node.
- ALL: Copy of entire table stored in each node.
Let us create the same and see:
Create table DIST_Key_Data (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20),
SALARY DECIMAL,
User_id INT DISTKEY
)
Screenshot for the same:
Let us create the same and see :
Create table DIST_ALL_Data (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(20),
SALARY DECIMAL,
User_id INT
) DISTSTYLE ALL ;
Screenshot for the same:
Recommended Articles
This is a guide to Redshift Create Table. Here we discuss the introduction, syntax, How does Redshift Create Table works? and examples with code implementation. You may also have a look at the following articles to learn more –
