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

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)

Terminology differences between Oracle and Cloud SQL for PostgreSQL

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.

Exporting Oracle configurations

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.

Importing and configuring users in Cloud SQL for PostgreSQL

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:

Action type	Database type	Command comparison
Create user and schema	`Oracle`	`CREATE USER username IDENTIFIED BY password;`
	`PostgreSQL`	User and schema are distinct concepts in PostgreSQL, therefore require two separate `CREATE` statements `CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;`
Assigning roles	`Oracle`	`GRANT CONNECT TO username;`
	`PostgreSQL`	`GRANT pg_monitor TO username;`
Granting privileges	`Oracle`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
	`PostgreSQL`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
Revoking privileges	`Oracle`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
	`PostgreSQL`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
Grant DBA/superuser	`Oracle`	`GRANT DBA TO username;`
	`PostgreSQL`	`GRANT cloudsqlsuperuser TO username;`
Drop user	`Oracle`	`DROP USER username CASCADE;`
	`PostgreSQL`	User and schema are distinct concepts in PostgreSQL, therefore require two separate `DROP` statements `DROP USER username; DROP SCHEMA schema_name CASCADE;`
Users metadata	`Oracle`	`DBA_USERS`
	`PostgreSQL`	`pg_catalog.pg_user`
Permissions metadata	`Oracle`	`DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS`
	`PostgreSQL`	`pg_catalog.pg_roles`
CLI connection string	`Oracle`	`sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid`
	`PostgreSQL`	Without password prompt: `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

Screenshot of Users page.

Importing table and view definitions

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.

Create table syntax

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

Create Table As Select (CTAS)

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.

Oracle 12c invisible columns

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

Table constraints

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:

Oracle constraint type	Cloud SQL for PostgreSQL support	Cloud SQL for PostgreSQL equivalent
`PRIMARY KEY`	Yes	`PRIMARY KEY`
`FOREIGN KEY`	Yes	Uses the same ANSI SQL syntax as Oracle. 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` PostgreSQL does not support Oracle's `ON DELETE SET NULL` clause. Uses the `ON UPDATE` clause to handle cases of `FOREIGN KEY` parent records updates. PostgreSQL provides three options to handle `FOREIGN KEY` constraint update events: `ON UPDATE CASCADE` `ON UPDATE RESTRICT` `ON UPDATE NO ACTION` PostgreSQL does not automatically create an index on the referencing columns for a foreign key constraint.
`UNIQUE`	Yes	Creates a `UNIQUE` index by default.
`CHECK`	Yes	`CHECK`
`NOT NULL`	Yes	`NOT NULL`
`REF`	No	Not supported.
`DEFERRABLE/NOT DEFERRABLE`	Yes	`DEFERRABLE/NOT DEFERRABLE`
`INITIALLY IMMEDIATE`	Yes	`INITIALLY IMMEDIATE`
`INITIALLY DEFERRED`	Yes	`INITIALLY DEFERRED`
`VALIDATE/NO VALIDATE`	No	Not supported.
`ENABLE/DISABLE`	No	Enabled by default. Use the `NOT VALID` option when a new foreign key or check constraint is added to the table using an `ALTER TABLE` statement to skip referential integrity checks on existing records.
Constraint on VIEWs	No	Not supported except the `VIEW WITH CHECK OPTION`.
Constraints metadata	Oracle	`DBA_CONSTRAINTS`
	PostgreSQL	`INFORMATION_SCHEMA.TABLE_CONSTRAINTS`

Virtual and generated columns

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)

Table indexes

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:

Index algorithm	Description
B-tree	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
Hash	Used to speed up equality searches More efficient than B-tree index but limited to only handling equality searches
GIN	Inverted tree indexes More efficient than B-tree index when dealing with columns that contain multiple component values, such as array and text
GiST	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
SP-GiST	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
BRIN	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:

Oracle index	Description	Supported by PostgreSQL	PostgreSQL equivalent
Bitmap index	Stores a bitmap for each index key, best suited for providing fast data retrieval for OLAP workloads	No	N/A
B-tree index	Most common index type, well suited for a variety of workloads and can be configured in `ASC\|DESC` sorting.	Yes	B-tree index
Composite index	Created over two or more columns to improve the performance of data retrieval. Column ordering inside the index determines the access path.	Yes	Multiple-column indexes Up to 32 columns can be specified when creating a multiple-column index.
Function-based index	Stores the output of a function applied on the values of a table column.	Yes	Indexes on expressions
Unique index	A B-tree index which enforces a `UNIQUE` constraint on the indexed values on a per column basis.	Yes	Unique index
Application domain index	Suited for indexing non-relational data such as audio/video data, LOB data and other non-textual types.	No	N/A
Invisible index	Oracle feature that lets you manage, maintain, and test indexes without affecting the optimizer decision making.	No	For an alternative solution, you can create an additional index on a read-replica for test purposes without affecting on-going activity.
Index-organized table	A type of index that controls how data is stored at the table and index level.	No	PostgreSQL does not support index-organized tables. The `CLUSTER` statement instructs PostgreSQL to organize table storage according to a specified index. It serves a similar purpose to Oracle's index-organized table. However, clustering is a one-time operation, and PostgreSQL does not maintain the structure of the table on subsequent updates. Manual, periodic clustering is needed.
Local and global index	Used for indexing partitioned tables in an Oracle database. Each index is defined as either `LOCAL` or `GLOBAL`.	No	PostgreSQL partitions work indexes have the same functionality as Oracle local indexes (i.e., the index is defined at the partition level, global level is not supported).
Partial indexes for partitioned tables (Oracle 12c)	Creates an index on a subset of a table's partitions. Supports `LOCAL` and `GLOBAL`.	Yes	Partitioning in PostgreSQL works by attaching child tables into a parent table. It's possible to create indexes only on a subset of child tables.
`CREATE/DROP INDEX`	Command used for index creation and dropping.	Yes	PostgreSQL supports the `CREATE INDEX` command. It also supports `ALTER TABLE tableName ADD INDEX indexName columnName`
`ALTER INDEX ... REBUILD`	Rebuilds the index, which can cause an exclusive lock on the indexed table.	Requires different syntax	PostgreSQL supports index rebuilds using the `REINDEX` statement. The table is locked for writes during this operation and only reads are allowed.
`ALTER INDEX ... REBUILD ONLINE`	Rebuilds an index without creating an exclusive lock on the table.	Requires different syntax	PostgreSQL supports concurrent index rebuilds using the `REINDEX TABLE CONCURRENTLY` statement. In this mode, PostgreSQL tries to rebuild indexes using minimum locking with the trade-off of potentially taking more time and resources to complete the rebuild.
Index compression	A feature to reduce index physical size.	No	N/A
Allocate index to a tablespace	Creates an index tablespace that can be stored on a separate disk from table data to reduce disk I/O bottlenecks.	No	Although PostgreSQL allows an index to be created in user-defined tablespace, you cannot create tablespaces in Cloud SQL for PostgreSQL, and the index has to be built in default tablespace.
Indexes metadata (tables/views)	Oracle	`DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS`
	PostgreSQL	`pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class`

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

Table partitioning

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.

RANGE partitioning

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

LIST partitioning

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

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

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

Attaching or detaching partitions

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:

Oracle partition type	Supported by PostgreSQL	PostgreSQL implementation
`RANGE` partitions	Yes	`PARTITION BY RANGE`
`LIST` partitions	Yes	`PARTITION BY LIST`
`HASH` partitions	Yes	`PARTITION BY HASH`
`SUB-PARTITIONING`	Yes	Multi-level partitioning
Interval partitions	No	Not supported
Partition advisor	No	Not supported
Preference partitioning	No	Not supported
Virtual column-based partitioning	No	As a workaround, consider partitioning with the virtual column expression directly: `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)));`
Automatic list partitioning	No	Not supported
Split partitions	No	For a workaround, consider detaching or attaching table partitions to adjust partition boundaries
Exchange partitions	Yes	`DETACH / ATTACH PARTITION`
Multi-type partitioning (composite partitioning)	Yes	Multi-level partitioning
Partitions metadata	Oracle	`DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS`
	PostgreSQL	`pg_catalog.pg_class pg_catalog.pg_partitioned_table`

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

Temporary tables

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.

Temporary table feature	Oracle implementation	PostgreSQL implementation
Syntax	`CREATE GLOBAL TEMPORARY TABLE`	`CREATE TEMPORARY TABLE`
Accessibility	Accessible from multiple sessions	Accessible from the creator's session only unless referenced with schema-qualified names
Index support	Yes	Yes
Foreign key support	Yes	Yes
Preserve DDL	Yes	No
`ON COMMIT` default action	Records are deleted	Records are preserved
`ON COMMIT PRESERVE ROWS`	Yes	Yes
`ON COMMIT DELETE ROWS`	Yes	Yes
`ON COMMIT DROP`	No	Yes
`ALTER TABLE` support	Yes	Yes
Gathering statistics	`DBMS_STATS.GATHER_TABLE_STATS`	`ANALYZE`
Oracle 12c `GLOBAL_TEMP_` `TABLE_STATS`	`DBMS_STATS.SET_TABLE_PREFS`	`ANALYZE`

Unused columns

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

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

Character sets

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

Views

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.

Oracle view feature	Description	Cloud SQL for PostgreSQL support	Conversion considerations
`FORCE`	Create a view without verifying if the source tables/views exist.	No	No equivalent option available.
`CREATE OR REPLACE`	Create a non-existing view or overwrite an existing view.	Yes	PostgreSQL supports the `CREATE OR REPLACE` command for views.
`WITH CHECK OPTION`	Specifies the level of enforcement when performing DML operations against the view.	Yes	Default is `CASCADED`, which causes referenced views to be evaluated as well. The `LOCAL` keyword causes only the current view to be evaluated.
`WITH READ-ONLY`	Permits only read operations on the view. DML operations are forbidden.	No	A workaround is to grant SELECT privileges on the view to all users.
`VISIBLE \| INVISIBLE` (Oracle 12c)	Specify whether a column based on the view is visible or invisible to the user.	No	Create the `VIEW` with the required columns only.

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

View access management:

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.

What's next

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