Migrazione di utenti e schemi di Oracle® Database a Cloud SQL per PostgreSQL

Questo documento fa parte di una serie che fornisce informazioni e indicazioni chiave relative alla pianificazione e all'esecuzione di migrazioni dei database Oracle® 11g/12c verso Cloud SQL per PostgreSQL alla versione 12. Questo documento illustra le differenze di base tra Oracle® Database e Cloud SQL per PostgreSQL in relazione alla creazione di utenti, schemi, tabelle indici e viste.

Oltre alla sezione nella parte introduttiva della configurazione, La serie è composta dalle seguenti parti:

• Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: terminologia e funzionalità
• Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: tipi di dati, utenti e tabelle
• Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: query, procedure archiviate, funzioni e trigger
• Migrazione degli utenti Oracle a Cloud SQL per PostgreSQL: sicurezza, operazioni, monitoraggio e logging
• Migrazione di utenti e schemi di Oracle Database a Cloud SQL per PostgreSQL (questo documento)

Differenze terminologiche tra Oracle e Cloud SQL per PostgreSQL

Oracle e Cloud SQL per PostgreSQL hanno architetture e terminologie diverse per istanze, database, utenti e schemi. Per un riepilogo di questi le differenze, consulta le terminologia di questa serie.

Esportazione delle configurazioni di Oracle

Uno dei primi passaggi per pianificare una migrazione a Cloud SQL per PostgreSQL consiste nell'esaminare le impostazioni dei parametri esistenti nel database Oracle di origine. Le impostazioni relative all'allocazione della memoria, al set di caratteri e ai parametri di archiviazione sono particolarmente utili perché possono fornire informazioni sulla configurazione e sulle dimensioni iniziali dell'ambiente di destinazione Cloud SQL per PostgreSQL. Esistono diversi metodi per estrarre le impostazioni dei parametri Oracle. Eccone alcuni comuni:

• I report del repository dei carichi di lavoro automatici (AWR) contengono i dati di allocazione delle risorse (CPU, RAM), la configurazione dei parametri dell'istanza e le sessioni attive massime.
• DBA_HIST, V$OSSTAT e V$LICENSE per i dettagli sull'utilizzo della CPU.
• Vista V$PARAMETER per i parametri di configurazione del database.
• Visualizza V$NLS_PARAMETERS per i parametri della lingua del database.
• Vista DBA_DATA_FILES per calcolare le dimensioni dello spazio di archiviazione del database.
• Il file Oracle SPFILE per le configurazioni delle istanze di database.
• Strumenti di pianificazione dei job (ad esempio crontab) per identificare i backup di routine o finestre di manutenzione che devono essere prese in considerazione.

Importazione e configurazione degli utenti in Cloud SQL per PostgreSQL

A un livello generale, ogni schema Oracle deve essere creato come schema indipendente in PostgreSQL. In un database Oracle, utente è sinonimo di schema. Ciò significa che viene creato uno schema quando crei un utente. Esiste sempre un rapporto 1:1 tra utenti e schemi. In PostgreSQL, gli utenti e gli schemi vengono creati separatamente. R è possibile creare l'utente senza creare uno schema corrispondente. Per mantenere la stessa struttura dello schema o dell'utente Oracle in PostgreSQL, puoi creare uno schema per ogni utente.

La seguente tabella illustra alcuni esempi di conversione:

Tipo di azione	Tipo di database	Confronto dei comandi
Crea utente e schema	Oracle	CREATE USER username IDENTIFIED BY password;
	PostgreSQL	Utente e schema sono concetti distinti in PostgreSQL, pertanto richiedono due comandi CREATE distinti CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;
Assegnazione dei ruoli	Oracle	GRANT CONNECT TO username;
	PostgreSQL	GRANT pg_monitor TO username;
Concessione di privilegi	Oracle	GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
	PostgreSQL	GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;
Revoca dei privilegi	Oracle	REVOKE UPDATE ON HR.EMPLOYEES FROM username;
	PostgreSQL	REVOKE UPDATE ON HR.EMPLOYEES FROM username;
Concedi DBA/superutente	Oracle	GRANT DBA TO username;
	PostgreSQL	GRANT cloudsqlsuperuser TO username;
Elimina utente	Oracle	DROP USER username CASCADE;
	PostgreSQL	Utente e schema sono concetti distinti in PostgreSQL, pertanto richiedono due dichiarazioni DROP separate DROP USER username; DROP SCHEMA schema_name CASCADE;
Metadati degli utenti	Oracle	DBA_USERS
	PostgreSQL	pg_catalog.pg_user
Metadati delle autorizzazioni	Oracle	DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS
	PostgreSQL	pg_catalog.pg_roles
Stringa di connessione della CLI	Oracle	sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid
	PostgreSQL	Senza richiesta di password: PGPASSWORD=password psql -h hostname -U username -d database_name Con richiesta di password: psql -h hostname -U username -W -d database_name

Utenti dei database Oracle 12c:

In Oracle 12c esistono due tipi di utenti: utenti comuni e utenti locali. Gli utenti comuni vengono creati nel CDB principale, inclusi i PDB. Sono identificati dal tipo C## nel nome utente. Gli utenti locali vengono creati solo in un PDB specifico. È possibile creare utenti di database diversi con nomi utente identici in più PDB. Quando esegui la migrazione da Oracle 12c a PostgreSQL, modifica gli utenti e le autorizzazioni in base all'architettura di PostgreSQL. Di seguito sono riportati due esempi comuni per illustrare queste differenze:

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

Gestire gli utenti tramite la console Google Cloud

Per visualizzare gli utenti attualmente configurati di Cloud SQL per PostgreSQL, vai alla seguente pagina nella console Google Cloud:

Google Cloud > Spazio di archiviazione > SQL > Istanza > Utenti

Importazione delle definizioni di tabelle e viste

Oracle e PostgreSQL differiscono in termini di distinzione tra maiuscole e minuscole. I nomi di Oracle non sono sensibili alle maiuscole. I nomi PostgreSQL non sono sensibili alle maiuscole, tranne se sono circondati tra virgolette. Molti strumenti di generazione di SQL ed esportazione dello schema per Oracle, come DBMS_METADATA.GET_DDL, aggiungono automaticamente virgolette doppie ai nomi degli oggetti. Queste virgolette possono causare tutti i tipi di problemi dopo la migrazione. Consigliamo di rimuovere dai dati tutte le virgolette che circondano i nomi degli oggetti con le istruzioni DDL (Definition Language) prima di creare gli oggetti in PostgreSQL.

Crea sintassi della tabella

Durante la conversione delle tabelle da Oracle a Tipi di dati PostgreSQL, Il primo passaggio è estrarre le istruzioni di creazione della tabella Oracle dall'origine per configurare un database. La seguente query di esempio estrae il DDL per la tabella delle località dallo schema RU:

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

L'output completo include elementi di archiviazione, indici e informazioni sullo spazio delle tabelle, omessi perché questi elementi aggiuntivi non sono supportati Istruzione CREATE TABLE PostgreSQL.

Dopo aver estratto il DDL, rimuovi le virgolette che circondano i nomi ed esegui la conversione della tabella in base alla tabella di conversione dei tipi di dati da Oracle a PostgreSQL. Controlla ogni tipo di dati della colonna per verificare se può essere convertito così com'è o, se non è supportato, scegli un tipo di dati diverso in base alla tabella di conversione. Per di seguito è riportato il DDL convertito per la tabella delle località.

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

Crea tabella come selezione (CTAS)

L'istruzione CREATE TABLE AS SELECT (CTAS) viene utilizzata per creare una nuova tabella basata su una tabella esistente. Tieni presente che solo i nomi delle colonne e i tipi di dati delle colonne vengono copiati, mentre i vincoli e gli indici non lo sono. PostgreSQL supporta il protocollo ANSI Standard SQL per la funzionalità degli inviti all'azione ed è compatibile con gli inviti all'azione di Oracle l'Informativa.

Colonne invisibili Oracle 12c

PostgreSQL non supporta le colonne invisibili. Per una soluzione alternativa, crea una vista che contiene solo le colonne visibili.

Vincoli della tabella

Oracle fornisce sei tipi di vincoli di tabella che possono essere definiti nella tabella o dopo la creazione della tabella con il comando ALTER TABLE. I tipi di vincoli Oracle sono PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL e REF. Inoltre, Oracle consente all'utente di controllare lo stato di un limitato tramite le opzioni seguenti:

• INITIALLY IMMEDIATE: controlla la limitazione alla fine di ogni istruzione SQL successiva (stato predefinito).
• DEFERRABLE/NOT DEFERRABLE: consente l'utilizzo della clausola SET CONSTRAINT nelle transazioni successive fino all'invio di un'istruzione COMMIT
• INITIALLY DEFERRED: controlla il vincolo alla fine delle transazioni successive.
• VALIDATE/NO VALIDATE: controlli (o non controlla deliberatamente) nelle righe nuove o modificate per errori. Questi parametri dipendono dal fatto che il vincolo sia ENABLED o DISABLED.
• ENABLED/DISABLED: specifica se il vincolo deve essere applicato dopo la creazione (ENABLED per impostazione predefinita)

PostgreSQL supporta anche sei tipi di vincoli di tabella: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL e EXCLUDE. Tuttavia, esistono alcune differenze significative tra i tipi di vincoli di Oracle e PostgreSQL, tra cui:

• PostgreSQL non supporta la limitazione REF di Oracle.
• PostgreSQL non crea automaticamente un indice sui riferimenti per un vincolo di chiave esterna. Un'istruzione CREATE INDEX separata sulle colonne di riferimento è necessaria se è richiesto un indice.
• PostgreSQL non supporta la clausola ON DELETE SET NULL di Oracle. Questa clausola indica a Oracle di impostare su NULL eventuali valori dipendenti nelle tabelle secondarie quando viene eliminato il record nella tabella principale.
• I vincoli su VIEWS non sono supportati, ad eccezione dei CHECK OPTION
• PostgreSQL non supporta la disattivazione dei vincoli. PostgreSQL supporta l'opzione NOT VALID quando viene aggiunta una nuova chiave esterna o un nuovo vincolo di controllo utilizzando un'istruzione ALTER TABLE. Questa opzione indica a PostgreSQL di ignorare i controlli dell'integrità referenziale sui record esistenti nella tabella figlio.

La tabella seguente riassume le principali differenze tra Oracle e Tipi di vincoli di PostgreSQL:

Tipo di vincolo Oracle	Supporto di Cloud SQL per PostgreSQL	Equivalente Cloud SQL per PostgreSQL
PRIMARY KEY	Sì	PRIMARY KEY
FOREIGN KEY	Sì	Utilizza la stessa sintassi ANSI SQL di Oracle. Utilizza la clausola ON DELETE per gestire i casi di eliminazione di FOREIGN KEY record padre. PostgreSQL fornisce tre opzioni per gestire i casi in cui i dati vengono eliminati dalla tabella principale e viene fatto riferimento a una tabella secondaria da un vincolo FOREIGN KEY: ON DELETE CASCADE ON DELETE RESTRICT ON DELETE NO ACTION PostgreSQL non supporta la clausola ON DELETE SET NULL di Oracle. Utilizza la clausola ON UPDATE per gestire i casi di aggiornamento di FOREIGN KEY record padre. PostgreSQL fornisce tre opzioni per gestire gli eventi di aggiornamento dei vincoli: ON UPDATE CASCADE ON UPDATE RESTRICT ON UPDATE NO ACTION PostgreSQL non crea automaticamente un indice sui riferimenti per un vincolo di chiave esterna.
UNIQUE	Sì	Crea un indice UNIQUE per impostazione predefinita.
CHECK	Sì	CHECK
NOT NULL	Sì	NOT NULL
REF	No	Non supportati.
DEFERRABLE/NOT DEFERRABLE	Sì	DEFERRABLE/NOT DEFERRABLE
INITIALLY IMMEDIATE	Sì	INITIALLY IMMEDIATE
INITIALLY DEFERRED	Sì	INITIALLY DEFERRED
VALIDATE/NO VALIDATE	No	Non supportati.
ENABLE/DISABLE	No	Questa opzione è abilitata per impostazione predefinita. Utilizza l'opzione NOT VALID quando viene generato un nuovo una chiave esterna o un vincolo di verifica viene aggiunto alla tabella utilizzando un'istruzione ALTER TABLE per saltare i controlli dell'integrità referenziale sugli record.
Vincolo per le visualizzazioni	No	Non supportato, ad eccezione di VIEW WITH CHECK OPTION.
Metadati dei vincoli	Oracle	DBA_CONSTRAINTS
	PostgreSQL	INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Colonne virtuali e generate

Le colonne virtuali di Oracle si basano sui risultati del calcolo di altre colonne. Loro vengono visualizzate come colonne regolari, ma i relativi valori sono derivati da un calcolo dal motore del database Oracle e non archiviati nel database. Virtuale le colonne possono essere utilizzate con vincoli, indici, partizioni delle tabelle ed ma non possono essere manipolati mediante il Data Manipulation Language (DML) operazioni aziendali.

Le colonne generate di PostgreSQL sono paragonabili alle colonne virtuali di Oracle in termini di funzionalità. Tuttavia, diversamente da Oracle, le colonne generate in PostgreSQL vengono archiviate e devi specificare un tipo di dato per ogni colonna generata, il che significa che occupano spazio di archiviazione come se fossero colonne normali.

Esempio di colonna virtuale in 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

Esempio equivalente in 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)

Indici tabella

Oracle e PostgreSQL forniscono una serie di algoritmi di indicizzazione e tipi di indici che possono essere utilizzati per una serie di applicazioni. Di seguito è riportato un elenco degli algoritmi di indicizzazione disponibili in PostgreSQL:

Algoritmo indice	Descrizione
Blu	Tipo di indice predefinito per PostgreSQL, utilizzato per accelerare l'uguaglianza e intervallo di valori Supporta tutti i tipi di dati primitivi e può essere utilizzato per recuperare i valori NULL Per impostazione predefinita, i valori dell'indice sono ordinati in ordine crescente, ma possono essere configurati anche in ordine decrescente
Hash	Utilizzato per velocizzare le ricerche di uguaglianza Più efficiente dell'indice B-tree, ma limitato alla sola gestione dell'uguaglianza ricerche
GIN	Indici ad albero invertiti Più efficiente dell'indice ad albero B quando si tratta di colonne contenenti più valori di componenti, ad esempio array e testo
GiST	Non è un solo tipo di indice, ma un'infrastruttura per definire Indici che potrebbero supportare più operatori di confronto rispetto a un normale B-tree supporterebbe Utile per i dati geometrici quando è necessaria l'ottimizzazione delle ricerche "nearest-neighbor"
SP-GiST	Analogamente a GiST, SP-GiST è un'infrastruttura per le strategie di indicizzazione definite dall'utente Consente un'ampia gamma di diverse strutture di dati non bilanciate, come Quadtrees Non disponibile in Cloud SQL per PostgreSQL
BRIN	INdexe intervallo di blocchi Memorizza i riepiloghi degli intervalli di blocchi fisici di una tabella Per le colonne con un ordinamento lineare Utile per la ricerca per intervallo in tabelle di grandi dimensioni

La tabella seguente mette a confronto i tipi di indice tra Oracle e PostgreSQL:

Indice Oracle	Descrizione	Supportato da PostgreSQL	Equivalente PostgreSQL
Indice bitmap	Memorizza un bitmap per ogni chiave dell'indice, ideale per fornire un recupero rapido dei dati per i carichi di lavoro OLAP	No	N/D
Indice ad albero B	Tipo di indice più comune, adatto a una serie di carichi di lavoro e può essere configurato nell'ordinamento ASC|DESC.	Sì	Indice ad albero B
Indice composto	Create più di due o più colonne per migliorare il rendimento dei dati recupero. L'ordinamento delle colonne all'interno dell'indice determina il percorso di accesso.	Sì	Indici con più colonne È possibile specificare fino a 32 colonne durante la creazione di un indice con più colonne.
Indice basato sulle funzioni	Memorizza l'output di una funzione applicata ai valori di una colonna della tabella.	Sì	Indici attivi espressioni
Indice univoco	Un indice B-tree che applica una limitazione UNIQUE ai valori indicizzati in base alla colonna.	Sì	Univoco indice
Indice del dominio dell'applicazione	Adatta all'indicizzazione di dati non relazionali come dati audio/video, dati LOB e altri tipi non di testo.	No	N/D
Indice invisibile	Funzionalità Oracle che consente di gestire, mantenere e testare gli indici senza influire sul processo decisionale dell'ottimizzatore.	No	Per una soluzione alternativa, puoi creare un indice aggiuntivo su una replica di lettura a scopo di test senza influire sull'attività in corso.
Tabella organizzata dall'indice	Un tipo di indice che controlla la modalità di archiviazione dei dati a livello di tabella e indice.	No	PostgreSQL non supporta le tabelle organizzate in base all'indice. La CLUSTER indica a PostgreSQL di organizzare l'archiviazione delle tabelle in base a specificato per l'indice. Ha uno scopo simile a quello organizzato in base all'indice tabella. Tuttavia, il clustering è un'operazione una tantum e PostgreSQL mantenere la struttura della tabella negli aggiornamenti successivi. È necessario un clustering manuale e periodico.
Indice locale e globale	Utilizzato per l'indicizzazione delle tabelle partizionate in un database Oracle. Ogni indice è definita come LOCAL o GLOBAL.	No	Gli indici di lavoro delle partizioni PostgreSQL hanno la stessa funzionalità degli indici locali di Oracle (ovvero l'indice è definito a livello di partizione, il livello globale non è supportato).
Indici parziali per tabelle partizionate (Oracle 12c)	Crea un indice su un sottoinsieme delle partizioni di una tabella. Supporta LOCAL e GLOBAL.	Sì	La suddivisione in parti in PostgreSQL funziona collegando le tabelle figlio a una tabella principale. È possibile creare indici solo su un sottoinsieme di tabelle figlie.
CREATE/DROP INDEX	Comando utilizzato per la creazione e l'eliminazione dell'indice.	Sì	PostgreSQL supporta il comando CREATE INDEX. Supporta inoltre ALTER TABLE tableName ADD INDEX indexName columnName
ALTER INDEX ... REBUILD	Ricostruisce l'indice, il che può causare un blocco esclusivo della tabella indicizzata.	Richiede una sintassi diversa	PostgreSQL supporta la ricostruzione degli indici utilizzando l'istruzione REINDEX. La tabella è bloccata per le scritture durante questa operazione e sono consentite solo le letture.
ALTER INDEX ... REBUILD ONLINE	Ricostruisce un indice senza creare un blocco esclusivo sulla tabella.	Richiede una sintassi diversa	PostgreSQL supporta la ricostruzione degli indici in contemporanea utilizzando l'istruzione REINDEX TABLE CONCURRENTLY. In questa modalità, PostgreSQL prova a ricreare indici che utilizzano un blocco minimo con il compromesso di potenzialmente richiedere più e le risorse necessarie per completare la ristrutturazione.
Compressione dell'indice	Una funzionalità per ridurre le dimensioni fisiche dell'indice.	No	N/D
Alloca l'indice a un tablespace	Crea uno spazio tabella di indici che può essere archiviato su un disco separato dai dati delle tabelle per ridurre i colli di bottiglia di I/O del disco.	No	Sebbene PostgreSQL consenta di creare un indice nello spazio tabella definito dall'utente, non puoi creare spazi tabella in Cloud SQL per PostgreSQL e l'indice deve essere creato nello spazio tabella predefinito.
Metadati degli indici (tabelle/visualizzazioni)	Oracle	DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS
	PostgreSQL	pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class

Considerazioni sulla conversione dell'indice

Nella maggior parte dei casi, gli indici Oracle possono essere semplicemente convertiti Indici B-tree, perché questo tipo di indice è il più comunemente utilizzato. Come in un database Oracle, viene creato automaticamente un indice sui campi PRIMARY KEY di una tabella. Analogamente, viene creato automaticamente un indice UNIQUE per i campi con un vincolo UNIQUE. Inoltre, gli indici secondari vengono creato utilizzando l'istruzione CREATE INDEX standard.

Il seguente esempio illustra come una tabella Oracle con più campi indicizzati può essere convertita in 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=>

Partizionamento delle tabelle

Sia Oracle che PostgreSQL offrono funzionalità di partizionamento per suddividere le tabelle di grandi dimensioni. Ciò viene ottenuto segmentando fisicamente una tabella in parti più piccole, in cui ogni parte contiene un sottoinsieme orizzontale delle righe. La tabella partizionata è denominata tabella padre e le sue righe sono archiviati fisicamente nelle sue partizioni. Sebbene non tutta la partizione di Oracle sono supportati in PostgreSQL, PostgreSQL supporta i più comuni quelli.

Le seguenti sezioni descrivono i tipi di partizioni PostgreSQL supportati. illustrando ognuna di esse con un esempio su come creare le partizioni corrispondono a quel tipo.

Partizionamento RANGE

Questo tipo di partizione assegna le righe alle partizioni in base ai valori delle colonne rientranti in un determinato intervallo. Ogni partizione contiene righe per le quali il valore dell'espressione di partizione rientra in un determinato intervallo. È importante tenere presente che gli intervalli non si sovrappongono tra le partizioni.

Esempio

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

Partizionamento LIST

Come per il partizionamento RANGE, il partizionamento LIST assegna righe a in base ai valori della colonna che rientrano in un insieme predefinito di e i relativi valori. Le coppie chiave-valore che appaiono in ogni partizione sono elencate esplicitamente per LIST partizioni.

Esempio

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

Partizionamento HASH

La suddivisione in HASH è ideale quando l'obiettivo è ottenere una distribuzione uniforme dei dati tra tutte le partizioni. Un valore di colonna (o un'espressione) in base al valore di colonna da sottoporre ad hashing) e il valore della riga viene assegnato la partizione corrispondente al valore hash. I valori hash devono essere assegnati in modo univoco alle partizioni e tutti i valori inseriti devono essere mappati a esattamente una partizione.

Esempio

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

Partizionamento multilivello

La partizione a più livelli è un metodo per creare una gerarchia di partizioni per una singola tabella. Ogni partizione è ulteriormente suddivisa in un numero di partizioni diverse. Il numero di sottopartizioni può variare da una partizione all'altra.

Esempio

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

Collegamento o scollegamento delle partizioni

In PostgreSQL, le partizioni possono essere aggiunte o rimosse dalla tabella padre. Una tabella indipendente può essere ricollegata alla stessa tabella in un secondo momento. Inoltre, quando si ricollega la suddivisione, è possibile specificare nuove condizioni di suddivisione, che consentono di regolare i relativi confini.

Esempio

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;

La tabella seguente descrive dove Oracle e Cloud SQL per PostgreSQL di partizione sono equivalenti e in cui è consigliata una conversione:

Tipo di partizione Oracle	Supportato da PostgreSQL	Implementazione di PostgreSQL
RANGE partizioni	Sì	PARTITION BY RANGE
LIST partizioni	Sì	PARTITION BY LIST
HASH partizioni	Sì	PARTITION BY HASH
SUB-PARTITIONING	Sì	Partizionamento multilivello
Partizioni degli intervalli	No	Non supportata
Partition Advisor	No	Non supportata
Partizionamento delle preferenze	No	Non supportata
Partizionamento virtuale basato su colonne	No	Come soluzione alternativa, valuta il partizionamento con l'espressione della colonna virtuale direttamente: 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)));
Partizione automatica degli elenchi	No	Non supportata
Suddividi partizioni	No	Per una soluzione alternativa, valuta la possibilità di scollegare o collegare le partizioni delle tabelle Regola i confini della partizione
Partizioni della piattaforma di scambio pubblicitario	Sì	DETACH / ATTACH PARTITION
Partizionamento multi-tipo (partizionamento composito)	Sì	Partizionamento multilivello
Metadati delle partizioni	Oracle	DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS
	PostgreSQL	pg_catalog.pg_class pg_catalog.pg_partitioned_table

L'esempio seguente è un confronto fianco a fianco della creazione di tabelle su entrambe le piattaforme. Tieni presente che PostgreSQL non supporta fare riferimento a uno spazio delle tabelle nella clausola PARTITIONS del documento CREATE TABLE .

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

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

Tabelle temporanee

In un database Oracle, le tabelle temporanee sono chiamate GLOBAL TEMPORARY TABLES, mentre in PostgreSQL sono note semplicemente come tabelle temporanee. La funzionalità di base di una tabella temporanea è identica su entrambe le piattaforme. Tuttavia, esistono alcune differenze sostanziali:

• Oracle archivia la struttura temporanea della tabella per un uso ripetuto anche dopo riavviare il database, mentre PostgreSQL archivia la tabella temporanea la durata di una sessione.
• Più utenti possono accedere a una tabella temporanea di un database Oracle con le autorizzazioni appropriate. Al contrario, una tabella temporanea È possibile accedere a PostgreSQL solo durante la sessione in cui è stato creato a meno che alla tabella temporanea non si faccia riferimento con nomi validi per lo schema.
• In un database Oracle esiste una distinzione tra GLOBAL e LOCAL temporaneo tabelle che specificano se il contenuto della tabella è globale o di sessione specifici. In PostgreSQL, le parole chiave GLOBAL e LOCAL sono supportate per motivi di compatibilità, ma non influiscono sulla visibilità dei dati.
• Se la clausola ON COMMIT viene omessa durante la creazione di una tabella temporanea, il comportamento predefinito in Oracle Database è ON COMMIT DELETE ROWS, significa che Oracle tronca la tabella temporanea dopo ogni commit. Al contrario, in PostgreSQL il comportamento predefinito è conservare le righe nella tabella temporary dopo ogni commit.

La tabella seguente evidenzia le differenze nelle tabelle temporanee tra Oracle e Cloud SQL per PostgreSQL.

Funzionalità tabella temporanea	Implementazione di Oracle	Implementazione di PostgreSQL
Sintassi	CREATE GLOBAL TEMPORARY TABLE	CREATE TEMPORARY TABLE
Accessibilità	Accessibile da più sessioni	Accessibile solo dalla sessione del creator, a meno che non venga fatto riferimento con nomi qualificati per lo schema
Supporto dell'indice	Sì	Sì
Supporto delle chiavi esterne	Sì	Sì
Mantieni DDL	Sì	No
Azione predefinita ON COMMIT	I record sono stati eliminati	I record vengono conservati
ON COMMIT PRESERVE ROWS	Sì	Sì
ON COMMIT DELETE ROWS	Sì	Sì
ON COMMIT DROP	No	Sì
ALTER TABLE assistenza	Sì	Sì
Raccolta delle statistiche	DBMS_STATS.GATHER_TABLE_STATS	ANALYZE
Oracle 12c GLOBAL_TEMP_ TABLE_STATS	DBMS_STATS.SET_TABLE_PREFS	ANALYZE

Colonne inutilizzate

La funzionalità di Oracle che consente di contrassegnare colonne specifiche come UNUSED viene spesso utilizzata per rimuovere le colonne dalle tabelle senza rimuovere fisicamente i dati delle colonne. Questo è per evitare carichi potenzialmente elevati che si verificano quando si eliminano le colonne tabelle.

In PostgreSQL, l'eliminazione di una colonna di grandi dimensioni non rimuove i dati della colonna dall'archiviazione fisica ed è quindi un'operazione rapida anche su tabelle di grandi dimensioni. Non è necessario contrassegnare una colonna come UNUSED come in un database Oracle. Spazio occupata dalla colonna eliminata viene recuperata da nuove istruzioni DML nel corso di una VACUUM operativa.

Tabelle di sola lettura

Le tabelle di sola lettura sono una funzionalità di Oracle che contrassegna le tabelle come di sola lettura utilizzando Comando ALTER TABLE. In Oracle 12c R2, questa funzione è disponibile anche per e tabelle con partizioni e sottopartizioni. PostgreSQL non offre una funzionalità equivalente, ma esistono due possibili soluzioni alternative:

• Concedi l'autorizzazione SELECT sulle tabelle per utenti specifici. Tieni presente che questo non impedisce al proprietario della tabella di eseguire operazioni DML sulle sue tabelle.
• Crea un'istanza Cloud SQL per PostgreSQL replica di lettura e indirizzare gli utenti alle tabelle di replica che sono tabelle di sola lettura. Questa soluzione richiede l'aggiunta di un'istanza replica di lettura a un'istanza Cloud SQL per PostgreSQL esistente.

• Crea un trigger di database che generi eccezioni per le istruzioni DML, ad esempio esempio:

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

Set di caratteri

Sia Oracle che PostgreSQL supportano un'ampia gamma di set di caratteri, collation e Unicode, incluso il supporto per le lingue a byte singolo e a più byte. Nella Inoltre, i database PostgreSQL che risiedono nella stessa istanza possono essere configurati con set di caratteri distinti. Consulta l'elenco dei set di caratteri supportati in PostgreSQL.

In Oracle Database, i set di caratteri vengono specificati a livello di database (Oracle 12g R1 o precedente) o a livello di database modulare (Oracle 12g R2 o in seguito). In PostgreSQL, viene specificato un set di caratteri predefinito quando viene L'istanza Cloud SQL per PostgreSQL creato. Ogni database creato all'interno dell'istanza può essere creato con un set di caratteri. L'ordine di ordinamento e la classificazione dei caratteri possono essere specificati per colonna della tabella.

Esempio

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

Visualizzazioni

PostgreSQL supporta viste sia semplici che complesse. Per le opzioni di creazione delle visualizzazioni, esistono alcune differenze tra Oracle e PostgreSQL. La tabella seguente evidenzia queste differenze.

Visualizza funzionalità Oracle	Descrizione	Assistenza Cloud SQL per PostgreSQL	Considerazioni sulle conversioni
FORCE	Crea una vista senza verificare se le tabelle/viste di origine esistono.	No	Nessun'opzione equivalente disponibile.
CREATE OR REPLACE	Creare una visualizzazione non esistente o sovrascrivere una esistente.	Sì	PostgreSQL supporta il comando CREATE OR REPLACE per visualizzazioni.
WITH CHECK OPTION	Specifica il livello di applicazione quando vengono eseguite operazioni DML sulla vista.	Sì	Il valore predefinito è CASCADED, che comporta anche la valutazione delle visualizzazioni a cui si fa riferimento. La parola chiave LOCAL determina solo la visualizzazione corrente viene valutato.
WITH READ-ONLY	Consente solo le operazioni di lettura sulla vista. Le operazioni DML sono vietate.	No	Una soluzione alternativa è concedere i privilegi SELECT per la vista a tutti gli utenti.
VISIBLE | INVISIBLE (Oracle 12c)	Specifica se una colonna basata sulla visualizzazione è visibile o invisibile all'utente.	No	Crea VIEW con solo le colonne obbligatorie.

L'esempio di conversione seguente mostra la conversione da Oracle a Cloud SQL PostgreSQL per le viste.

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

Gestione dell'accesso in visualizzazione:

I proprietari di una vista devono disporre dei privilegi sulle tabelle di base per crearla. L'utente di una vista deve disporre delle autorizzazioni SELECT appropriate per la vista. Inoltre, devono disporre delle autorizzazioni INSERT, UPDATE, DELETE appropriate per la visualizzazione quando eseguono operazioni DML tramite la visualizzazione. In entrambi i casi, gli utenti non hanno bisogno di autorizzazioni per le tabelle sottostanti.

Passaggi successivi

• Scopri di più sugli account utente di PostgreSQL.
• Esplora architetture di riferimento, diagrammi e best practice su Google Cloud. Dai un'occhiata al nostro Centro architetture cloud.