How to Create a View in Redshift
Views allow to encapsulate or "hide" complexities.
To create a Redshift view, use the
CREATE VIEW command:
CREATE OR REPLACE VIEW view_name AS <select statement>;
While optional, the
OR REPLACE part is frequently used so the the view is updated if already exists.
-- A view to show only beta users CREATE VIEW beta_users_vw AS SELECT * FROM users WHERE beta = 1; -- A view to limit read access to only certain columns CREATE VIEW users_basic_vw AS SELECT first_name, last_name, telephone_number FROM users;
You may notice all view names above end in
_vw. This is an example convention some developers adopt to easily distinguish views from tables.
Redshift view creation may include the
WITH NO SCHEMA BINDING clause. This specifies that the view is not bound to the underlying database objects, such as tables and user-defined functions. This means you can create a view even if the referenced objects don't exist and you can drop or alter a referenced object without affecting the view. One restriction with using this clause is that the tables and views referenced in the
SELECT statement must be qualified with a schema name.
-- this won't work because the referenced object does not have the schema name CREATE OR REPLACE VIEW this_view AS SELECT col1, col2 FROM events WITH NO SCHEMA BINDING; -- the corrected statement has the schema name of the referenced table CREATE OR REPLACE VIEW this_view AS SELECT col1, col2 FROM public.events WITH NO SCHEMA BINDING;
Spread the wordTweet