Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/solutions/migrating-oracle-database-to-cloud-sql-for-postgresql below:

Migrating Oracle® Database users and schemas to Cloud SQL for PostgreSQL | Solutions

This document is part of a series that provides key information and guidance related to planning and performing Oracle® 11g/12c database migrations to Cloud SQL for PostgreSQL version 12. This document discusses the basic differences between Oracle® Database and Cloud SQL for PostgreSQL as they relate to creating users, schemas, tables, indexes, and views.

In addition to the introductory setup part, the series includes the following parts:

• Migrating Oracle users to Cloud SQL for PostgreSQL: Terminology and functionality
• Migrating Oracle users to Cloud SQL for PostgreSQL: Data types, users, and tables
• Migrating Oracle users to Cloud SQL for PostgreSQL: Queries, stored procedures, functions, and triggers
• Migrating Oracle users to Cloud SQL for PostgreSQL: Security, operations, monitoring, and logging
• Migrating Oracle Database users and schemas to Cloud SQL for PostgreSQL (this document)

Oracle and Cloud SQL for PostgreSQL have different architectures and terminology for instances, databases, users, and schemas. For a summary of these differences, see the terminology part of this series.

One of the first steps when planning a migration to Cloud SQL for PostgreSQL is to review the existing parameters settings on the source Oracle database. The settings around memory allocation, character-set, and storage parameters are particularly useful because they can inform the initial configuration and sizing of the Cloud SQL for PostgreSQL target environment. There are several methods for extracting Oracle parameters settings. Here are a few common ones:

• Automatic Workload Repository (AWR) reports hold resource allocation data (CPU, RAM), instance parameters configuration, and maximum active sessions.
• DBA_HIST, V$OSSTAT and V$LICENSEfor CPU usage details.
• V$PARAMETER view for database configuration parameters.
• V$NLS_PARAMETERS view for database language parameters.
• DBA_DATA_FILES view for calculating the database storage size.
• The Oracle SPFILE for database instance configurations.
• Job scheduler tools (for example, crontab) to identify routine backups or maintenance windows that should be taken under consideration.

At a high-level, each Oracle schema should be created as its own schema in PostgreSQL. In an Oracle database, user is synonymous with schema. That means a schema is created when you create a user. There is always a 1:1 relationship between users and schemas. In PostgreSQL, users and schemas are created separately. A user could be created without creating a corresponding schema. To maintain the same Oracle user or schema structure in PostgreSQL, you can create a schema for each user.

The following table illustrates conversion examples:

CREATE USER username WITH PASSWORD 'password';
CREATE SCHEMA schema_name;

DROP USER username;
DROP SCHEMA schema_name CASCADE;

PGPASSWORD=password psql -h hostname -U username -d database_name

With password prompt:

psql -h hostname -U username -W -d database_name

Oracle 12c databases users:

There are two types of users in Oracle 12c, common users and local users. Common users are created in the root CDB including PDBs. They are identified by the C## prefix in their username. Local users are created only in a specific PDB. Different database users with identical usernames can be created in multiple PDBs. When migrating from Oracle 12c to PostgreSQL, modify users and permissions to suit PostgreSQL's architecture. Here are two common examples to illustrate these differences:

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

Managing users through the Google Cloud console

To view Cloud SQL for PostgreSQL current configured users, go to the following page in the Google Cloud console:

Google Cloud > Storage > SQL > Instance > Users

Oracle and PostgreSQL differ in terms of case sensitivity. Oracle names are not case sensitive. PostgreSQL names are not case sensitive except when surrounded by double quotation marks. Many schema export and SQL generating tools for Oracle such as DBMS_METADATA.GET_DDL automatically add double quotation marks to object names. These quotation marks can lead to all sorts of problems after migration. We recommend removing all quotation marks surrounding object names from data definition language (DDL) statements before you create the objects in PostgreSQL.

When converting tables from Oracle to PostgreSQL data types, the first step is to extract the Oracle create table statements from the source database. The following sample query extracts the DDL for the locations table from the HR schema:

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

The full output includes storage elements, indexes, and tablespace information, which were omitted because these additional elements are not supported by the PostgreSQL CREATE TABLE statement.

After the DDL has been extracted, remove quotation marks surrounding names and perform the table conversion according to the Oracle-to-PostgreSQL data types conversion table. Check each column data type to see if it can be converted as is, or if not supported, choose a different data type according to the conversion table. For example, the following is the converted DDL for the locations table.

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

The CREATE TABLE AS SELECT (CTAS) statement is used to create a new table based on an existing table. Note that only column names and column data types are copied, while constraints and indexes are not. PostgreSQL supports the ANSI SQL standard for CTAS functionality, and is compatible with the Oracle CTAS statement.

PostgreSQL does not support invisible columns. For a workaround, create a view that holds only the visible columns.

Oracle provides six types of table constraints that can be defined on table creation or after table creation using the ALTER TABLE command. The Oracle constraints types are PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and REF. In addition, Oracle lets the user control the state of a constraint through the following options:

• INITIALLY IMMEDIATE: Checks the constraint at the end of each subsequent SQL statement (the default state).
• DEFERRABLE/NOT DEFERRABLE: Enables the use of the SET CONSTRAINT clause in subsequent transactions until a COMMIT statement is submitted
• INITIALLY DEFERRED: Checks the constraint at the end of subsequent transactions.
• VALIDATE/NO VALIDATE: Checks (or deliberately does not check) new or modified rows for errors. These parameters depend on whether the constraint is ENABLED or DISABLED.
• ENABLED/DISABLED: Specifies whether the constraint should be enforced after creation (ENABLED by default)

PostgreSQL also supports six types of table constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, and EXCLUDE. However, there are a few notable differences between Oracle and PostgreSQL constraint types, including the following:

• PostgreSQL does not support Oracle's REF constraint.
• PostgreSQL does not automatically create an index on the referencing columns for a foreign key constraint. A separate CREATE INDEX statement on the referencing columns is needed if an index is required.
• PostgreSQL does not support Oracle's ON DELETE SET NULL clause. This clause instructs Oracle to set any dependent values in child tables to NULL when the record in the parent table is deleted.
• Constraints on VIEWS are not supported, with the exception of CHECK OPTION.
• PostgreSQL does not support disabling constraints. PostgreSQL supports the NOT VALID option when a new foreign key or check constraint is added using an ALTER TABLE statement. This option tells PostgreSQL to skip the referential integrity checks on existing records in the child table.

The following table summarizes the key differences between Oracle and PostgreSQL's constraint types:

Uses the ON DELETE clause to handle cases of FOREIGN KEY parent record deletions. PostgreSQL provides three options to handle cases where data is deleted from the parent table and a child table is referenced by a FOREIGN KEY constraint:

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION

Uses the ON UPDATE clause to handle cases of FOREIGN KEY parent records updates.

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION

Oracle's virtual columns are based on other columns' calculation results. They appear as regular columns, but their values are derived from a calculation on the fly by the Oracle database engine and not stored in the database. Virtual columns can be used with constraints, indexes, table partitioning, and foreign keys, but cannot be manipulated through data manipulation language (DML) operations.

PostgreSQL's generated columns are comparable to Oracle's virtual columns in terms of functionality. However, unlike Oracle, generated columns in PostgreSQL are stored and you must specify a data type for each generated column, meaning that they occupy storage as if they are normal columns.

Example of a virtual column 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

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

Oracle and PostgreSQL provide a variety of indexing algorithms and types of indexes that can be used for a variety of applications. The following is a list of available indexing algorithms in PostgreSQL:

• Default index type for PostgreSQL, used for speeding up equality and range queries
• Supports all primitive data types and can be used to retrieve NULL values
• Index values are sorted in ascending order by default but can be configured in a descending order as well

• Used to speed up equality searches
• More efficient than B-tree index but limited to only handling equality searches

• Inverted tree indexes
• More efficient than B-tree index when dealing with columns that contain multiple component values, such as array and text

• Not a single kind of index, but an infrastructure for defining indexes that could support more comparison operators than a normal B-tree index would support
• Useful for geometric data when optimizing "nearest-neighbor" searches is needed

• Similar to GiST, SP-GiST is an infrastructure for user-defined indexing strategies
• Allows a wide range of different non-balanced data structures such as quadtrees
• Not available in Cloud SQL for PostgreSQL

• Block Range INdexes
• Stores summaries of physical block ranges of a table
• For columns with a linear sort order
• Useful for range lookup on huge tables

The following table compares the index types between Oracle and PostgreSQL:

Index conversion considerations

In most cases, Oracle indexes can simply be converted to PostgreSQL's B-tree indexes, because this type of index is the most commonly used index type. As in an Oracle database, an index is automatically created on the PRIMARY KEY fields of a table. Similarly, a UNIQUE index is automatically created on fields which have a UNIQUE constraint. In addition, secondary indexes are created using the standard CREATE INDEX statement.

The following example illustrates how an Oracle table with multiple indexed fields can be converted to 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=>

Both Oracle and PostgreSQL offer partitioning capabilities for splitting up large tables. This is accomplished by physically segmenting a table into smaller parts, where each part contains a horizontal subset of the rows. The partitioned table is referred to as the parent table and its rows are physically stored in its partitions. Though not all of Oracle's partition types are supported in PostgreSQL, PostgreSQL does support the most common ones.

The following sections describe the PostgreSQL supported partition types, illustrating each one with an example on how to create the partitions that correspond to that type.

This type of partition assigns rows to partitions based on column values falling within a given range. Each partition contains rows for which the partitioning expression value lies within a given range. It's important to note that ranges do not overlap across partitions.

Example

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

Similar to RANGE partitioning, LIST partitioning assigns rows to partitions based on column values falling within a predefined set of values. Key values that appear in each partition are explicitly listed for LIST partitions.

Example

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 partitioning is best suited for when the goal is to achieve an even distribution of data between all partitions. A column value (or expression based on a column value to be hashed) and the row value are assigned to the partition that corresponds to that hash value. Hash values must be uniquely assigned to partitions, and all inserted values must be mapped to exactly one partition.

Example

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

Multi-level partitioning is a method of creating a hierarchy of partitions for a single table. Each partition is further divided into a number of different partitions. The number of sub-partitions can vary from one partition to another.

Example

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

In PostgreSQL, partitions can be added or removed from the parent table. A partition that is detached can later be reattached to the same table. Moreover, new partitioning conditions can be specified when reattaching the partition, which allows partition boundaries to be adjusted.

Example

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;

The following table describes where Oracle and Cloud SQL for PostgreSQL partition types are equivalent and where a conversion is recommended:

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

The following example is a side-by-side comparison of creating table partitions on both platforms. Note that PostgreSQL does not support referencing a tablespace in the PARTITIONS clause of the CREATE TABLE command.

Oracle implementation

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 implementation

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

In an Oracle database, temporary tables are called GLOBAL TEMPORARY TABLES, while in PostgreSQL, they are known simply as temporary tables. The basic functionality of a temporary table is identical on both platforms. There are, however, a few notable differences:

• Oracle stores the temporary table structure for repeated use even after a database restart, while PostgreSQL stores the temporary table only for the duration of a session.
• A temporary table in an Oracle database can be accessed by different users with the appropriate permissions. By contrast, a temporary table in PostgreSQL can only be accessed during the session in which it was created unless the temporary table is referenced with schema-qualified names.
• In an Oracle database, there is a distinction between GLOBAL and LOCAL temporary tables that specify whether the content of the table is global or session specific. In PostgreSQL, the GLOBAL and LOCAL keywords are supported for compatibility reasons, but they have no effect on the visibility of the data.
• If the ON COMMIT clause is omitted when creating a temporary table, the default behavior in Oracle Database is ON COMMIT DELETE ROWS, which means that Oracle truncates the temporary table after each commit. By contrast, in PostgreSQL the default behavior is to preserve rows in the temporary table after each commit.

The following table highlights the differences in temporary tables between Oracle and Cloud SQL for PostgreSQL.

TABLE_STATS

Oracle's feature of marking specific columns as UNUSED is often used to remove columns from tables without physically removing the column data. This is to prevent the potential high loads that occur when dropping columns from large tables.

In PostgreSQL, dropping a large column does not remove the column data from the physical storage and is therefore a fast operation even on large tables. There is no need to mark a column as UNUSED as in an Oracle database. The space occupied by the dropped column is reclaimed either by new DML statements or during a subsequent VACUUM operation.

Read-only tables is an Oracle feature which marks tables as read-only using the ALTER TABLE command. In Oracle 12c R2, this feature is also available for tables with partitions and subpartitions. PostgreSQL does not offer an equivalent feature, but there are two possible workarounds:

• Grant SELECT permission on tables for specific users. Note that this does not preclude the table owner from performing DML operations on their tables.
• Create a Cloud SQL for PostgreSQL read replica and direct users to the replica tables that are read-only tables. This solution requires adding a read-replica instance to an existing Cloud SQL for PostgreSQL instance.

• Create a database trigger that raises exceptions on DML statements—for example:

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

Both Oracle and PostgreSQL support a wide variety of character sets, collations, and unicode, including support for both single-byte and multi-byte languages. In addition, PostgreSQL databases that reside on the same instance can be configured with distinct character sets. See the list of supported character sets in PostgreSQL.

In Oracle Database, character sets are specified at the database level (Oracle 12g R1 or earlier) or at pluggable database level (Oracle 12g R2 or later). In PostgreSQL, a default character set is specified when a new Cloud SQL for PostgreSQL instance is created. Each database created within that instance can be created with a different character set. Sort order and character classification can be specified per table column.

Example

-- 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 supports both simple and complex views. For view-creation options, there are a few differences between Oracle and PostgreSQL. The following table highlights these differences.

The LOCAL keyword causes only the current view to be evaluated.

The following conversion example demonstrates converting from Oracle to Cloud SQL PostgreSQL for views.

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

The owners of a view must have privileges on the base tables to create the view. The user of a view needs the appropriate SELECT permissions on the view. They also need the appropriate INSERT, UPDATE, DELETE permissions on the view when performing DML operations through the view. In either case, users don't need permissions on the underlying tables.

• Explore more about PostgreSQL user accounts.
• Explore reference architectures, diagrams, and best practices about Google Cloud. Take a look at our Cloud Architecture Center.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4