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

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

  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 
 

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

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

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)

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

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

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

  CREATE 
  
 TABLE 
  
 employees 
  
 ( 
  
 empid 
  
 INT 
 , 
  
 fname 
  
 VARCHAR 
 ( 
 30 
 ), 
  
 lname 
  
 VARCHAR 
 ( 
 30 
 ), 
  
 hired 
  
 DATE 
 , 
  
 separated 
  
 DATE 
 , 
  
 job_code 
  
 INT 
 , 
  
 store_id 
  
 INT 
 ) 
  
 PARTITION 
  
 BY 
  
 HASH 
  
 ( 
 date_part 
 ( 
 'year' 
 , 
  
 hired 
 )); 
 CREATE 
  
 TABLE 
  
 employees_p0 
  
 PARTITION 
  
 OF 
  
 employees 
  
 FOR 
  
 VALUES 
  
 WITH 
  
 ( 
 MODULUS 
  
 4 
 , 
  
 REMAINDER 
  
 0 
 ); 
 CREATE 
  
 TABLE 
  
 employees_p1 
  
 PARTITION 
  
 OF 
  
 employees 
  
 FOR 
  
 VALUES 
  
 WITH 
  
 ( 
 MODULUS 
  
 4 
 , 
  
 REMAINDER 
  
 1 
 ); 
 CREATE 
  
 TABLE 
  
 employees_p2 
  
 PARTITION 
  
 OF 
  
 employees 
  
 FOR 
  
 VALUES 
  
 WITH 
  
 ( 
 MODULUS 
  
 4 
 , 
  
 REMAINDER 
  
 2 
 ); 
 CREATE 
  
 TABLE 
  
 employees_p3 
  
 PARTITION 
  
 OF 
  
 employees 
  
 FOR 
  
 VALUES 
  
 WITH 
  
 ( 
 MODULUS 
  
 4 
 , 
  
 REMAINDER 
  
 3 
 ); 
 

  CREATE 
  
 TABLE 
  
 sales 
  
 ( 
  
 Saleid 
  
 INT 
 , 
  
 sale_date 
  
 DATE 
 , 
  
 cust_code 
  
 VARCHAR 
 ( 
 15 
 ), 
  
 income 
  
 DECIMAL 
 ( 
 8 
 , 
 2 
 )) 
 PARTITION 
  
 BY 
  
 RANGE 
 ( 
 date_part 
 ( 
 'year' 
 , 
  
 sale_date 
 )); 
 CREATE 
  
 TABLE 
  
 sales_2019 
  
 PARTITION 
  
 OF 
  
 sales 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 2019 
 ) 
  
 TO 
  
 ( 
 2020 
 ) 
  
 PARTITION 
  
 BY 
  
 RANGE 
 ( 
 date_part 
 ( 
 'month' 
 , 
  
 sale_date 
 )); 
 CREATE 
  
 TABLE 
  
 sales_2019_q1 
  
 PARTITION 
  
 OF 
  
 sales_2019 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 1 
 ) 
  
 TO 
  
 ( 
 4 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2019_q2 
  
 PARTITION 
  
 OF 
  
 sales_2019 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 4 
 ) 
  
 TO 
  
 ( 
 7 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2019_q3 
  
 PARTITION 
  
 OF 
  
 sales_2019 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 7 
 ) 
  
 TO 
  
 ( 
 10 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2019_q4 
  
 PARTITION 
  
 OF 
  
 sales_2019 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 10 
 ) 
  
 TO 
  
 ( 
 13 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2020 
  
 PARTITION 
  
 OF 
  
 sales 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 2020 
 ) 
  
 TO 
  
 ( 
 2021 
 ) 
  
 PARTITION 
  
 BY 
  
 RANGE 
 ( 
 date_part 
 ( 
 'month' 
 , 
  
 sale_date 
 )); 
 CREATE 
  
 TABLE 
  
 sales_2020_q1 
  
 PARTITION 
  
 OF 
  
 sales_2020 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 1 
 ) 
  
 TO 
  
 ( 
 4 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2020_q2 
  
 PARTITION 
  
 OF 
  
 sales_2020 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 4 
 ) 
  
 TO 
  
 ( 
 7 
 ); 
 CREATE 
  
 TABLE 
  
 sales_2020_h2 
  
 PARTITION 
  
 OF 
  
 sales_2020 
  
 FOR 
  
 VALUES 
  
 FROM 
  
 ( 
 7 
 ) 
  
 TO 
  
 ( 
 13 
 ); 
 

  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 
 ; 
 

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

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