Migrer des utilisateurs et des schémas de Oracle® Database vers Cloud SQL pour PostgreSQL

Ce document fait partie d'une série qui fournit des informations clés et des conseils pour planifier et effectuer des migrations de bases de données Oracle® 11g/12c vers Cloud SQL pour PostgreSQL version 12. Ce document décrit les principales différences entre Oracle® Database et Cloud SQL pour MySQL concernant la création d'utilisateurs, de schémas, de tables, d'index et de vues.

Outre la section d'introduction à la configuration, la série inclut les éléments suivants :

Migrer des utilisateurs Oracle vers Cloud SQL pour PostgreSQL : terminologie et fonctionnalités
Migrer des utilisateurs Oracle vers Cloud SQL pour PostgreSQL : types de données, utilisateurs et tables
Migrer des utilisateurs Oracle vers Cloud SQL pour PostgreSQL : requêtes, procédures stockées, fonctions et déclencheurs
Migrer des utilisateurs Oracle vers Cloud SQL pour PostgreSQL : sécurité, opérations, surveillance et journalisation
Migrer des utilisateurs et des schémas de Oracle Database vers Cloud SQL pour PostgreSQL (ce document)

Différences terminologiques entre Oracle et Cloud SQL pour PostgreSQL

Oracle et Cloud SQL pour PostgreSQL présentent des architectures et une terminologie différentes pour les instances, les bases de données, les utilisateurs et les schémas. Pour obtenir un résumé de ces différences, consultez la partie Terminologie de cette série.

Exporter des configurations Oracle

Lorsque vous planifiez une migration vers Cloud SQL pour PostgreSQL, la première étape consiste à examiner les paramètres existants dans la base de données Oracle source. Les paramètres concernant l'allocation de mémoire, le jeu de caractères et les paramètres de stockage sont particulièrement utiles, car ils peuvent éclairer la configuration initiale et le dimensionnement de l'environnement cible Cloud SQL pour PostgreSQL. Il existe plusieurs méthodes pour extraire des paramètres Oracle. En voici quelques exemples courants :

Les rapports AWR (Automatic Workload Repository) contiennent des données sur l'allocation des ressources (processeur, RAM), la configuration des paramètres de l'instance et le nombre maximal de sessions actives.
DBA_HIST, V$OSSTAT et V$LICENSE pour les détails d'utilisation du processeur.
Vue V$PARAMETER pour les paramètres de configuration de base de données.
Vue V$NLS_PARAMETERS pour les paramètres de langage de base de données.
Vue DBA_DATA_FILES pour calculer la taille de l'espace de stockage de base de données.
Le fichier SPFILE Oracle pour les configurations d'instance de base de données.
Les outils de planification de tâches (par exemple, crontab) pour identifier les sauvegardes quotidiennes ou les intervalles de maintenance qui doivent être pris en considération.

Importer et configurer des utilisateurs dans Cloud SQL pour PostgreSQL

En règle générale, chaque schéma Oracle doit être créé comme son propre schéma dans PostgreSQL. Dans une base de données Oracle, utilisateur est synonyme de schéma. Cela signifie qu'un schéma est créé lorsque vous créez un utilisateur. Il existe toujours une relation 1:1 entre les utilisateurs et les schémas. Dans PostgreSQL, les utilisateurs et les schémas sont créés séparément. Vous pouvez créer un utilisateur sans créer de schéma correspondant. Pour conserver la même structure d'utilisateurs ou de schéma Oracle dans PostgreSQL, vous pouvez créer un schéma pour chaque utilisateur.

Le tableau suivant illustre des exemples de conversion :

Type d'action	Type de base de données	Comparaison de commandes
Créer un utilisateur et un schéma	`Oracle`	`CREATE USER username IDENTIFIED BY password;`
	`PostgreSQL`	L'utilisateur et le schéma sont des concepts distincts dans PostgreSQL. Vous devez donc utiliser deux instructions `CREATE` distinctes. `CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;`
Attribution de rôles	`Oracle`	`GRANT CONNECT TO username;`
	`PostgreSQL`	`GRANT pg_monitor TO username;`
Octroi de droits	`Oracle`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
	`PostgreSQL`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
Révoquer des droits	`Oracle`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
	`PostgreSQL`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
Accorder des droits DBA/super-utilisateur	`Oracle`	`GRANT DBA TO username;`
	`PostgreSQL`	`GRANT cloudsqlsuperuser TO username;`
Supprimer un utilisateur	`Oracle`	`DROP USER username CASCADE;`
	`PostgreSQL`	L'utilisateur et le schéma sont des concepts distincts dans PostgreSQL. Vous devez donc utiliser deux instructions `DROP` distinctes. `DROP USER username; DROP SCHEMA schema_name CASCADE;`
Métadonnées des utilisateurs	`Oracle`	`DBA_USERS`
	`PostgreSQL`	`pg_catalog.pg_user`
Métadonnées des autorisations	`Oracle`	`DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS`
	`PostgreSQL`	`pg_catalog.pg_roles`
Chaîne de connexion CLI	`Oracle`	`sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid`
	`PostgreSQL`	Sans invite de mot de passe : `PGPASSWORD=password psql -h hostname -U username -d database_name` Avec invite de mot de passe : `psql -h hostname -U username -W -d database_name`

Utilisateurs de bases de données Oracle 12c :

Il existe deux types d'utilisateurs dans Oracle 12c : les utilisateurs courants et les utilisateurs locaux. Les utilisateurs courants sont créés dans la CDB racine, y compris les PDB. Ils sont identifiés par le préfixe C## dans leur nom d'utilisateur. Les utilisateurs locaux ne sont créés que dans une PDB spécifique. Différents utilisateurs de base de données ayant des noms d'utilisateur identiques peuvent être créés dans plusieurs PDB. Lors de la migration d'Oracle 12c vers PostgreSQL, modifiez les utilisateurs et les autorisations pour les adapter à l'architecture de PostgreSQL. Voici deux exemples courants pour illustrer ces différences :

# 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)

Gérer des utilisateurs via la console Google Cloud

Pour afficher les utilisateurs actuellement configurés pour Cloud SQL pour PostgreSQL, accédez à la page suivante dans la console Google Cloud :

Google Cloud > Stockage > SQL > Instance > Utilisateurs

Capture d'écran de la page "Utilisateurs"

Importer des définitions de table et de vue

Oracle et PostgreSQL diffèrent en termes de sensibilité à la casse. Les noms Oracle ne sont pas sensibles à la casse. Les noms PostgreSQL ne sont pas sensibles à la casse, sauf lorsqu'ils sont entourés de guillemets doubles. De nombreux outils d'exportation de schéma et de génération SQL pour Oracle, tels que DBMS_METADATA.GET_DDL, ajoutent automatiquement des guillemets doubles aux noms d'objets. Ces guillemets peuvent entraîner toutes sortes de problèmes après la migration. Nous vous recommandons de supprimer tous les guillemets entourant les noms d'objet des instructions LDD (langage de définition de données) avant de créer les objets dans PostgreSQL.

Syntaxe de création de table

Lors de la conversion de tables d'Oracle vers des types de données PostgreSQL, la première étape consiste à extraire les instructions CREATE TABLE de la base de données source. L'exemple de requête suivant extrait le LDD pour la table des emplacements du schéma HR :

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

La sortie complète inclut des éléments de stockage, des index et des informations sur l'espace de table, qui ont été omis, car ces éléments supplémentaires ne sont pas acceptés par l'instruction CREATE TABLE PostgreSQL.

Une fois le LDD extrait, supprimez les guillemets autour des noms et effectuez la conversion de la table en fonction de la table de conversion des types de données Oracle vers PostgreSQL. Vérifiez chaque type de données de colonnes afin de déterminer s'il peut être converti tel quel. S'il n'est pas compatible, choisissez un type de données différent en fonction de la table de conversion. Par exemple, voici le LDD converti pour la table des emplacements.

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)

L'instruction CREATE TABLE AS SELECT (CTAS) permet de créer une table basée sur une table existante. Notez que seuls les noms de colonnes et les types de données de colonnes sont copiés, contrairement aux contraintes et aux index. PostgreSQL est compatible avec la norme ANSI SQL pour les fonctionnalités CTAS. Il est également compatible avec les instructions CTAS d'Oracle.

Colonnes invisibles Oracle 12c

PostgreSQL n'est pas compatible avec les colonnes invisibles. Pour contourner le problème, créez une vue ne contenant que les colonnes visibles.

Contraintes de table

Oracle fournit six types de contraintes de table pouvant être définis lors de la création de la table ou après à l'aide de la commande ALTER TABLE. Les types de contraintes Oracle sont PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL et REF. De plus, Oracle permet à l'utilisateur de contrôler l'état d'une contrainte via les options suivantes :

INITIALLY IMMEDIATE : vérifie la contrainte à la fin de chaque instruction SQL ultérieure (état par défaut).
DEFERRABLE/NOT DEFERRABLE : active l'utilisation de la clause SET CONSTRAINT dans les transactions ultérieures jusqu'à l'envoi d'une instruction COMMIT.
INITIALLY DEFERRED : vérifie la contrainte à la fin des transactions ultérieures.
VALIDATE/NO VALIDATE : vérifie (ou ne vérifie pas délibérément) les lignes nouvelles ou modifiées pour détecter des erreurs. Ces paramètres varient selon que la contrainte est ENABLED ou DISABLED.
ENABLED/DISABLED : indique si la contrainte doit être appliquée après la création (ENABLED par défaut).

PostgreSQL accepte également six types de contraintes de table : PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL et EXCLUDE. Cependant, il existe quelques différences notables entre les types de contraintes Oracle et PostgreSQL, en particulier :

PostgreSQL n'est pas compatible avec la contrainte REF d'Oracle.
PostgreSQL ne crée pas automatiquement d'index sur les colonnes de référence pour une contrainte de clé étrangère. Une instruction CREATE INDEX distincte sur les colonnes de référence est nécessaire si un index est requis.
PostgreSQL n'est pas compatible avec la clause ON DELETE SET NULL d'Oracle. Cette clause indique à Oracle de définir les valeurs dépendantes des tables enfants sur NULL lorsque l'enregistrement de la table parente est supprimé.
Les contraintes sur VIEWS ne sont pas acceptées, à l'exception de CHECK OPTION.
PostgreSQL n'est pas compatible avec les contraintes de désactivation. PostgreSQL accepte l'option NOT VALID lorsqu'une nouvelle clé étrangère ou une contrainte de vérification est ajoutée à l'aide d'une instruction ALTER TABLE. Cette option indique à PostgreSQL d'ignorer les vérifications d'intégrité référentielle sur les enregistrements existants de la table enfant.

Le tableau suivant récapitule les principales différences entre les types de contraintes Oracle et PostgreSQL :

Type de contrainte Oracle	Compatibilité avec Cloud SQL pour PostgreSQL	Équivalent Cloud SQL pour PostgreSQL
`PRIMARY KEY`	Oui	`PRIMARY KEY`
`FOREIGN KEY`	Oui	Utilise la même syntaxe SQL ANSI qu'Oracle. Utilise la clause `ON DELETE` pour gérer les cas de suppression d'enregistrements parents `FOREIGN KEY` : PostgreSQL propose trois options pour gérer les cas où les données sont supprimées de la table parente et une table enfant est référencée par une contrainte `FOREIGN KEY` : `ON DELETE CASCADE` `ON DELETE RESTRICT` `ON DELETE NO ACTION` PostgreSQL n'est pas compatible avec la clause `ON DELETE SET NULL` d'Oracle. Utilise la clause `ON UPDATE` pour gérer les cas de mise à jour des enregistrements parents `FOREIGN KEY`. PostgreSQL permet de gérer trois événements de mise à jour de contrainte `FOREIGN KEY` : `ON UPDATE CASCADE` `ON UPDATE RESTRICT` `ON UPDATE NO ACTION` PostgreSQL ne crée pas automatiquement d'index sur les colonnes référençant une contrainte de clé étrangère.
`UNIQUE`	Oui	Crée un index `UNIQUE` par défaut.
`CHECK`	Oui	`CHECK`
`NOT NULL`	Oui	`NOT NULL`
`REF`	Non	Non compatible.
`DEFERRABLE/NOT DEFERRABLE`	Oui	`DEFERRABLE/NOT DEFERRABLE`
`INITIALLY IMMEDIATE`	Oui	`INITIALLY IMMEDIATE`
`INITIALLY DEFERRED`	Oui	`INITIALLY DEFERRED`
`VALIDATE/NO VALIDATE`	Non	Non compatible.
`ENABLE/DISABLE`	Non	Cette option est activée par défaut. Utilisez l'option `NOT VALID` lorsqu'une nouvelle contrainte de clé étrangère ou de vérification est ajoutée à la table à l'aide d'une instruction `ALTER TABLE` pour ignorer les vérifications de l'intégrité référentielle sur les enregistrements existants.
Contrainte sur des vues	Non	Non compatible, à l'exception de `VIEW WITH CHECK OPTION`.
Métadonnées des contraintes	Oracle	`DBA_CONSTRAINTS`
	PostgreSQL	`INFORMATION_SCHEMA.TABLE_CONSTRAINTS`

Colonnes virtuelles et générées

Les colonnes virtuelles d'Oracle sont basées sur les résultats des calculs d'autres colonnes. Elles apparaissent sous forme de colonnes standards, mais leurs valeurs sont dérivées d'un calcul à la volée par le moteur de base de données Oracle et ne sont pas stockées dans la base de données. Les colonnes virtuelles peuvent être utilisées avec des contraintes, des index, le partitionnement des tables et des clés étrangères, mais elles ne peuvent pas être gérées via des opérations LMD.

Les colonnes générées de PostgreSQL sont comparables aux colonnes virtuelles d'Oracle en termes de fonctionnalités. Cependant, contrairement à Oracle, les colonnes générées dans PostgreSQL sont stockées. Vous devez spécifier un type de données pour chaque colonne générée, ce qui signifie qu'elles occupent un espace de stockage comme s'il s'agissait de colonnes normales.

Exemple de colonne virtuelle dans 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

Exemple équivalent dans 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)

Index de table

Oracle et PostgreSQL fournissent divers algorithmes et types d'index pouvant être utilisés pour diverses applications. Voici une liste des algorithmes d'indexation disponibles dans PostgreSQL :

Algorithme d'index	Description
B-tree	Type d'index par défaut pour PostgreSQL, utilisé pour accélérer les requêtes d'égalité et de plage Il est compatible avec tous les types de données primitifs et peut être utilisé pour récupérer les valeurs `NULL`. Par défaut, les valeurs d'index sont triées par ordre croissant, mais peuvent également être configurées par ordre décroissant.
Hachage	Permet d'accélérer les recherches d'égalité. Plus efficace que l'index B-tree, mais limité à la gestion des recherches d'égalité
GIN	Index arborescents inversés Plus efficace que l'index B-tree pour les colonnes contenant plusieurs valeurs de composant, comme un tableau et du texte
GiST	Pas un seul genre d'index, mais une infrastructure pour définir des index pouvant accepter plus d'opérateurs de comparaison qu'un index B-tree normal Utile pour les données géométriques lors de l'optimisation des recherches "plus proches voisins"
SP-GiST	Semblable à GST, SP-GSTST est une infrastructure pour les stratégies d'indexation définies par l'utilisateur Autorise un large éventail de structures de données non équilibrées, telles que des quadtrees Non disponible dans Cloud SQL pour PostgreSQL
BRIN	Index de plages de blocs Stocke les résumés des plages de blocs physiques d'une table Pour les colonnes avec un ordre de tri linéaire Utile pour la recherche de plages sur des tables volumineuses

Le tableau suivant compare les types d'index entre Oracle et PostgreSQL :

Index Oracle	Description	Compatible avec PostgreSQL	Équivalent PostgreSQL
Index bitmap	Stocke un bitmap pour chaque clé d'index, particulièrement adapté pour permettre la récupération des données pour les charges de travail OLAP.	Non	ND
Index B-tree	Type d'index le plus courant, parfaitement adapté à diverses charges de travail et pouvant être configuré dans l'ordre `ASC\|DESC`.	Oui	Index B-tree
Index composite	Crée plus de deux colonnes pour améliorer les performances de récupération des données. L'ordre des colonnes dans l'index détermine le chemin d'accès.	Oui	Index à plusieurs colonnes Vous pouvez spécifier jusqu'à 32 colonnes lorsque vous créez un index à plusieurs colonnes.
Index basé sur une fonction	Stocke le résultat d'une fonction appliquée sur les valeurs d'une colonne de table.	Oui	Index sur les expressions
Index unique	Un index b-tree qui applique une contrainte `UNIQUE` sur les valeurs indexées par colonne.	Oui	Index unique
Index de domaine d'application	Adapté à l'indexation des données non relationnelles, telles que les données audio/vidéo, les données LOB et d'autres types non textuels.	Non	Non disponible
Index invisible	Fonctionnalité d'Oracle qui vous permet de gérer, d'entretenir et de tester les index sans affecter la prise de décision de l'optimiseur.	Non	Vous pouvez également créer un index supplémentaire sur une instance dupliquée avec accès en lecture à des fins de test sans que cela affecte l'activité en cours.
Table organisée par index	Type d'index qui contrôle physiquement la manière dont les données sont stockées au niveau de la table et de l'index.	Non	PostgreSQL n'est pas compatible avec les tables organisées en index. L'instruction `CLUSTER` indique à PostgreSQL d'organiser le stockage des tables en fonction d'un index spécifié. Elle a un objectif semblable à celui de la table organisée par index d'Oracle. Cependant, le clustering est une opération unique et PostgreSQL ne conserve pas la structure de la table lors des mises à jour ultérieures. Un clustering manuel périodique est nécessaire.
Index local et global	Permet d'indexer des tables partitionnées dans une base de données Oracle. Chaque index est défini comme `LOCAL` ou `GLOBAL`.	Non	Les index de fonctionnement des partitions PostgreSQL ont les mêmes fonctionnalités que les index locaux Oracle (c'est-à-dire que l'index est défini au niveau de la partition, le niveau global n'est pas compatible).
Index partiels pour les tables partitionnées (Oracle 12c)	Crée un index sur un sous-ensemble de partitions d'une table. Compatible avec `LOCAL` et `GLOBAL`.	Oui	Le partitionnement dans PostgreSQL fonctionne en associant des tables enfants dans une table parente. Il est possible de créer des index uniquement sur un sous-ensemble de tables enfants.
`CREATE/DROP INDEX`	Commande utilisée pour créer et supprimer un index.	Oui	PostgreSQL accepte la commande `CREATE INDEX`. Il est également compatible avec `ALTER TABLE tableName ADD INDEX indexName columnName`.
`ALTER INDEX ... REBUILD`	Recompile l'index, ce qui peut provoquer un verrouillage exclusif sur la table indexée.	Nécessite une syntaxe différente	PostgreSQL accepte les recompilations d'index à l'aide de l'instruction `REINDEX`. La table est verrouillée pour les écritures pendant cette opération, et seules les lectures sont autorisées.
`ALTER INDEX ... REBUILD ONLINE`	Recompile un index sans créer de verrou exclusif sur la table.	Nécessite une syntaxe différente	PostgreSQL accepte les recompilations d'index simultanées à l'aide de l'instruction `REINDEX TABLE CONCURRENTLY`. Dans ce mode, PostgreSQL tente de reconstruire les index à l'aide d'un verrouillage minimal, avec le compromis de prendre plus de temps et de ressources pour terminer la recompilation.
Compression de l'index	Fonctionnalité permettant de réduire la taille physique des index.	Non	Non disponible
Allouer un index à un espace de table	Crée un espace de table d'index pouvant être stocké sur un disque distinct des données de la table afin de réduire les goulots d'étranglement d'E/S disque.	Non	Bien que PostgreSQL permette de créer un index dans un espace de table défini par l'utilisateur, vous ne pouvez pas créer d'espaces de table dans Cloud SQL pour PostgreSQL, et l'index doit être créé dans un espace de table par défaut.
Métadonnées d'index (tables/vues)	Oracle	`DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS`
	PostgreSQL	`pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class`

Considérations relatives à la conversion d'index

Dans la plupart des cas, les index Oracle peuvent simplement être convertis en index B-tree de PostgreSQL, car ce type d'index est le plus couramment utilisé. Comme dans une base de données Oracle, un index est automatiquement créé sur les champs PRIMARY KEY d'une table. De même, un index UNIQUE est automatiquement créé sur les champs qui ont une contrainte UNIQUE. En outre, les index secondaires sont créés à l'aide de l'instruction CREATE INDEX standard.

L'exemple suivant montre comment une table Oracle comportant plusieurs champs indexés peut être convertie pour 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=>

Partitionnement de table

Oracle et PostgreSQL offrent tous deux des fonctionnalités de partitionnement pour fractionner des tables volumineuses. Cela consiste à segmenter physiquement une table en parties plus petites, où chaque partie contient un sous-ensemble horizontal des lignes. La table partitionnée est appelée table parente et ses lignes sont stockées physiquement dans ses partitions. Bien que tous les types de partition d'Oracle ne soient pas compatibles avec PostgreSQL, PostgreSQL accepte les types les plus courants.

Les sections suivantes décrivent les types de partition compatibles avec PostgreSQL, chacun d'eux étant illustré par un exemple de création de partitions qui correspondent à ce type.

Partitionnement RANGE

Ce type de partition attribue des lignes aux partitions en fonction des valeurs de colonne comprises dans une plage donnée. Chaque partition contient des lignes pour lesquelles la valeur de l'expression de partitionnement se situe dans une plage donnée. Il est important de noter que les plages ne se chevauchent pas entre les partitions.

Exemple

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

Partitionnement LIST

À l'instar du partitionnement RANGE, le partitionnement LIST attribue des lignes aux partitions en fonction des valeurs de colonne comprises dans un ensemble prédéfini de valeurs. Les valeurs de clé qui apparaissent dans chaque partition sont explicitement répertoriées pour les partitions LIST.

Exemple

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

Partitionnement HASH

Le partitionnement HASH est particulièrement adapté lorsque l'objectif est d'obtenir une distribution équitable des données entre toutes les partitions. Une valeur de colonne (ou expression basée sur une valeur de colonne à hacher) et la valeur de ligne sont attribuées à la partition correspondant à cette valeur de hachage. Les valeurs de hachage doivent être attribuées de manière unique aux partitions, et toutes les valeurs insérées doivent être mappées sur une seule partition.

Exemple

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

Partitionnement à plusieurs niveaux

Le partitionnement à plusieurs niveaux permet de créer une hiérarchie de partitions pour une seule table. Chaque partition est également divisée en un certain nombre de partitions différentes. Le nombre de sous-partitions peut varier d'une partition à l'autre.

Exemple

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

Associer ou dissocier des partitions

Dans PostgreSQL, des partitions peuvent être ajoutées ou supprimées de la table parente. Une partition dissociée peut être réassociée ultérieurement à la même table. De plus, de nouvelles conditions de partitionnement peuvent être spécifiées lors de la réassociation de la partition, ce qui permet d'ajuster les limites de la partition.

Exemple

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;

Le tableau suivant indique lorsque les types de partitions Oracle et Cloud SQL pour PostgreSQL sont équivalents et lorsqu'une conversion est recommandée :

Type de partition Oracle	Compatible avec PostgreSQL	Mise en œuvre PostgreSQL
Partitions `RANGE`	Oui	`PARTITION BY RANGE`
Partitions `LIST`	Oui	`PARTITION BY LIST`
Partitions `HASH`	Oui	`PARTITION BY HASH`
`SUB-PARTITIONING`	Oui	Partitionnement à plusieurs niveaux
Partitions d'intervalles	Non	Non disponible
Conseiller de partition	Non	Non disponible
Partitionnement des préférences	Non	Non disponible
Partitionnement basé sur des colonnes virtuelles	Non	Pour contourner ce problème, envisagez directement le partitionnement avec l'expression de colonne virtuelle : `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)));`
Partitionnement de liste automatique	Non	Non disponible
Diviser des partitions	Non	Pour contourner ce problème, envisagez de dissocier ou d'associer des partitions de table pour ajuster les limites de partition.
Échanger des partitions	Oui	`DETACH / ATTACH PARTITION`
Partitionnement multitype (partitionnement composite)	Oui	Partitionnement à plusieurs niveaux
Métadonnées des partitions	Oracle	`DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS`
	PostgreSQL	`pg_catalog.pg_class pg_catalog.pg_partitioned_table`

L'exemple suivant compare la création de partitions de tables sur les deux plates-formes. Notez que PostgreSQL n'est pas compatible avec le référencement d'un espace de table dans la clause PARTITIONS de la commande CREATE TABLE.

Mise en œuvre dans 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 );

Mise en œuvre 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);

Tables temporaires

Dans une base de données Oracle, les tables temporaires sont appelées GLOBAL TEMPORARY TABLES, tandis que dans PostgreSQL, elles sont simplement appelées tables temporaires. La fonctionnalité de base d'une table temporaire est identique sur les deux plates-formes. Il existe toutefois quelques différences notables :

Oracle stocke la structure de table temporaire pour une utilisation répétée même après un redémarrage de la base de données, tandis que PostgreSQL ne stocke la table temporaire que pendant la durée d'une session.
Une table temporaire dans une base de données Oracle est accessible par différents utilisateurs disposant des autorisations appropriées. En revanche, une table temporaire dans PostgreSQL n'est accessible que pendant la session dans laquelle elle a été créée, sauf si elle est référencée par des noms qualifiés de schéma.
Dans une base de données Oracle, il existe une distinction entre les tables temporaires GLOBAL et LOCAL qui spécifient si le contenu de la table est global ou spécifique à la session. Dans PostgreSQL, les mots clés GLOBAL et LOCAL sont acceptés pour des raisons de compatibilité, mais ils n'ont aucun effet sur la visibilité des données.
Si la clause ON COMMIT est omise lors de la création d'une table temporaire, le comportement par défaut dans Oracle est ON COMMIT DELETE ROWS, ce qui signifie qu'Oracle tronque la table temporaire après chaque commit. Dans PostgreSQL, en revanche, le comportement par défaut consiste à conserver les lignes de la table temporaire après chaque commit.

Le tableau suivant met en évidence les différences entre les tables temporaires Oracle et Cloud SQL pour PostgreSQL.

Fonctionnalité de table temporaire	Mise en œuvre dans Oracle	Mise en œuvre PostgreSQL
Syntaxe	`CREATE GLOBAL TEMPORARY TABLE`	`CREATE TEMPORARY TABLE`
Accessibilité	Accessible à partir de plusieurs sessions	Accessible à partir de la session du créateur uniquement, sauf si elle est référencée avec des noms qualifiés de schéma
Compatibilité avec les index	Oui	Oui
Compatibilité avec les clés étrangères	Oui	Oui
Conservation du LDD	Oui	Non
Action par défaut `ON COMMIT`	Les enregistrements sont supprimés.	Les enregistrements sont conservés.
`ON COMMIT PRESERVE ROWS`	Oui	Oui
`ON COMMIT DELETE ROWS`	Oui	Oui
`ON COMMIT DROP`	Non	Oui
Compatibilité `ALTER TABLE`	Oui	Oui
Collecte de statistiques	`DBMS_STATS.GATHER_TABLE_STATS`	`ANALYZE`
Oracle 12c `GLOBAL_TEMP_` `TABLE_STATS`	`DBMS_STATS.SET_TABLE_PREFS`	`ANALYZE`

Colonnes inutilisées

La fonctionnalité d'Oracle permettant de marquer des colonnes spécifiques en tant que UNUSED est souvent utilisée pour supprimer des colonnes des tables sans supprimer physiquement les données des colonnes. Cela permet d'éviter les charges élevées potentielles qui se produisent lors de la suppression de colonnes de tables volumineuses.

Dans PostgreSQL, la suppression d'une colonne de grande taille ne supprime pas les données de la colonne du stockage physique. L'opération est donc rapide, même sur les tables volumineuses. Il n'est pas nécessaire de marquer une colonne en tant que UNUSED comme dans une base de données Oracle. L'espace occupé par la colonne supprimée est récupéré par de nouvelles instructions LMD ou lors d'une opération VACUUM ultérieure.

Tables en lecture seule

Les tables en lecture seule sont une fonctionnalité Oracle qui permet de marquer les tables en lecture seule à l'aide de la commande ALTER TABLE. Dans Oracle 12c R2, cette fonctionnalité est également disponible pour les tables avec des partitions et des sous-partitions. PostgreSQL ne propose pas de fonctionnalité équivalente, mais il existe deux solutions de contournement :

Accordez l'autorisation SELECT sur les tables de certains utilisateurs. Notez que cela n'empêche pas le propriétaire de la table d'effectuer des opérations LMD sur ses tables.
Créez une instance dupliquée avec accès en lecture Cloud SQL pour PostgreSQL et redirigez les utilisateurs vers les tables dupliquées qui sont des tables en lecture seule. Cette solution nécessite l'ajout d'une instance dupliquée avec accès en lecture à une instance Cloud SQL pour PostgreSQL existante.

Créez un déclencheur de base de données qui génère des exceptions sur les instructions LMD. Exemple :

-- 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=>

Jeux de caractères

Oracle et PostgreSQL sont compatibles avec un grand nombre de jeux de caractères, de classements et d'Unicode, avec prise en charge des langages à un octet et à plusieurs octets. De plus, les bases de données PostgreSQL résidant sur la même instance peuvent être configurées avec des jeux de caractères distincts. Consultez la liste des jeux de caractères compatibles avec PostgreSQL.

Dans la base de données Oracle, les jeux de caractères sont spécifiés au niveau de la base de données (Oracle 12g R1 ou version antérieure) ou au niveau de la base de données connectable (Oracle 12g R2 ou version ultérieure). Dans PostgreSQL, un jeu de caractères par défaut est spécifié lorsqu'une instance Cloud SQL pour PostgreSQL est créée. Chaque base de données créée dans cette instance peut être créée avec un jeu de caractères différent. L'ordre de tri et la classification des caractères peuvent être spécifiés pour chaque colonne de table.

Exemple

-- 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(> );

Vues

PostgreSQL accepte les vues simples et complexes. Pour les options de création de vues, il existe quelques différences entre Oracle et PostgreSQL. Le tableau suivant met en évidence ces différences.

Fonctionnalité de vue Oracle	Description	Compatibilité avec Cloud SQL pour PostgreSQL	Remarques sur les conversions
`FORCE`	Créez une vue sans vérifier si les tables ou les vues sources existent.	Non	Aucune option équivalente disponible.
`CREATE OR REPLACE`	Créez une vue non existante ou remplacez une vue existante.	Oui	PostgreSQL accepte la commande `CREATE OR REPLACE` pour les vues.
`WITH CHECK OPTION`	Spécifie le niveau d'application lors de l'exécution d'opérations LMD sur la vue.	Oui	La valeur par défaut est `CASCADED`, ce qui entraîne également l'évaluation des vues référencées. Le mot clé `LOCAL` entraîne uniquement l'évaluation de la vue actuelle.
`WITH READ-ONLY`	Autorise uniquement les opérations de lecture sur la vue. Les opérations LMD sont interdites.	Non	Une solution de contournement consiste à accorder des privilèges SELECT sur la vue à tous les utilisateurs.
`VISIBLE \| INVISIBLE` (Oracle 12c)	Spécifiez si une colonne basée sur la vue est visible ou invisible pour l'utilisateur.	Non	Créez la colonne `VIEW` avec les colonnes requises uniquement.

L'exemple de suivant montre comment effectuer la conversion d'Oracle vers Cloud SQL pour 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).

Affichez la gestion des accès :

Les propriétaires d'une vue doivent disposer de droits sur les tables de base pour pouvoir la créer. L'utilisateur d'une vue doit disposer des autorisations SELECT appropriées sur cette vue. Il doit également disposer des autorisations INSERT, UPDATE et DELETE appropriées sur la vue lors de l'exécution d'opérations LMD via cette vue. Dans les deux cas, les utilisateurs n'ont pas besoin d'autorisations sur les tables sous-jacentes.

Étape suivante

Obtenez plus d'informations sur les comptes utilisateur PostgreSQL.
Découvrez des architectures de référence, des schémas et des bonnes pratiques concernant Google Cloud. Consultez notre Centre d'architecture cloud.