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 the SELECT 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.

[]

Mastering SQL using Postgresql

CTAS - Create Table as Select¶