ALTER VIEW

Modifies an existing view's definition. The view must exist before it can be altered.

For more information on views, see the Views documentation.

Syntax​

ALTER VIEW view_name AS ( query )

Parameters​

ParameterDescription
view_nameName of the existing view to modify
queryNew SELECT statement defining the view

Examples​

Change view query​

Alter view definition
-- Original view
CREATE VIEW summary AS (
  SELECT ts, symbol, max(price) as max_price
  FROM trades
  SAMPLE BY 1h
)

-- Alter to change aggregation
ALTER VIEW summary AS (
  SELECT ts, symbol, avg(price) as avg_price
  FROM trades
  SAMPLE BY 1h
)

Add columns to view​

Expand view columns
-- Original view
CREATE VIEW trade_view AS (
  SELECT ts, symbol, price FROM trades
)

-- Add volume column
ALTER VIEW trade_view AS (
  SELECT ts, symbol, price, quantity FROM trades
)

Change filtering​

Modify view filter
-- Original view
CREATE VIEW filtered AS (
  SELECT * FROM trades WHERE price > 100
)

-- Change filter threshold
ALTER VIEW filtered AS (
  SELECT * FROM trades WHERE price > 200
)

Update parameterized view​

Modify parameterized view
-- Original view with parameter
CREATE VIEW by_price AS (
  DECLARE @min := 0
  SELECT * FROM trades WHERE price >= @min
)

-- Change default value
ALTER VIEW by_price AS (
  DECLARE @min := 100
  SELECT * FROM trades WHERE price >= @min
)

Add parameters to existing view​

Add DECLARE to view
-- Original view without parameters
CREATE VIEW trades_filtered AS (
  SELECT * FROM trades WHERE price > 100
)

-- Add parameter
ALTER VIEW trades_filtered AS (
  DECLARE @threshold := 100
  SELECT * FROM trades WHERE price > @threshold
)

Errors​

ErrorCause
view does not exist [view=name]View with specified name doesn't exist
table does not exist [table=name]Referenced table in new query doesn't exist
Invalid columnColumn in new query doesn't exist
circular dependency detectedNew definition would create circular reference
Access denied [ALTER VIEW on view_name]User lacks ALTER VIEW permission (Enterprise)
Access denied [SELECT on table_name]User lacks SELECT on tables in new definition (Enterprise)

Behavior​

  • Altering a view replaces its entire definition
  • The new query must be valid at the time of alteration
  • Dependent views may become invalid if the altered view's output changes
  • Use CREATE OR REPLACE VIEW as an alternative if you want to create the view when it doesn't exist

Definer permissions transfer (Enterprise)​

When a user alters a view, the view's definer permissions transfer to that user. This means:

  • The view now runs with the permissions of the user who performed the ALTER
  • Other users querying the view can access data the new definer has access to
  • The original creator's permissions no longer apply to the view
Definer transfer example
-- UserA creates view on table1 (UserA has SELECT on table1)
-- UserA is the "definer"
CREATE VIEW my_view AS (SELECT * FROM table1);

-- UserB alters view to reference table2 (UserB has SELECT on table2)
-- UserB becomes the new "definer"
ALTER VIEW my_view AS (SELECT * FROM table2);

-- UserC (with SELECT on my_view) now sees table2 data
-- using UserB's permissions
SELECT * FROM my_view;

Permissions (Enterprise)​

Altering a view requires:

  1. ALTER VIEW permission on the view
  2. SELECT permission on all tables referenced in the new definition
-- Grant ALTER VIEW permission
GRANT ALTER VIEW ON my_view TO username;

You cannot use ALTER VIEW to access tables you don't have permission for:

-- This fails if user doesn't have SELECT on secret_table
ALTER VIEW my_view AS (SELECT * FROM secret_table);
-- Error: Access denied for username [SELECT on secret_table]

See also​