Supported subsets of SQL commands in Aurora DSQL

This PostgreSQL section provides detailed information about supported expressions, focusing on commands with extensive parameter sets and subcommands. For example, CREATE TABLE in PostgreSQL offers many clauses and parameters. This section describes all the PostgreSQL syntax elements that Aurora DSQL supports for these commands.

CREATE TABLE

CREATE TABLE defines a new table.

CREATE TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression )|
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | ALL }

index_parameters in UNIQUE, and PRIMARY KEY constraints are:
[ INCLUDE ( column_name [, ... ] ) ]

ALTER TABLE

ALTER TABLE changes the definition of a table.

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type 
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

CREATE VIEW

CREATE VIEW defines a new persistent view. Aurora DSQL does not support temporary views; only permanent views are supported.

Supported syntax

CREATE [ OR REPLACE ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]    

Description

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

CREATE or REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be different.

If a schema name is given, such as CREATE VIEW myschema.myview ...) then the view is created in the specified schema. Otherwise, it is created in the current schema.

The name of the view must be distinct from the name of any other relation (table, index, view) in the same schema.

Parameters

Notes

Use the DROP VIEW statement to drop views.

The names and data types of the view's columns should be carefully considered. For example, CREATE VIEW vista AS SELECT 'Hello World'; is not recommended because the column name defaults to ?column?;. Also, the column data type defaults to text, which might not be what you wanted.

A better approach is to explicitly specify the column name and data type, such as: CREATE VIEW vista AS SELECT text 'Hello World' AS hello;.

By default, access to the underlying base relations referenced in the view is determined by the permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables. However, not all views are secure against tampering.

Updatable views

Simple views are automatically updatable: the system will allow INSERT, UPDATE, and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it's a simple reference to an updatable column of the underlying base relation. Otherwise, the column is read-only, and an error occurs if an INSERT or UPDATE statement attempts to assign a value to it.

For automatically updatable views, the system converts any INSERT, UPDATE, or DELETE statement on the view into the corresponding statement on the underlying base relation. INSERT statements with an ON CONFLICT UPDATE clause are fully supported.

If an automatically updatable view contains a WHERE condition, the condition restricts which rows of the base relation are available for modification by UPDATE and DELETE statements on the view. However, an UPDATE can change a row so that it no longer satisfies the WHERE condition, making it invisible through the view. Similarly, an INSERT command can potentially insert base-relation rows that don't satisfy the WHERE condition, making them invisible through the view. ON CONFLICT UPDATE may similarly affect an existing row not visible through the view.

You can use the CHECK OPTION to prevent INSERT and UPDATE commands from creating rows that aren't visible through the view.

If an automatically updatable view is marked with the security_barrier property, all the view's WHERE conditions (and any conditions using operators marked as LEAKPROOF) are always evaluated before any conditions that a user of the view has added. Note that due to this, rows that aren't ultimately returned (because they don't pass the user's WHERE conditions) may still end up being locked. You can use EXPLAIN to see which conditions are applied at the relation level (and therefore don't lock rows) and which aren't.

A more complex view that doesn't satisfy all these conditions is read-only by default: the system doesn't allow an insert, update, or delete on the view.

Examples

To create a view consisting of all comedy films.

CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

This will create a view containing the columns that are in the film table at the time of view creation. Though * was used to create the view, columns added later to the table will not be part of the view.

Create a view with LOCAL CHECK OPTION.

CREATE VIEW pg_comedies AS
    SELECT *
    FROM comedies
    WHERE classification = 'PG'
    WITH CASCADED CHECK OPTION;

This will create a view that checks both the kind and classification of new rows.

Create a view with a mix of updatable and non-updatable columns.

CREATE VIEW comedies AS
    SELECT f.*,
           country_code_to_name(f.country_code) AS country,
           (SELECT avg(r.rating)
            FROM user_ratings r
            WHERE r.film_id = f.id) AS avg_rating
    FROM films f
    WHERE f.kind = 'Comedy';

This view will support INSERT, UPDATE,and DELETE. All the columns from the films table will be updatable, whereas the computed columns country and avg_rating will be read-only.

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;

Compatibility

CREATE OR REPLACE VIEW is a PostgreSQL language extension. The WITH ( ... ) clause is an extension as well, as are security barrier views and security invoker views. Aurora DSQL supports these language extensions.

ALTER VIEW

The ALTER VIEW statement allows changing various properties of an existing view, and Aurora DSQL supports all the PostgreSQL syntax for this command.

Supported syntax

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )

Description

ALTER VIEWchanges various auxiliary properties of a view. (If you want to modify the view's defining query, use CREATE OR REPLACE VIEW.) You must own the view to use ALTER VIEW. To change a view's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the view's schema. These restrictions enforce that altering the owner doesn't do anything you couldn't do by dropping and recreating the view.)

Parameters

Notes

For historical PostgreSQL reasons, ALTER TABLE can be used with views too; but the only variants of ALTER TABLE that are allowed with views are equivalent to the ones shown previously.

Examples

Renaming the view foo to bar.

ALTER VIEW foo RENAME TO bar;

Attaching a default column value to an updatable view.

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time
    

Compatibility

ALTER VIEW is a PostgreSQL extension of the SQL standard that Aurora DSQL supports.

DROP VIEW

The DROP VIEW statement removes an existing view. Aurora DSQL supports the full PostgreSQL syntax for this command.

Supported syntax

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Description

DROP VIEW drops an existing view. To execute this command you must be the owner of the view.

Parameters

Examples

DROP VIEW kinds;          

Compatibility

This command conforms to the SQL standard, except that the standard only allows one view to be dropped per command, and apart from the IF EXISTS option, which is a PostgreSQL extension that Aurora DSQL supports.