CREATE TABLE ALTER TABLE CREATE VIEW ALTER VIEW DROP VIEW

Aurora DSQL でサポートされている SQL コマンドのサブセット

この PostgreSQL セクションでは、サポートされている式に関する詳細情報を提供し、広範なパラメータセットとサブコマンドを持つコマンドに焦点を当てます。例えば、PostgreSQL の CREATE TABLE には句とパラメータが多数あります。このセクションでは、Aurora DSQL がこれらのコマンドでサポートするすべての PostgreSQL 構文要素について説明します。

トピック

CREATE TABLE
ALTER TABLE
CREATE VIEW
ALTER VIEW
DROP VIEW

`CREATE TABLE`

CREATE 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 は、テーブルの定義を変更します。


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 は、新しい永続ビューを定義します。Aurora DSQL では、一時ビューはサポートされていません。永続ビューのみがサポートされています。

サポートされている構文


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

説明

CREATE VIEW は、クエリのビューを定義します。ビューは物理的にマテリアライズされていません。代わりに、クエリでビューが参照されるたびにクエリが実行されます。

CREATE or REPLACE VIEW は似ていますが、同じ名前のビューが既に存在する場合は置き換えられます。新しいクエリは、既存のビュークエリによって生成されたのと同じ列 (つまり、同じ順序で同じデータ型を持つ同じ列名) を生成する必要がありますが、リストの最後に列を追加する場合があります。出力列を生成する計算は異なる場合があります。

スキーマ名を指定すると (CREATE VIEW myschema.myview ... など)、指定したスキーマでビューが作成されます。指定しない場合、現在のスキーマに作成されます。

ビューの名前は、同じスキーマ内の他のリレーション (テーブル、インデックス、ビュー) の名前とは異なる必要があります。

パラメータ

CREATE VIEW は、自動更新可能なビューの動作を制御するさまざまなパラメータをサポートしています。

RECURSIVE

再帰的ビューを作成します。構文: CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...; は CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name; と同等です。

再帰的ビューには、ビューの列名リストを指定する必要があります。

name

作成するビューの名前。オプションでスキーマ修飾されている場合があります。再帰的ビューには列名リストを指定する必要があります。

column_name

ビューの列に使用されるオプションの名前リスト。列名を指定しない場合、列名はクエリから推定されます。

WITH ( view_option_name [= view_option_value] [, ... ] )

この句は、ビューのオプションパラメータを指定します。次のパラメータがサポートされています。

check_option (enum) – このパラメータは local または cascaded のいずれかであり、WITH [ CASCADED | LOCAL ] CHECK OPTION を指定した場合と同等です。
security_barrier (boolean) – これは、ビューで行レベルのセキュリティを提供することを目的とする場合に使用します。現在、Aurora DSQL は行レベルのセキュリティをサポートしていませんが、このオプションでは、ビューの WHERE 条件 (および LEAKPROOF としてマークされている演算子を使用する条件) を最初に強制的に評価します。
security_invoker (boolean) – このオプションでは、基盤となるベースリレーションが、ビュー所有者ではなくビューのユーザーの権限と照合されます。詳細については、以下のメモを参照してください。

上記のオプションはすべて、ALTER VIEW を使用して既存のビューで変更できます。

query

ビューの列と行を提供する SELECT または VALUES コマンド。

WITH [ CASCADED | LOCAL ] CHECK OPTION – このオプションは、自動更新可能なビューの動作を制御します。このオプションを指定すると、ビューの INSERT および UPDATE コマンドがチェックされ、新しい行がビュー定義条件を満たしていることが確認されます (つまり、新しい行がチェックされ、ビューを通じて表示されていることを確認します)。そうでない場合、更新は拒否されます。CHECK OPTION が指定されていない場合、ビューの INSERT および UPDATE コマンドは、ビューでは表示されない行を作成できます。以下のチェックオプションがサポートされています。
LOCAL – 新しい行は、ビュー自体で直接定義された条件に対してのみチェックされます。基盤となるベースビューで定義された条件はチェックされません (CHECK OPTION も指定しない限り)。
CASCADED – 新しい行は、ビューおよび基盤となるすべてのベースビューの条件と照合されます。CHECK OPTION が指定されていて、LOCAL も CASCADED も指定されていない場合は、CASCADED が指定されたと見なされます。

注記

CHECK OPTION は RECURSIVE ビューでは使用できません。CHECK OPTION は、自動更新可能なビューでのみサポートされます。

メモ

ビューを削除するには、DROP VIEW ステートメントを使用します。

ビューの列の名前とデータ型は慎重に検討する必要があります。例えば、CREATE VIEW vista AS SELECT 'Hello World'; は、列名のデフォルトが ?column?; であるため、お勧めしません。また、列のデータ型はデフォルトで text になりますが、これは必要ではない可能性があります。

より良い方法は、CREATE VIEW vista AS SELECT text 'Hello World' AS hello; のように列名とデータ型を明示的に指定することです。

デフォルトでは、ビューで参照される基盤となるベースリレーションへのアクセスは、ビュー所有者のアクセス許可によって決まります。場合によっては、これは基盤となるテーブルへの安全で制限されたアクセスを提供するために使用できます。ただし、すべてのビューが改ざんから保護されているわけではありません。

ビューの security_invoker プロパティが true に設定されている場合、基盤となるベースリレーションへのアクセスは、ビューの所有者ではなく、クエリを実行するユーザーのアクセス許可によって決定されます。したがって、セキュリティ実行者ビューのユーザーは、ビューおよび基盤となるベースリレーションに対する適切なアクセス許可を持っている必要があります。
基盤となるベースリレーションのいずれかがセキュリティ実行者ビューである場合、元のクエリから直接アクセスされたものとして処理されます。したがって、セキュリティ実行者ビューは、security_invoker プロパティなしのビューからアクセスされた場合でも、常に現在のユーザーのアクセス許可を使用して、基盤となるベースリレーションをチェックします。
ビューで呼び出された関数は、ビューを使用するクエリから直接呼び出された場合と同様に処理されます。したがって、ビューのユーザーは、ビューで使用されるすべての関数を呼び出すアクセス許可を持っている必要があります。ビュー内の関数は、関数が SECURITY INVOKER または SECURITY DEFINER として定義されているかに応じて、クエリを実行するユーザーの権限または関数の所有者の権限で実行されます。例えば、ビューで直接 CURRENT_USER を呼び出すと、ビューの所有者ではなく、常に実行ユーザーを返します。これはビューの security_invoker 設定の影響を受けないため、security_invoker が false に設定されたビューは SECURITY DEFINER 関数と同等ではありません。
ビューを作成または置換するユーザーは、スキーマ内の参照オブジェクトを検索するために、ビュークエリで参照されるスキーマに対する USAGE 権限を持っている必要があります。ただし、この参照はビューが作成または置換された場合にのみ行われることに注意してください。したがって、ビューのユーザーは、セキュリティ実行者ビューの場合でも、ビュークエリで参照されるスキーマではなく、ビューを含むスキーマに対する USAGE 権限のみを必要とします。
CREATE OR REPLACE VIEW が既存のビューで使用さると、ビューを定義する SELECT ルールに加えて、WITH ( ... ) パラメータとその CHECK OPTION のみが変更されます。所有権、アクセス許可、SELECT 以外のルールなど、他のビュープロパティは変更されません。ビューを置き換えるには、ビューの所有者でなければなりません (所有ロールのメンバーである場合も含まれます)。

更新可能ビュー

簡単なビューは自動更新可能です。システムは、ビューに対する INSERT、UPDATE、DELETE ステートメントを通常のテーブルの場合と同じ方法で使用できるようにします。ビューは、以下の条件をすべて満たすと、自動更新可能になります。

ビューの FROM リストに、テーブルまたは更新可能な別のビューの項目が 1 つだけ含まれている必要があります。
ビューの定義の最上位レベルには、WITH、DISTINCT、GROUP BY、HAVING、LIMIT または OFFSET 句を含めることはできません。
ビューの定義の最上位レベルには、セットオペレーション (UNION、INTERSECT または EXCEPT) を含めることはできません。
ビューの選択リストに、集約関数、ウィンドウ関数、集合を返す関数を含めることはできません。

自動更新可能ビューでは、更新可能な列と更新不可能な列を混在させることができます。基盤となるベースリレーションの更新可能な列を単純に参照する列は更新可能です。そうでなければ、列は読み取り専用で、INSERT または UPDATE ステートメントでその列に値を割り当てようとしたらエラーが発生します。

ビューが自動更新可能であれば、システムはビューに対する INSERT、UPDATE または DELETE ステートメントを基盤となるベースリレーションへの対応するステートメントに変換します。ON CONFLICT UPDATE 句を持つ INSERT ステートメントは完全にサポートされます。

自動更新可能ビューが WHERE 条件を持つ場合、その条件は、ビューの UPDATE および DELETE ステートメントによって変更できるベースリレーションの行を制限します。ただし、UPDATE は、WHERE 条件を満たさないように行を変更し、ビューで非表示にすることができます。同様に、INSERT コマンドは、WHERE 条件を満たさず、そのためビューを通して参照できない行をベースリレーションに挿入する可能性があります。ON CONFLICT UPDATE は、ビューを通して見えない既存の行にも同様に影響を与える可能性があります。

CHECK OPTION は、INSERT および UPDATE コマンドがビューに表示されない行を作るのを防ぐために使用できます。

自動更新可能ビューが security_barrier プロパティを持つ場合、ビューのすべての WHERE 条件 (および LEAKPROOF の演算子を使ったすべての条件) が必ず、ビューのユーザーが追加した条件より前に評価されます。このため、最終的には、戻されない行 (ユーザーの WHERE 条件を満たさないため) はロックされてしまう可能性があります。EXPLAIN を使って、リレーションのレベルで適用されている条件 (その結果、行をロックしない) と適用されていない条件を確認できます。

これらの条件をすべて満たさないより複雑なビューは、デフォルトで読み取り専用です。システムは、ビューに対する挿入、更新、削除を許可しません。

注記

ビューに対する挿入、更新、削除を実行するユーザーには、ビューに対する対応する挿入、更新、または削除の権限が必要です。デフォルトでは、ビューの所有者は基盤となるベースリレーションに対する適切な権限を持っている必要がありますが、更新を行うユーザーは基盤となるベースリレーションに対するアクセス許可を必要としません。ただし、ビューの security_invoker が true に設定されている場合、ビューの所有者ではなく更新を行うユーザーが基盤となるベースリレーションに対する適切な権限を持っている必要があります。

例

すべてのコメディ映画からなるビューを作成します。


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

これは、ビューを作成した時点で film テーブル内にある列を持つビューを作成します。ビューを作成するために * が使用されましたが、その後にテーブルに追加された列はビューには含まれません。

LOCAL CHECK OPTION でビューを作成します。


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

これにより、新しい行の kind と classification の両方を確認するビューが作成されます。

更新可能な列と更新不可能な列が混在するビューを作成します。


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

このビューは、INSERT、UPDATE、および DELETE をサポートします。フィルムテーブルのすべての列は更新可能ですが、計算される列 country と avg_rating は読み取り専用です。


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

注記

この CREATE では再帰的ビューの名前はスキーマ修飾されていますが、その内側の自己参照はスキーマ修飾されていません。これは、暗黙的に作成される共通テーブル式 (CTE) の名前はスキーマ修飾できないためです。

互換性

CREATE OR REPLACE VIEW は PostgreSQL の言語拡張です。WITH ( ... ) 句も拡張であり、セキュリティバリアビューとセキュリティ実行者ビューも同様です。Aurora DSQL は、これらの言語拡張をサポートしています。

ALTER VIEW

ALTER VIEW ステートメントは、既存のビューのさまざまなプロパティの変更を許可し、Aurora DSQL はこのコマンドのすべての PostgreSQL 構文をサポートします。

サポートされている構文


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 [, ... ] )

説明

ALTER VIEW は、ビューの各種補助プロパティを変更します。(ビューを定義するクエリを変更する場合は、CREATE OR REPLACE VIEW を使用します。) ALTER VIEW を使用するには、ビューの所有者である必要があります。ビューのスキーマを変更するには、新しいスキーマに対する CREATE 権限も必要です。所有者を変更するには、新しい所有者ロールに対して SET ROLE ができなければなりません。また、そのロールにはビューのスキーマに対する CREATE 権限が必要です。(これらの制限は、ビューの削除および再作成によりユーザーが実行できないことを、所有者の変更により実行できないようにするためのものです。)

パラメータ

ALTER VIEW 個のパラメータ

name

既存のビューの名前 (オプションでスキーマ修飾可) です。

column_name

既存の列の新しい名前です。

IF EXISTS

ビューが存在しなかったとしてもエラーになりません。この場合、通知が発行されます。

SET/DROP DEFAULT

これらのフォームは、列のデフォルト値を設定または削除します。ビュー列のデフォルト値は、ビューを対象とした任意の INSERT または UPDATE コマンドに代入されます。ビューのデフォルトは、基になるリレーションのデフォルト値よりも優先されます。

new_owner

ビューの新しい所有者のユーザー名です。

new_name

ビューの新しい名前です。

new_schema

ビューの新しいスキーマです。

SET ( view_option_name [= view_option_value] [, ... ] )

RESET ( view_option_name [, ... ] )

ビューのオプションを設定またはリセットします。サポートされているオプションは以下のとおりです。

check_option (enum) – ビューのチェックオプションを変更します。値は local または cascaded である必要があります。
security_barrier (boolean) – ビューのセキュリティバリアプロパティを変更します。値は、true や false などのブール値である必要があります。
security_invoker (boolean) – ビューのセキュリティバリアプロパティを変更します。値は、true や false などのブール値である必要があります。

メモ

PostgreSQL の歴史上の理由から、ALTER TABLE はビューでも使用できますが、ビューで使用できる ALTER TABLE のバリエーションは、上記で示したものと同じものだけです。

例

ビュー foo の名前を bar に変更します。


ALTER VIEW foo RENAME TO bar;

更新可能なビューにデフォルトの列値を付与します。


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

互換性

ALTER VIEW は、Aurora DSQL がサポートする SQL 標準の PostgreSQL 拡張です。

`DROP VIEW`

DROP VIEW ステートメントは既存のビューを削除します。Aurora DSQL は、このコマンドの完全な PostgreSQL 構文をサポートしています。

サポートされている構文


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

説明

DROP VIEW は既存のビューを削除します。このコマンドを実行できるのは、ビューの所有者のみです。

パラメータ

IF EXISTS: ビューが存在しなかったとしてもエラーになりません。この場合、通知が発行されます。
name: 削除するビューの名前 (オプションでスキーマ修飾可) です。
CASCADE: 削除するビューに依存しているオブジェクト (他のビューなど) を自動的に削除し、さらにそれらのオブジェクトに依存するすべてのオブジェクトも削除します。
RESTRICT: 依存するオブジェクトがある場合は、ビューの削除を拒否します。これがデフォルトです。

例


DROP VIEW kinds;

互換性

このコマンドは SQL 標準に準拠していますが、標準ではコマンドごとに 1 つのビューしか削除できないこと、および Aurora DSQL がサポートする PostgreSQL 拡張である IF EXISTS オプションを除きます。

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

サポートされている SQL の機能

サポートされていない PostgreSQL 機能