CTAS - Create Table as SelectΒΆ
Let us understand details related to CTAS or Create Table As Select.
CTAS is primarily used to create tables based on query results.
Following are some of the use cases for which we typically use CTAS.
Taking back up of tables for troubleshooting and debugging performance issues.
Reorganizing the tables for performance tuning.
Getting query results into a table for data analysis as well as checking data quality.
We cannot specify column names and data types as part of
CREATE TABLE
clause in CTAS. It will pick the column names from theSELECT
clause.It is a good practice to specify meaningful aliases as part of the
SELECT
clause for derived values.Also it is a good practice to explicitly type cast to the desired data type for derived values.
%load_ext sql
The sql extension is already loaded. To reload it, use:
%reload_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%%sql
DROP TABLE IF EXISTS customers_backup
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE customers_backup
AS
SELECT * FROM customers
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
12435 rows affected.
[]
%%sql
DROP TABLE IF EXISTS orders_backup
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE orders_backup
AS
SELECT order_id,
to_char(order_date, 'yyyy')::int AS order_year,
to_char(order_date, 'MM')::int AS order_month,
to_char(order_date, 'dd')::int AS order_day_of_month,
to_char(order_date, 'DDD')::int AS order_day_of_year,
order_customer_id,
order_status
FROM orders
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
68883 rows affected.
[]
%%sql
SELECT * FROM orders_backup LIMIT 10
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id | order_year | order_month | order_day_of_month | order_day_of_year | order_customer_id | order_status |
---|---|---|---|---|---|---|
1021 | 2013 | 7 | 30 | 211 | 10118 | COMPLETE |
4068 | 2013 | 8 | 17 | 229 | 12293 | PENDING |
5881 | 2013 | 8 | 30 | 242 | 3715 | CLOSED |
7564 | 2013 | 9 | 9 | 252 | 8648 | CLOSED |
8766 | 2013 | 9 | 18 | 261 | 855 | COMPLETE |
8926 | 2013 | 9 | 19 | 262 | 10517 | ON_HOLD |
9290 | 2013 | 9 | 21 | 264 | 11879 | COMPLETE |
9793 | 2013 | 9 | 24 | 267 | 9809 | COMPLETE |
9816 | 2013 | 9 | 24 | 267 | 1753 | COMPLETE |
14047 | 2013 | 10 | 20 | 293 | 6473 | CLOSED |
Note
At times we have to create empty table with only structure of the table. We can specify always false condition such as 1 = 2
as part of WHERE
clause using CTAS.
%%sql
DROP TABLE IF EXISTS order_items_empty
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
[]
%%sql
CREATE TABLE order_items_empty
AS
SELECT * FROM order_items WHERE 1 = 2
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
[]
%%sql
SELECT count(1) FROM order_items_empty
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count |
---|
0 |
Note
Keeping databases clean is very important. It is a good practice to clean up any temporary tables created for learning or troubleshooting issues.
In this case all the tables created using CTAS are dropped
%%sql
DROP TABLE IF EXISTS customers_backup;
DROP TABLE IF EXISTS orders_backup;
DROP TABLE IF EXISTS order_items_empty;
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
Done.
Done.
[]