Oracle® Database のユーザーとスキーマを Cloud SQL for PostgreSQL に移行する

このドキュメントは、Oracle® 11g/12c データベースから Cloud SQL for PostgreSQL バージョン 12 への移行を計画し、実施する際に必要な情報とガイダンスを提供するシリーズの一つです。このドキュメントでは、ユーザー、スキーマ、テーブル、インデックス、ビューの作成に関連する Oracle® Database と Cloud SQL for PostgreSQL の基本的な違いについて説明します。

このシリーズは、最初の設定のパートに加えて、次のパートから構成されています。

Oracle から Cloud SQL for PostgreSQL への移行: 用語と機能
Oracle から Cloud SQL for PostgreSQL への移行: データ型、ユーザー、テーブル
Oracle から Cloud SQL for PostgreSQL への移行: クエリ、ストアドプロシージャ、関数、トリガー
Oracle から Cloud SQL for PostgreSQL への移行: セキュリティ、オペレーション、モニタリング、ロギング
Oracle データベースのユーザーとスキーマの Cloud SQL for PostgreSQL への移行（このドキュメント）

Oracle と Cloud SQL for PostgreSQL の用語の違い

Oracle と Cloud SQL for PostgreSQL では、インスタンス、データベース、ユーザー、スキーマのアーキテクチャと用語が異なります。これらの違いの概要については、このシリーズの用語パートをご覧ください。

Oracle 構成のエクスポート

Cloud SQL for PostgreSQL への移行を計画する際にまず行う作業として、移行元の Oracle データベースで既存のパラメータ設定を確認する必要があります。メモリ割り当て、文字セット、ストレージパラメータに関する設定は特に重要です。これらの設定により、Cloud SQL for PostgreSQL 移行先環境の初期構成とサイズを把握できます。Oracle のパラメータ設定を抽出する方法はいくつかあります。一般的な方法は次のとおりです。

自動ワークロードリポジトリ（AWR）レポート。リソースの割り当てデータ（CPU、RAM）、インスタンスパラメータの構成、最大アクティブセッションを確認できます。
DBA_HIST、V$OSSTAT、V$LICENSE。CPU 使用率の詳細を確認できます。
V$PARAMETER ビュー。データベース構成パラメータを確認できます。
V$NLS_PARAMETERS ビュー。データベース言語パラメータを確認できます。
DBA_DATA_FILES ビュー。データベースのストレージサイズを計算できます。
Oracle SPFILE。データベースインスタンスの構成を確認できます。
ジョブスケジューラツール（crontab など）。考慮が必要な定期的なバックアップまたはメンテナンスの時間枠を確認できます。

Cloud SQL for PostgreSQL にユーザーをインポートして構成する

おおまかに言うと、Oracle の各スキーマは PostgreSQL で独自のスキーマとして作成する必要があります。Oracle データベースでは、ユーザーはスキーマと同義です。つまり、ユーザーを作成するとスキーマが作成されます。ユーザーとスキーマの間には常に 1 対 1 の関係があります。PostgreSQL では、ユーザーとスキーマは別々に作成されます。対応するスキーマを作成せずにユーザーを作成できます。PostgreSQL で同じ Oracle のユーザー構造またはスキーマ構造を維持するには、ユーザーごとにスキーマを作成します。

次の表に変換例を示します。

操作の種類	データベースの種類	コマンドの比較
ユーザーとスキーマの作成	`Oracle`	`CREATE USER username IDENTIFIED BY password;`
	`PostgreSQL`	PostgreSQL では、ユーザーとスキーマは別個のコンセプトであるため、2 つの別々の `CREATE` ステートメントが必要です。 `CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;`
ロールの割り当て	`Oracle`	`GRANT CONNECT TO username;`
	`PostgreSQL`	`GRANT pg_monitor TO username;`
権限の付与	`Oracle`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
	`PostgreSQL`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
権限の取り消し	`Oracle`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
	`PostgreSQL`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
DBA / スーパーユーザーの付与	`Oracle`	`GRANT DBA TO username;`
	`PostgreSQL`	`GRANT cloudsqlsuperuser TO username;`
ユーザーのドロップ	`Oracle`	`DROP USER username CASCADE;`
	`PostgreSQL`	PostgreSQL では、ユーザーとスキーマは別個のコンセプトであるため、2 つの別々の `DROP` ステートメントが必要です。 `DROP USER username; DROP SCHEMA schema_name CASCADE;`
ユーザーメタデータ	`Oracle`	`DBA_USERS`
	`PostgreSQL`	`pg_catalog.pg_user`
権限メタデータ	`Oracle`	`DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS`
	`PostgreSQL`	`pg_catalog.pg_roles`
CLI 接続文字列	`Oracle`	`sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid`
	`PostgreSQL`	パスワードプロンプトがない場合: `PGPASSWORD=password psql -h hostname -U username -d database_name` パスワードプロンプトがある場合: `psql -h hostname -U username -W -d database_name`

Oracle 12c データベースユーザー

Oracle 12c には、共通ユーザーとローカルユーザーの 2 つのタイプがあります。共通ユーザーは PDB などのルート CDB に作成されます。ユーザー名には C## という接頭辞が付いています。ローカルユーザーは特定の PDB でのみ作成されます。名前が同じでも、異なるデータベースユーザーを複数の PDB で作成できます。Oracle 12c から PostgreSQL に移行するときは、PostgreSQL のアーキテクチャに合うようユーザーと権限を変更します。以下に、この違いを示す 2 つの一般的な例を示します。

# Oracle local user
SQL> ALTER SESSION SET CONTAINER=pdb;
SQL> CREATE USER username IDENTIFIED BY password QUOTA 50M ON USERS;

# PostgreSQL user for a single database and schema
postgres=> CREATE USER username WITH PASSWORD 'password';
postgres=> GRANT CONNECT TO DATABASE database_name TO username;
postgres=> GRANT USAGE ON SCHEMA schema_name TO username;
postgres=> -- Optionally, grant object privileges in the schema
postgres=> GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL PROCEDURES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL ROUTINES IN SCHEMA schema_name TO username;

# Oracle common user
SQL> CREATE USER c##username IDENTIFIED BY password CONTAINER=ALL;

# PostgreSQL user with permissions for all database (use the local user script above and repeat it for each database and schema)

Google Cloud コンソールからユーザーを管理する

現在 Cloud SQL for PostgreSQL に構成されているユーザーを確認するには、Google Cloud コンソールの次のページに移動します。

[Google Cloud] > [ストレージ] > [SQL] > [インスタンス] > [ユーザー]

[ユーザー] ページのスクリーンショット。

テーブル定義とビュー定義のインポート

Oracle と PostgreSQL には、大文字と小文字の区別の点で違いがあります。Oracle では、大文字と小文字は区別されません。PostgreSQL では、二重引用符で囲まれている場合を除き、大文字と小文字は区別されません。DBMS_METADATA.GET_DDL など、Oracle 用の多くのスキーマエクスポートツールと SQL 生成ツールには、オブジェクト名に二重引用符が自動的に追加されます。これらの引用符は、移行後にさまざまな問題を引き起こす可能性があります。PostgreSQL でオブジェクトを作成する前に、オブジェクト定義を囲むすべての引用符をデータ定義言語（DDL）ステートメントから削除することをおすすめします。

テーブル作成の構文

Oracle から PostgreSQL データ型にテーブルを変換する場合は、最初のステップとして、Oracle 作成テーブルステートメントを移行元データベースから抽出します。次のサンプルクエリでは、HR スキーマから locations テーブルの DDL を抽出しています。

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS') FROM DUAL;

CREATE TABLE "HR"."LOCATIONS"
   (  "LOCATION_ID" NUMBER(4,0),
  "STREET_ADDRESS" VARCHAR2(40),
  "POSTAL_CODE" VARCHAR2(12),
  "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
  "STATE_PROVINCE" VARCHAR2(25),
  "COUNTRY_ID" CHAR(2),
  CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")
...
      CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE

完全な出力には、PostgreSQL の CREATE TABLE ステートメントでサポートされていないために除外されていたストレージ要素、インデックス、テーブルスペースの情報も含まれます。

DDL が抽出されたら、名前を囲んでいる引用符を削除し、Oracle から PostgreSQL のデータ型の変換表に従ってテーブル変換を行います。各列のデータ型がそのまま変換できるかどうか確認します。できない場合は、変換表に従って別のデータ型を選択します。たとえば、locations テーブルの変換後の DDL は次のようになります。

CREATE TABLE HR.LOCATIONS (
  LOCATION_ID NUMERIC(4,0),
  STREET_ADDRESS VARCHAR(40),
  POSTAL_CODE VARCHAR(12),
  CITY VARCHAR(30) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE VARCHAR(25),
  COUNTRY_ID CHAR(2),
  CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID),
  CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID)
REFERENCES HR.COUNTRIES (COUNTRY_ID)
)

CREATE TABLE AS SELECT（CTAS）

CREATE TABLE AS SELECT（CTAS）ステートメントは、既存のテーブルに基づいて新しいテーブルを作成する場合に使用します。コピーされるのは列名と列のデータ型のみで、制約やインデックスはコピーされません。PostgreSQL は CTAS 機能に関する ANSI SQL 標準をサポートし、Oracle CTAS ステートメントと互換性があります。

Oracle 12c の非表示列

PostgreSQL は非表示列をサポートしていません。回避策として、表示されている列だけを保持するビューを作成します。

テーブル制約

Oracle には、ALTER TABLE コマンドでテーブルの作成時または作成後に定義できる 6 種類のテーブル制約があります。Oracle の制約タイプは PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL、REF です。また、Oracle では次のオプションを使用して、制約の状態を制御できます。

INITIALLY IMMEDIATE: 後続の各 SQL ステートメント（デフォルト状態）の最後にある制約を確認します。
DEFERRABLE/NOT DEFERRABLE: SET CONSTRAINT ステートメントが送信されるまで、後続のトランザクションで COMMIT 句を使用できるようにします。
INITIALLY DEFERRED: 後続のトランザクションの最後にある制約を確認します。
VALIDATE/NO VALIDATE: 新しい行または変更された行でエラーを確認します（または意図的には確認しません）。これらのパラメータは、制約が ENABLED か DISABLED かによって異なります。
ENABLED/DISABLED: 作成後に制約を適用するかどうかを指定します（デフォルトは ENABLED）。

PostgreSQL では、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL、EXCLUDE の 6 種類のテーブル制約もサポートされています。ただし、Oracle と PostgreSQL の制約タイプには次のような大きな違いがあります。

PostgreSQL は、Oracle の REF 制約をサポートしていません。
PostgreSQL は、外部キー制約の参照列にインデックスを自動作成しません。インデックスが必要な場合は、参照元の列で別の CREATE INDEX ステートメントが必要です。
PostgreSQL は Oracle の ON DELETE SET NULL 句をサポートしていません。この句は、親テーブルのレコードが削除されたときに、子テーブル内のすべての依存値を NULL に設定するよう Oracle に指示します。
CHECK OPTION を除き、VIEWS に対する制約はサポートされていません。
PostgreSQL は、制約の無効化をサポートしていません。PostgreSQL は、ALTER TABLE ステートメントを使用して新しい外部キーまたはチェック制約が追加された場合に、NOT VALID オプションをサポートします。このオプションを使用すると、子テーブルの既存レコードの参照整合性チェックを省略するよう PostgreSQL に指示します。

次の表に、Oracle の制約タイプと PostgreSQL の制約タイプの主な違いを示します。

Oracle の制約タイプ	Cloud SQL for PostgreSQL のサポート	Cloud SQL for PostgreSQL の同等機能
`PRIMARY KEY`	○	`PRIMARY KEY`
`FOREIGN KEY`	○	Oracle と同じ ANSI SQL 構文を使用します。 `FOREIGN KEY` 親レコードを削除するケースを処理する場合は、`ON DELETE` 句を使用します。PostgreSQL では、データが親テーブルから削除され、子テーブルが `FOREIGN KEY` 制約によって参照されるケースを処理する場合、以下の 3 つの方法があります。 `ON DELETE CASCADE` `ON DELETE RESTRICT` `ON DELETE NO ACTION` PostgreSQL は Oracle の `ON DELETE SET NULL` 句をサポートしていません。 `FOREIGN KEY` 親レコードを更新するケースを処理する場合は、`ON UPDATE` 句を使用します。 PostgreSQL では、以下の 3 つの方法で `FOREIGN KEY` 制約更新イベントを処理できます。 `ON UPDATE CASCADE` `ON UPDATE RESTRICT` `ON UPDATE NO ACTION` PostgreSQL は、外部キー制約の参照列にインデックスを自動作成しません。
`UNIQUE`	○	デフォルトで `UNIQUE` インデックスを作成します。
`CHECK`	○	`CHECK`
`NOT NULL`	○	`NOT NULL`
`REF`	×	サポートされていません。
`DEFERRABLE/NOT DEFERRABLE`	○	`DEFERRABLE/NOT DEFERRABLE`
`INITIALLY IMMEDIATE`	はい	`INITIALLY IMMEDIATE`
`INITIALLY DEFERRED`	○	`INITIALLY DEFERRED`
`VALIDATE/NO VALIDATE`	×	サポートされていません。
`ENABLE/DISABLE`	×	デフォルトで有効。`ALTER TABLE` ステートメントを使用して新しい外部キーまたはチェック制約をテーブルに追加する場合は、`NOT VALID` オプションを使用して、既存のレコードの参照整合性チェックを省略します。
ビューに対する制約	×	`VIEW WITH CHECK OPTION` 以外はサポートされていません。
制約メタデータ	Oracle	`DBA_CONSTRAINTS`
	PostgreSQL	`INFORMATION_SCHEMA.TABLE_CONSTRAINTS`

仮想列と生成された列

Oracle の仮想列は他の列の計算結果に基づき決まります。これらは通常の列として表示されますが、値は Oracle データベースエンジンによってその場で計算され、データベースには格納されません。仮想列は、制約、インデックス、テーブルのパーティショニング、外部キーで使用できますが、データ操作言語（DML）で操作することはできません。

PostgreSQL の生成列は、機能の点で Oracle の仮想列と同等です。ただし、Oracle とは異なり、PostgreSQL で生成される列は保存されます。また、生成される列ごとにデータ型を指定する必要があります。つまり、これらの列は、通常の列と同様にストレージを占有します。

Oracle の仮想列の例:

SQL> CREATE TABLE PRODUCTS (
        PRODUCT_ID     INT PRIMARY KEY,
        PRODUCT_TYPE   VARCHAR2(100) NOT NULL,
        PRODUCT_PRICE  NUMBER(6,2) NOT NULL,
        PRICE_WITH_TAX AS (ROUND(PRODUCT_PRICE * 1.01, 2))
);

SQL> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE)
     VALUES(1, 'A', 99.99);

SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_TYPE         PRODUCT_PRICE PRICE_WITH_TAX
---------- -------------------- ------------- --------------
         1 A                            99.99         100.99

対応する PostgreSQL の例:

postgres=> CREATE TABLE PRODUCTS (
postgres(>         PRODUCT_ID     INT PRIMARY KEY,
postgres(>         PRODUCT_TYPE   VARCHAR(100) NOT NULL,
postgres(>         PRODUCT_PRICE  NUMERIC(6,2) NOT NULL,
postgres(>         PRICE_WITH_TAX NUMERIC GENERATED ALWAYS AS (ROUND(PRODUCT_PRICE * 1.01, 2)) STORED
postgres(> );

postgres=> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99);

postgres=> SELECT * FROM PRODUCTS;

 product_id | product_type | product_price | price_with_tax
------------+--------------+---------------+----------------
          1 | A            |         99.99 |         100.99
(1 row)

テーブルのインデックス

Oracle と PostgreSQL にはさまざまなインデックス作成アルゴリズムと、さまざまなアプリケーションに使用できる各種のインデックスが用意されています。PostgreSQL で使用可能なインデックスアルゴリズムは次のとおりです。

インデックスアルゴリズム	説明
B ツリー	PostgreSQL のデフォルトのインデックスタイプ。等価クエリと範囲クエリを高速化するために使用されます。すべてのプリミティブデータ型をサポートし、`NULL` 値の取得に使用できます。デフォルトでは、インデックス値は昇順になっていますが、降順で並べ替えることもできます。
ハッシュ	等式検索を高速化するために使用されます。 B ツリーインデックスよりも効率的ですが、等式検索の処理に限定されます。
GIN	反転ツリーインデックス配列やテキストなどの複数の成分値を含む列を扱う場合は、B ツリーインデックスよりも効率的です。
GiST	単一の種類のインデックスではなく、通常の B ツリーインデックスより多くの比較演算子をサポートできるインデックスを定義するインフラストラクチャ。「最近傍」探索の最適化が必要な場合のジオメトリデータに役立ちます。
SP-GiST	SP-GiST は、GiST と同様に、ユーザー定義のインデックス戦略のためのインフラストラクチャです。四分木など、非平衡の幅広い種類のデータ構造を許可します。 Cloud SQL for PostgreSQL では使用できません。
BRIN	ブロック範囲インデックス。テーブルの物理ブロック範囲の概要を格納します。並べ替え順が線形の列の場合。大規模なテーブルでの範囲ルックアップに便利です。

次の表に、Oracle と PostgreSQL のインデックスタイプの比較を示します。

Oracle のインデックス	説明	PostgreSQL でのサポート	PostgreSQL 対応
ビットマップインデックス	各インデックスキーのビットマップを保存します。OLAP ワークロードのデータ取得に最適です。	×	なし
B ツリーインデックス	さまざまなワークロードに適した最も一般的なインデックスタイプ。`ASC\|DESC` で並べ替えを構成できます。	○	B ツリーインデックス
複合インデックス	データ取得のパフォーマンスを向上させるため、2 つ以上の列で作成します。インデックス内の列の順序によってアクセスパスが決まります。	○	複数列のインデックス複数列のインデックスの作成時に、最大 32 列を指定できます。
関数ベースのインデックス	テーブル列の値に適用された関数の出力を保存します。	○	式のインデックス
一意のインデックス	列単位でインデックス登録された値に `UNIQUE` 制約を適用する B ツリーのインデックス。	○	一意のインデックス
アプリケーションドメインインデックス	音声 / 動画データ、LOB データ、その他の非テキスト型など、非リレーショナルデータのインデックス登録に適しています。	×	なし
非表示のインデックス	オプティマイザーの決定に影響を与えずにインデックスを管理、維持、テストできる Oracle の機能。	×	代わりのソリューションとして、進行中のアクティビティに影響を与えずに、テスト用のリードレプリカに追加のインデックスを作成できます。
インデックスで整理されたテーブル	テーブルとインデックスレベルでのデータの保存方法を制御するインデックス。	×	PostgreSQL はインデックス構成テーブルをサポートしていません。`CLUSTER` ステートメントは、指定されたインデックスに従ってテーブルストレージを整理するように PostgreSQL に指示します。これは Oracle のインデックス構成テーブルと同様の役割を果たします。ただし、クラスタリングは 1 回限りのオペレーションであり、PostgreSQL はそれ以降の更新時にテーブルの構造を維持しません。手動による定期的なクラスタリングが必要です。
ローカルインデックスとグローバルインデックス	Oracle データベース内のパーティション分割テーブルのインデックス作成に使用されます。各インデックスは、`LOCAL` または `GLOBAL` として定義されます。	×	PostgreSQL のパーティション処理インデックスは、Oracle のローカルインデックスと同じ機能を持ちます（つまり、インデックスはパーティションレベルで定義され、グローバルレベルはサポートされません）。
パーティション分割テーブルの部分的なインデックス（Oracle 12c）	テーブルのパーティションのサブセットにインデックスを作成します。`LOCAL` と `GLOBAL` がサポートされます。	○	PostgreSQL のパーティショニングは、子テーブルを親テーブルに接続することによって機能します。インデックスは子テーブルのサブセットにのみ作成できます。
`CREATE/DROP INDEX`	インデックスの作成と削除に使用されるコマンド。	○	PostgreSQL は、`CREATE INDEX` コマンドをサポートします。`ALTER TABLE tableName ADD INDEX indexName columnName` もサポートしています。
`ALTER INDEX ... REBUILD`	インデックスを再構築します。再構築により、インデックス登録されたテーブルに排他的ロックが発生することがあります。	異なる構文が必要	PostgreSQL では、`REINDEX` ステートメントを使用してインデックスを再構築できます。この処理が完了するまでテーブルはロックされ、読み取りのみが許可されます。
`ALTER INDEX ... REBUILD ONLINE`	テーブルに排他的なロックを作成せずにインデックスを再構築します。	異なる構文が必要	PostgreSQL は、`REINDEX TABLE CONCURRENTLY` ステートメントを使用した同時インデックスの再構築をサポートしています。このモードでは、PostgreSQL がロックを最小限に保ちながらインデックスの再作成を試みますが、その分、再構築にかかる時間とリソースが増える可能性があります。
インデックス圧縮	インデックスの物理サイズを減らす機能。	×	なし
テーブルスペースへのインデックスの割り当て	ディスク I/O のボトルネックを減らすため、テーブルデータとは別のディスクに保存できるインデックステーブルスペースを作成します。	×	PostgreSQL ではユーザー定義のテーブルスペースにインデックスを作成できますが、Cloud SQL for PostgreSQL にはテーブルスペースを作成できません。また、インデックスは、デフォルトのテーブルスペースに作成する必要があります。
インデックスメタデータ（テーブル / ビュー）	Oracle	`DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS`
	PostgreSQL	`pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class`

インデックス変換に関する考慮事項

ほとんどの場合、Oracle のインデックスは PostgreSQL の B ツリーインデックスに簡単に変換できます。これは、このタイプのインデックスが、最も一般的に使用されているインデックスタイプであるためです。Oracle データベースと同様に、テーブルの PRIMARY KEY フィールドにインデックスが自動作成されます。同様に、UNIQUE 制約のあるフィールドでは、UNIQUE インデックスが自動作成されます。また、標準の CREATE INDEX ステートメントを使用してセカンダリインデックスが作成されます。

次の例は、複数のインデックス付きフィールドを持つ Oracle テーブルを PostgreSQL に変換する方法を示しています。

SQL> CREATE TABLE ORA_IDX_TO_PG (
        col1 INT PRIMARY KEY,
        col2 VARCHAR2(60),
        col3 DATE,
        col4 CLOB,
        col5 VARCHAR2(20)
      );

-- Single-field index
SQL> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index
SQL> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index
SQL> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(col3);

-- Function-based index
SQL> CREATE INDEX idx_func_col3 ON
        ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB index
SQL> CREATE INDEX idx_col4 ON
       ora_idx_to_pg(col4) INDEXTYPE IS CTXSYS.CONTEXT;

-- Invisible index
SQL> CREATE INDEX idx_col5_inv ON
        ora_idx_to_pg(col5) INVISIBLE;

-- Drop index
SQL> DROP INDEX idx_col5_inv;

postgres=> CREATE TABLE ORA_IDX_TO_PG (
postgres(> col1 INT PRIMARY KEY,
postgres(> col2 VARCHAR(60),
postgres(> col3 DATE,
postgres(> col4 TEXT,
postgres(> col5 VARCHAR(20)
postgres(> );

-- Single index (supported)
postgres=> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index (supported)
postgres=> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index (supported)
postgres=> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(COL3);

-- Function-based index (supported)
postgres=> CREATE INDEX idx_func_col3 ON
postgres->         ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB (Supported, but requires different syntax. See Full Text Search for details)
postgres=> CREATE INDEX idx_col4 ON ora_idx_to_pg
postgres->         USING GIN (to_tsvector('english', col4));

-- Invisible index (not supported)
-- Optional - create the index as a B-tree index
postgres=> CREATE INDEX idx_col5 ON ora_idx_to_pg(col5);

-- Drop index
postgres=> DROP INDEX idx_col2;

SQL> SELECT ui.table_name,
            ui.index_name,
            ui.index_type,
            ic.column_name
     FROM user_indexes ui JOIN user_ind_columns ic
     ON ui.index_name = ic.index_name
     WHERE ui.table_name = 'ORA_IDX_TO_PG'
     ORDER BY 4;

postgres=> select distinct
postgres->     t.relname as table_name,
postgres->     i.relname as index_name,
postgres-> pg_get_indexdef(ix.indexrelid) index_definition
postgres-> from
postgres->     pg_class t,
postgres->     pg_class i,
postgres->     pg_index ix
postgres-> where
postgres->     t.oid = ix.indrelid
postgres->     and i.oid = ix.indexrelid
postgres->     and t.relname = 'ora_idx_to_pg'
postgres-> order by
postgres->     t.relname,
postgres->     i.relname;

-- OR Use psql \d command:
postgres=> \d ora_idx_to_pg
                  Table "public.ora_idx_to_pg"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col1   | integer               |           | not null |
 col2   | character varying(60) |           |          |
 col3   | date                  |           |          |
 col4   | text                  |           |          |
 col5   | character varying(20) |           |          |
Indexes:
    "ora_idx_to_pg_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)
    "idx_col4" gin (to_tsvector('english'::regconfig, col4))
    "idx_col5" btree (col5)
    "idx_cols3_2" btree (col3 DESC, col2)
    "idx_func_col3" btree (date_part('month'::text, col3))

postgres=>

テーブルのパーティショニング

Oracle と PostgreSQL のいずれも、大きなテーブルを分割するためのパーティショニング機能を備えています。これは、各部分に行の水平サブセットが含まれる小さな部分にテーブルを分割することで実現できます。パーティション分割テーブルは親テーブルと呼ばれ、その行がパーティションに物理的に格納されます。Oracle のパーティションタイプの一部は PostgreSQL ではサポートされていませんが、PostgreSQL は最も一般的なパーティションタイプをサポートしています。

以下のセクションでは、PostgreSQL でサポートされているパーティションタイプについて説明し、そのタイプに対応するパーティションの作成方法を例示します。

RANGE パーティショニング

このタイプのパーティションは、任意の範囲内の列の値に基づいてパーティションに行を割り当てます。各パーティションには、パーティショニング式の値が特定の範囲内にある行が含まれます。範囲がパーティション間で重複しないことに注意してください。

例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (store_id);

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (MINVALUE) TO (6);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (6) TO (11);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES FROM (11) TO (16);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES FROM (16) TO (21);

LIST パーティショニング

RANGE パーティショニングと同様に、LIST パーティショニングでは、事前に定義された値のセットに含まれる列の値に基づいてパーティションに行が割り当てられます。各パーティションに表示されるキーの値は、LIST パーティションに明示的に表示されます。

例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY LIST (store_id);

CREATE TABLE employees_pNorth PARTITION OF employees
 FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
 FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
 FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
 FOR VALUES IN (7,8,15);

HASH パーティショニング

HASH パーティショニングは、すべてのパーティションにデータを均等に分散する場合に最適です。列の値（またはハッシュされる列の値に基づく式）と行の値が、そのハッシュ値に対応するパーティションに割り当てられます。ハッシュ値はパーティションに一意に割り当てる必要があります。また、挿入されるすべての値は 1 つのパーティションに対応付ける必要があります。

例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY HASH (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

マルチレベルパーティショニング

マルチレベルパーティショニングは、単一のテーブルに対してパーティションの階層を作成する方法です。各パーティションは、さらにいくつかの異なるパーティションに分割されます。サブパーティションの数は、パーティションによって異なる場合があります。

例

CREATE TABLE sales (
 Saleid    INT,
 sale_date DATE,
 cust_code VARCHAR(15),
 income    DECIMAL(8,2))
PARTITION BY RANGE(date_part('year', sale_date));

CREATE TABLE sales_2019 PARTITION OF sales
 FOR VALUES FROM (2019) TO (2020)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2019_q1 PARTITION OF sales_2019
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2019_q2 PARTITION OF sales_2019
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2019_q3 PARTITION OF sales_2019
 FOR VALUES FROM (7) TO (10);
CREATE TABLE sales_2019_q4 PARTITION OF sales_2019
 FOR VALUES FROM (10) TO (13);

CREATE TABLE sales_2020 PARTITION OF sales
 FOR VALUES FROM (2020) TO (2021)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2020_q1 PARTITION OF sales_2020
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2020_q2 PARTITION OF sales_2020
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2020_h2 PARTITION OF sales_2020
 FOR VALUES FROM (7) TO (13);

パーティションの接続または切断

PostgreSQL では、親テーブルに対してパーティションの追加や削除を行うことができます。切断されたパーティションは、後で同じテーブルに再接続できます。また、パーティションを再接続するときに新しいパーティショニング条件を指定できます。これにより、パーティションの境界を調整できます。

例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (2010) TO (2015);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (2015) TO (2020);

-- changing partition boundaries
BEGIN TRANSACTION;
ALTER TABLE employees DETACH PARTITION employees_p1;
ALTER TABLE employees ATTACH PARTITION employees_p1 FOR VALUES FROM (2015) TO (2022);
COMMIT TRANSACTION;

次の表に、Oracle と Cloud SQL for PostgreSQL のパーティションタイプが同等で、変換が推奨される場所を示します。

Oracle のパーティションタイプ	PostgreSQL でのサポート	PostgreSQL の実装
`RANGE` パーティション	○	`PARTITION BY RANGE`
`LIST` パーティション	○	`PARTITION BY LIST`
`HASH` パーティション	○	`PARTITION BY HASH`
`SUB-PARTITIONING`	○	マルチレベルパーティショニング
インターバルパーティション	×	非対応
パーティションアドバイザ	×	非対応
プリファレンスパーティショニング	×	非対応
仮想列ベースのパーティショニング	×	回避策として、仮想列の式を使用して直接パーティショニングを行うことを検討してください。 `CREATE TABLE users ( id INT, username VARCHAR(20), first_letter VARCHAR(1) GENERATED ALWAYS AS ( UPPER(SUBSTR(TRIM(username), 1, 1)) ) STORED ) PARTITION BY LIST (UPPER(SUBSTR(TRIM(username), 1, 1)));`
自動リストパーティショニング	×	非対応
分割パーティション	×	回避策として、テーブルパーティションの切断または接続を行い、パーティションの境界を調整することを検討してください。
交換パーティション	○	`DETACH / ATTACH PARTITION`
マルチタイプのパーティショニング（複合パーティショニング）	○	マルチレベルパーティショニング
パーティションメタデータ	Oracle	`DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS`
	PostgreSQL	`pg_catalog.pg_class pg_catalog.pg_partitioned_table`

次の例は、両方のプラットフォームでテーブルパーティションを作成する方法を並べて比較したものです。なお、PostgreSQL では、CREATE TABLE コマンドの PARTITIONS 句でテーブルスペースを参照できません。

Oracle の実装

CREATE TABLE employees ( empid NUMBER, fname VARCHAR2(30), lname VARCHAR2(30), hired DATE, separated DATE, job_code NUMBER, store_id NUMBER) PARTITION BY LIST (store_id) ( PARTITION employees_pNorth VALUES (3,5,6) TABLESPACE users, PARTITION employees_pEast VALUES (1,2,10) TABLESPACE users, PARTITION employees_pWest VALUES (4,12,13) TABLESPACE users, PARTITION employees_pCnrl VALUES (7,8,15) TABLESPACE users );

PostgreSQL の実装

CREATE TABLE employees ( empid INT, fname VARCHAR(30), lname VARCHAR(30), hired DATE, separated DATE, job_code INT, store_id INT) PARTITION BY LIST (store_id); CREATE TABLE employees_pNorth PARTITION OF employees FOR VALUES IN (3,5,6); CREATE TABLE employees_pEast PARTITION OF employees FOR VALUES IN (1,2,10); CREATE TABLE employees_pWest PARTITION OF employees FOR VALUES IN (4,12,13); CREATE TABLE employees_pCnrl PARTITION OF employees FOR VALUES IN (7,8,15);

一時テーブル

Oracle データベースでは、一時テーブルは GLOBAL TEMPORARY TABLES と呼ばれ、PostgreSQL では単に一時テーブルと呼ばれます。一時テーブルの基本的な機能は、どちらのプラットフォームでも変わりありません。ただし、次のような大きな違いがあります。

Oracle では、データベースの再起動後も繰り返し使用できるように一時テーブルの構造を保存しますが、PostgreSQL ではセッションの間のみ一時テーブルを保存します。
Oracle データベースの一時テーブルには、適切な権限を持つさまざまなユーザーがアクセスできますが、PostgreSQL の一時テーブルには、そのテーブルが作成されたセッション中にのみアクセスできます。ただし、一時テーブルがスキーマ修飾名で参照されている場合はこの限りではありません。
Oracle データベースでは、GLOBAL と LOCAL の一時テーブルを区別して、テーブルのコンテンツがグローバルかセッション固有かを指定します。PostgreSQL では、適合性の理由から GLOBAL キーワードと LOCAL キーワードがサポートされていますが、これらはデータの可視性には影響しません。
一時テーブルの作成時に ON COMMIT 句を省略した場合、Oracle データベースのデフォルトの動作は ON COMMIT DELETE ROWS です。Oracle では commit するたびに一時テーブルが破棄されます。PostgreSQL のデフォルト動作では、commit するたびに一時テーブルの行が保持されます。

次の表に、Oracle と Cloud SQL for PostgreSQL の一時テーブルの違いを示します。

一時テーブルの機能	Oracle の実装	PostgreSQL の実装
構文	`CREATE GLOBAL TEMPORARY TABLE`	`CREATE TEMPORARY TABLE`
アクセス性	複数のセッションからアクセス可能	スキーマ修飾名で参照される場合を除き、作成者のセッションからのみアクセス可能
インデックスのサポート	○	○
外部キーのサポート	○	○
DDL の保持	○	×
`ON COMMIT` のデフォルトのアクション	レコードを削除	レコードを保持
`ON COMMIT PRESERVE ROWS`	○	はい
`ON COMMIT DELETE ROWS`	はい	○
`ON COMMIT DROP`	いいえ	○
`ALTER TABLE` サポート	○	○
統計情報の収集	`DBMS_STATS.GATHER_TABLE_STATS`	`ANALYZE`
Oracle 12c `GLOBAL_TEMP_` `TABLE_STATS`	`DBMS_STATS.SET_TABLE_PREFS`	`ANALYZE`

未使用の列

特定の列を UNUSED としてマークする Oracle の機能は、多くの場合、列データを物理的に削除せずにテーブルから列を削除する場合に使用されます。これは、大規模なテーブルから列を削除するときに発生する可能性のある高負荷を回避するためです。

PostgreSQL では、大きな列を削除しても、物理ストレージから列データが削除されることがないため、大きなテーブルでも高速な演算処理が可能です。Oracle データベースのように列を UNUSED としてマークする必要はありません。ドロップされた列が占めているスペースは、新しい DML ステートメントか、後続の VACUUM オペレーションで回収されます。

読み取り専用テーブル

読み取り専用テーブルは、ALTER TABLE コマンドでテーブルを読み取り専用にマークする Oracle の機能です。Oracle 12c R2 では、パーティションとサブパーティションを持つテーブルでもこの機能を使用できます。PostgreSQL に同等の機能はありませんが、代わりに次の 2 つの回避策があります。

テーブルに対する SELECT 権限を特定のユーザーに付与する。これにより、テーブルオーナーがこれらのテーブルに DML 操作を実行できなくなるわけではありません。
Cloud SQL for PostgreSQL のリードレプリカを作成し、読み取り専用テーブルのレプリカテーブルにユーザーを転送します。このソリューションでは、リードレプリカインスタンスを既存の Cloud SQL for PostgreSQL インスタンスに追加する必要があります。

DML ステートメントで例外が発生するデータベーストリガーを作成します。次に例を示します。

-- Define trigger function
CREATE OR REPLACE FUNCTION raise_readonly_exception() RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Table is readonly!';
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

-- Fire trigger when DML statements is executed on read only table
CREATE TRIGGER myTable_readonly_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON myTable FOR EACH STATEMENT
EXECUTE PROCEDURE raise_readonly_exception();

-- Testing the trigger
postgres=> INSERT INTO myTable (id) VALUES (1);
ERROR:  Table is readonly!
CONTEXT:  PL/pgSQL function raise_readonly_exception() line 3 at RAISE
postgres=>

文字セット

Oracle と PostgreSQL はどちらも、シングルバイト言語とマルチバイト言語の両方に対するサポートをはじめとして、さまざまな文字セット、照合順序、Unicode に対応しています。また、同じインスタンスにある PostgreSQL データベースは、それぞれ異なる文字セットで構成できます。PostgreSQL でサポートされている文字セットのリストをご覧ください。

Oracle Database では、文字セットをデータベースレベル（Oracle 12g R1 以前）またはプラグイン対応データベースレベル（Oracle 12g R2 以降）で指定します。PostgreSQL では、新しい Cloud SQL for PostgreSQL インスタンスが作成されるときにデフォルトの文字セットが指定されます。そのインスタンス内に作成された各データベースは、異なる文字セットを使用して作成できます。並べ替え順と文字分類は、テーブル列ごとに指定できます。

例

-- Create a database using UTF-8 character set and ja_JP.UTF collation
postgres=> CREATE DATABASE jpdb WITH ENCODING 'UTF8' LC_COLLATE='ja_JP.UTF8' LC_CTYPE='ja_JP.UTF8' TEMPLATE=template0;

-- Query the character set and collation settings of all databases
postgres=> SELECT datname AS DATABASE_NAME, datcollate AS LC_COLLATE, datctype AS LC_CTYPE from pg_database;
 database_name | lc_collate |  lc_ctype
---------------+------------+------------
 cloudsqladmin | en_US.UTF8 | en_US.UTF8
 template0     | en_US.UTF8 | en_US.UTF8
 template1     | en_US.UTF8 | en_US.UTF8
 postgres      | en_US.UTF8 | en_US.UTF8
 jpdb          | ja_JP.UTF8 | ja_JP.UTF8
(5 rows)

-- Alternatively, use psql \l command to query the database settings
postgres=> \l
                                                List of databases
     Name      |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
---------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 postgres      | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
               |                   |          |            |            | testuser=CTc/cloudsqlsuperuser
 template0     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
               |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1     | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
-- Specifying column level collation
postgres=> CREATE TABLE test1 (
postgres(>     a text COLLATE "de_DE",
postgres(>     b text COLLATE "es_ES"
postgres(> );

ビュー

PostgreSQL は、単純なビューと複雑なビューの両方をサポートします。ビュー作成オプションについては、Oracle と PostgreSQL の間にいくつかの違いがあります。次の表に、この違いを示します。

Oracle のビュー機能	説明	Cloud SQL for PostgreSQL のサポート	変換に関する考慮事項
`FORCE`	ソーステーブルまたはビューが存在するかどうかを確認せずにビューを作成します。	×	対応するオプションはありません。
`CREATE OR REPLACE`	存在しないビューを作成するか、既存のビューを上書きします。	○	PostgreSQL は、ビューの `CREATE OR REPLACE` コマンドをサポートしています。
`WITH CHECK OPTION`	ビューに DML 操作を実行する際の強制レベルを指定します。	○	デフォルトは `CASCADED` です。これにより、参照されたビューも評価されます。 `LOCAL` キーワードにより、現在のビューのみが評価されます。
`WITH READ-ONLY`	ビューに対する読み取り操作のみを許可します。DML 操作は禁止されています。	×	これを回避するには、ビューに対する SELECT 権限をすべてのユーザーに付与します。
`VISIBLE \| INVISIBLE`（Oracle 12c）	ビューに基づく列をユーザーに表示するか非表示にするかを指定します。	×	必要な列のみで `VIEW` を作成します。

次の変換例は、ビューの Oracle から Cloud SQL PostgreSQL への変換を示しています。

-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
     SET salary=salary+1000;

postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres->        FIRST_NAME,
postgres->        LAST_NAME,
postgres->        SALARY,
postgres->        DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;

-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;

ERROR:  new row violates check option for view "vw_emp_dept100"
DETAIL:  Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).

表示権限の管理

ビューのオーナーには、ビューを作成するベーステーブルに対する権限を付与する必要があります。ビューのユーザーには、ビューに対する適切な SELECT 権限を割り当てる必要があります。また、ビューで DML 操作を行うには、ビューに対する適切な INSERT 権限、UPDATE 権限、DELETE 権限が必要です。いずれの場合も、ユーザーには、基になるテーブルに対する権限は必要ありません。

次のステップ

PostgreSQL ユーザーアカウントの詳細を確認する。
Google Cloud に関するリファレンスアーキテクチャ、図、ベストプラクティスを確認する。Cloud アーキテクチャセンターをご覧ください。