Amazon Redshift Create View Syntax and Examples

A View creates a pseudo-table or virtual table. It appears exactly as a regular table, you can use it in SELECT statements, JOINs etc. A view can be created from a subset of rows or columns of another table, or many tables via a JOIN. Redshift uses the CREATE VIEW statement from PostgreSQL syntax to create View. In this article, we will check Redshift create view syntax and some examples on how to create views.

Just like views or table in other database, a Redshift view contains rows and columns. The fields in a view are fields from one or more real tables in the database. Redshift Views are read-only. The system does not allow you an insert, update, or delete on a view.

Redshift Create View Syntax

Below is the syntax for creating a view in Redshift:

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query
[ WITH NO SCHEMA BINDING ];

Where WITH NO SCHEMA BINDING indicates that the view is not bound to the underlying database objects, such as tables and user-defined functions. As a result, there is no dependency between the view and the objects it references. You can create a view even if the referenced objects don’t exist. Amazon Redshift doesn’t check for dependencies until the view is queried.

The only requirement is to qualify schema name in case if you use WITH NO SCHEMA BINDING option.

Redshift Create View Examples

Here is an example to create a view that consists of all action films. You can create view in any supported client or using PostgreSQL:

https://gist.github.com/65a4cf23e9f242c118ab6612685e7e44

Redshift Create View Examples Using WITH NO SCHEMA BINDING

If you specify the ‘with no schema binding’ then view is not bound to underlying redshift database object or user defined functions. This is unique and important feature that Redshift provides.

Below is the example to create view without reference table:

training=# create view mytestview as select * from public.test_view 
with no schema binding; 
CREATE VIEW

You should create table at the time of view execution otherwise you will end up with an error.

training=# select * from public.mytestview; 
ERROR: relation "public.test_view" does not exist

Restrictions on VIEWS in Redshift

Views on Redshift mostly work as other databases. But here are some restrictions:

You cannot create materialized view in Redshift.
You must re-build the view in case if you drop and re-crate underlying table.
Redshift query planner has trouble optimizing queries through a view.