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

Copy to clipboard

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Copy to clipboard

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

Copy to clipboard

%%sql

DROP TABLE IF EXISTS customers_backup

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

CREATE TABLE customers_backup
AS
SELECT * FROM customers

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
12435 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

DROP TABLE IF EXISTS orders_backup

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
68883 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT * FROM orders_backup LIMIT 10

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

Copy to clipboard

[]

Copy to clipboard

%%sql

CREATE TABLE order_items_empty
AS
SELECT * FROM order_items WHERE 1 = 2

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.

Copy to clipboard

[]

Copy to clipboard

%%sql

SELECT count(1) FROM order_items_empty

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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;

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.
Done.
Done.

Copy to clipboard

[]