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.

Some examples:

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