이 문서는 PostgreSQL용 Cloud SQL 버전 12로 Oracle® 11g/12c 데이터베이스 마이그레이션 계획 및 수행과 관련된 핵심 정보와 지침을 제공하는 시리즈의 일부입니다. 이 문서에서는 사용자, 스키마, 테이블, 색인, 뷰 만들기와 관련하여 Oracle과 PostgreSQL용 Cloud SQL의 기본적인 차이점을 설명합니다.
이 시리즈에는 소개 설정 부분 외에도 다음 부분이 포함됩니다.
- Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 용어 및 기능
- Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 데이터 유형, 사용자, 테이블
- Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 쿼리, 저장 프로시저, 함수, 트리거
- Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 보안, 작업, 모니터링, 로깅
- Oracle 데이터베이스 사용자 및 스키마를 PostgreSQL용 Cloud SQL로 마이그레이션(이 문서)
Oracle과 PostgreSQL용 Cloud SQL의 용어 차이점
Oracle과 PostgreSQL용 Cloud SQL은 인스턴스, 데이터베이스, 사용자, 스키마의 아키텍처와 용어가 서로 다릅니다. 이러한 차이점에 대한 요약은 이 시리즈의 용어 부분을 참조하세요.
Oracle 구성 내보내기
PostgreSQL용 Cloud SQL로의 마이그레이션을 계획할 때 첫 번째 단계 중 하나는 소스 Oracle 데이터베이스의 기존 매개변수 설정을 검토하는 것입니다. 메모리 할당, 문자 집합, 스토리지 매개변수와 관련된 설정으로 PostgreSQL용 Cloud SQL 대상 환경의 초기 구성 및 크기를 알 수 있으므로 유용합니다. Oracle 매개변수 설정을 추출하는 방법에는 여러 가지가 있습니다. 일반적인 방법 몇 가지는 다음과 같습니다.
- 자동 워크로드 저장소(AWR) 보고서에는 리소스 할당 데이터(CPU, RAM), 인스턴스 매개변수 구성, 최대 활성 세션이 저장됩니다.
DBA_HIST,V$OSSTAT,V$LICENSE: CPU 사용량 세부정보V$PARAMETER뷰: 데이터베이스 구성 매개변수V$NLS_PARAMETERS뷰: 데이터베이스 언어 매개변수DBA_DATA_FILES뷰: 데이터베이스 스토리지 크기 계산- Oracle
SPFILE: 데이터베이스 인스턴스 구성 - 작업 스케줄러 도구(예:
crontab): 주기적으로 고려해야 할 루틴 백업 또는 유지보수 기간 식별
PostgreSQL용 Cloud SQL에서 사용자 가져오기 및 구성하기
개략적으로 각 Oracle 스키마는 PostgreSQL에서 자체 스키마로 만들어야 합니다. Oracle 데이터베이스에서 사용자는 스키마와 동의어입니다. 즉, 사용자를 만들면 스키마가 생성됩니다. 사용자와 스키마는 항상 1:1 관계입니다. PostgreSQL에서는 사용자와 스키마가 별도로 생성됩니다. 해당 스키마를 만들지 않고 사용자를 만들 수 있습니다. PostgreSQL에서 Oracle 사용자 또는 스키마 구조를 동일하게 유지하려면 각 사용자의 스키마를 만들면 됩니다.
다음 표는 변환 예시를 보여줍니다.
| 작업 유형 | 데이터베이스 유형 | 명령어 비교 |
|---|---|---|
| 사용자 및 스키마 만들기 | Oracle |
CREATE USER username IDENTIFIED BY password; |
PostgreSQL |
사용자 및 스키마는 PostgreSQL에서 별개의 개념이므로 별도의 CREATE 문 두 개가 필요합니다.CREATE USER username WITH PASSWORD 'password'; |
|
| 역할 지정 | Oracle |
GRANT CONNECT TO username; |
PostgreSQL |
GRANT pg_monitor TO username; |
|
| 권한 부여 | Oracle |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
PostgreSQL |
GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username; |
|
| 권한 취소 | Oracle |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
PostgreSQL |
REVOKE UPDATE ON HR.EMPLOYEES FROM username; |
|
| DBA/수퍼유저 권한 부여 | Oracle |
GRANT DBA TO username; |
PostgreSQL |
GRANT cloudsqlsuperuser TO username; |
|
| 사용자 삭제 | Oracle |
DROP USER username CASCADE; |
PostgreSQL |
사용자 및 스키마는 PostgreSQL에서 별개의 개념이므로 별도의 DROP 문 두 개가 필요합니다.DROP USER username; |
|
| 사용자 메타데이터 | Oracle |
DBA_USERS |
PostgreSQL |
pg_catalog.pg_user |
|
| 권한 메타데이터 | Oracle |
DBA_SYS_PRIVS |
PostgreSQL |
pg_catalog.pg_roles |
|
| CLI 연결 문자열 | Oracle |
sqlplus username/password@host/tns_alias |
PostgreSQL |
비밀번호 프롬프트 없음:PGPASSWORD=password psql -h hostname -U username -d database_name비밀번호 프롬프트 포함: psql -h hostname -U username -W -d database_name |
Oracle 12c 데이터베이스 사용자:
Oracle 12c에는 두 가지 유형의 사용자(일반 사용자 및 로컬 사용자)가 있습니다. 일반 사용자는 PDB를 포함한 루트 CDB에 생성됩니다. 사용자 이름의 C## 프리픽스로 식별됩니다. 로컬 사용자는 특정 PDB에서만 생성됩니다.
사용자 이름이 같은 여러 데이터베이스 사용자를 여러 PDB에 만들 수 있습니다. Oracle 12c에서 PostgreSQL로 마이그레이션할 때 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)
Google Cloud 콘솔을 통해 사용자 관리
현재 구성된 PostgreSQL용 Cloud SQL 사용자를 보려면 Google Cloud 콘솔의 다음 페이지로 이동합니다.
Google Cloud > 스토리지 > SQL > 인스턴스 > 사용자
테이블 및 뷰 정의 가져오기
Oracle과 PostgreSQL은 대소문자 구분이 서로 다릅니다. Oracle 이름은 대소문자를 구분하지 않습니다. PostgreSQL 이름은 큰따옴표로 묶인 경우를 제외하고 대소문자를 구분하지 않습니다. DBMS_METADATA.GET_DDL와 같은 Oracle용 스키마 내보내기 및 SQL 생성 도구는 대부분 객체 이름에 큰따옴표를 추가합니다. 이러한 따옴표는 마이그레이션 후에 모든 종류의 문제를 일으킬 수 있습니다.
PostgreSQL에서 객체를 만들기 전에 데이터 정의 언어(DDL) 문에서 객체 이름을 둘러싼 모든 따옴표를 삭제하는 것이 좋습니다.
테이블 구문 만들기
Oracle에서 PostgreSQL 데이터 유형으로 테이블을 변환할 때 첫 번째 단계는 소스 데이터베이스에서 Oracle의 테이블 만들기 문을 추출하는 것입니다. 다음 샘플 쿼리는 HR 스키마에서 위치 테이블의 DDL을 추출합니다.
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
전체 출력에는 스토리지 요소, 색인, 테이블 공간 정보가 포함됩니다. 이러한 추가 요소는 PostgreSQL용 CREATE TABLE 문에서 지원되지 않기 때문에 생략되었습니다.
DDL이 추출된 후 이름을 둘러싼 따옴표를 제거하고 Oracle에서 PostgreSQL로의 데이터 유형 변환 테이블에 따라 테이블 변환을 수행합니다. 각 열 데이터 유형을 있는 그대로 변환할 수 있는지 확인합니다. 지원되지 않는 경우 변환 테이블에 따라 다른 데이터 유형을 선택합니다. 예를 들어 다음은 위치 테이블의 변환된 DDL입니다.
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)
CREATE TABLE AS SELECT(CTAS) 문은 기존 테이블을 기반으로 새 테이블을 만드는 데 사용됩니다. 열 이름과 열 데이터 유형만 복사되고 제약조건과 색인은 복사되지 않습니다. PostgreSQL은 CTAS 기능의 ANSI SQL 표준을 지원하며 Oracle CTAS 문과 호환됩니다.
Oracle 12c 표시되지 않는 열
PostgreSQL은 보이지 않는 열을 지원하지 않습니다. 이 문제를 해결하려면 표시된 열만 있는 뷰를 만듭니다.
테이블 제약조건
Oracle은 ALTER TABLE 명령어를 사용하여 테이블 생성 시 또는 테이블 생성 이후에 정의할 수 있는 6가지 유형의 테이블 제약조건을 제공합니다. Oracle 제약조건 유형은 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT
NULL, REF입니다. 또한 Oracle에서는 다음 옵션을 통해 사용자가 제약조건 상태를 제어할 수 있습니다.
INITIALLY IMMEDIATE: 각 후속 SQL 문 종료 시 제약조건을 확인합니다(기본 상태).DEFERRABLE/NOT DEFERRABLE:COMMIT문을 제출할 때까지 후속 트랜잭션에서SET CONSTRAINT절을 사용도록 설정합니다.INITIALLY DEFERRED: 후속 트랜잭션 종료 시 제약조건을 확인합니다.VALIDATE/NO VALIDATE: 오류에 대한 새 행 또는 수정된 행을 확인합니다(또는 의도적으로 확인하지 않습니다). 이러한 매개변수는 제약조건이ENABLED인지DISABLED인지에 따라 다릅니다.ENABLED/DISABLED: 생성 후 제약조건의 시행 여부를 지정합니다(기본값:ENABLED).
PostgreSQL도 PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, EXCLUDE의 6가지 유형의 테이블 제약조건을 지원합니다. 하지만 Oracle과 PostgreSQL의 제약조건 유형 간에 다음과 같은 주요 차이점이 있습니다.
- PostgreSQL은 Oracle의
REF제약조건을 지원하지 않습니다. - PostgreSQL은 외래 키 제약조건의 참조 열에 색인을 자동으로 만들지 않습니다. 색인이 필요한 경우 참조 열에 별도의
CREATE INDEX문이 필요합니다. - PostgreSQL은 Oracle의
ON DELETE SET NULL절을 지원하지 않습니다. 이 절은 상위 테이블의 레코드가 삭제될 때 하위 테이블의 종속 값을NULL로 설정하도록 Oracle에 지시합니다. CHECK OPTION을 제외하고VIEWS의 제약조건은 지원되지 않습니다.- PostgreSQL은 제약조건 사용 중지를 지원하지 않습니다. PostgreSQL은 새 외래 키 또는 검사 제약조건이
ALTER TABLE문을 사용하여 추가되는 경우NOT VALID옵션을 지원합니다. 이 옵션은 하위 테이블의 기존 레코드에 대한 참조 무결성 검사를 건너뛰도록 PostgreSQL에 지시합니다.
다음 표에는 Oracle과 PostgreSQL의 제약조건 유형 간의 주요 차이점이 요약되어 있습니다.
| Oracle 제약조건 유형 | PostgreSQL용 Cloud SQL 지원 | PostgreSQL용 Cloud SQL 동일 |
|---|---|---|
PRIMARY KEY |
예 | PRIMARY KEY |
FOREIGN KEY |
예 | Oracle과 동일한 ANSI SQL 구문을 사용합니다. ON DELETE 절을 사용하여 FOREIGN
KEY 상위 레코드 삭제의 경우를 처리할 수 있습니다. PostgreSQL은 상위 테이블에서 데이터가 삭제되고 하위 테이블을 FOREIGN KEY 제약조건으로 참조하는 세 가지 옵션을 제공합니다.
PostgreSQL은 Oracle의 ON DELETE SET NULL 절을 지원하지 않습니다. ON UPDATE 절을 사용하여 FOREIGN
KEY 상위 레코드 업데이트의 경우를 처리할 수 있습니다.PostgreSQL은 FOREIGN KEY 제약조건 업데이트 이벤트를 처리하는 세 가지 옵션을 제공합니다.
PostgreSQL은 외래 키 제약조건의 참조 열에 색인을 자동으로 만들지 않습니다. |
UNIQUE |
예 | 기본적으로 UNIQUE 색인을 만듭니다. |
CHECK |
예 | CHECK |
NOT NULL |
예 | NOT NULL |
REF |
아니요 | 지원되지 않음 |
DEFERRABLE/NOT DEFERRABLE |
예 | DEFERRABLE/NOT DEFERRABLE |
INITIALLY IMMEDIATE |
예 | INITIALLY IMMEDIATE |
INITIALLY DEFERRED |
예 | INITIALLY DEFERRED |
VALIDATE/NO VALIDATE |
아니요 | 지원되지 않음 |
ENABLE/DISABLE |
아니요 | 기본적으로 사용 설정됩니다. 새 외래 키 또는 검사 제약조건이 ALTER
TABLE 문을 사용하여 테이블에 추가되는 경우 NOT VALID 옵션을 사용하여 기존 레코드에 대한 참조 무결성 검사를 건너뜁니다. |
| 뷰의 제약조건 | 아니요 | 지원되지 않습니다(VIEW WITH CHECK OPTION는 예외). |
| 제약조건 메타데이터 | Oracle | DBA_CONSTRAINTS |
| PostgreSQL | INFORMATION_SCHEMA.TABLE_CONSTRAINTS |
가상 열과 생성 열
Oracle의 가상 열은 다른 열의 계산 결과를 기반으로 합니다. 이러한 열은 일반 열로 표시되지만 열 값은 Oracle 데이터베이스 엔진의 계산에서 파생되고, 그 값은 데이터베이스에 저장되지 않습니다. 가상 열은 제약조건, 색인, 테이블 파티션 나누기, 외래 키와 함께 사용할 수 있지만 DML(Data Manipulation Language) 작업을 통해 조작할 수 없습니다.
PostgreSQL의 생성 열은 기능 측면에서 Oracle의 가상 열과 유사합니다. 그러나 Oracle과 달리 PostgreSQL의 생성 열은 저장되고 각 생성 열에 데이터 유형을 지정해야 합니다. 즉, 이러한 열은 일반 열인 것처럼 스토리지를 사용합니다.
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
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과 PostgreSQL은 다양한 애플리케이션에 사용할 수 있는 다양한 색인 생성 알고리즘과 색인 생성 유형을 제공합니다. 다음은 PostgreSQL에서 사용 가능한 색인 생성 알고리즘의 목록입니다.
| 색인 알고리즘 | 설명 |
|---|---|
| B-tree |
|
| 해시 |
|
| 진 |
|
| GiST |
|
| SP-GiST |
|
| BRIN |
|
다음 표에서는 Oracle과 PostgreSQL 간의 색인 유형을 비교합니다.
| Oracle 색인 | 설명 | PostgreSQL에서 지원 | PostgreSQL 상응 |
|---|---|---|---|
| 비트맵 색인 | 각 색인 키의 비트맵을 저장하며 OLAP 워크로드의 빠른 데이터 검색을 제공하는 데 가장 적합합니다. | 아니요 | 해당 사항 없음 |
| B-tree 색인 | 가장 일반적인 색인 유형이며 다양한 워크로드에 적합하고 ASC|DESC 정렬로 구성할 수 있습니다. |
예 | B-tree 색인 |
| 복합 색인 | 데이터 검색 성능을 높이기 위해 2개 이상의 열에 생성됩니다. 색인 내부의 열 순서에 따라 액세스 경로가 결정됩니다. | 예 | 다중 열 색인 다중 열 색인을 만들 때 최대 32개 열을 지정할 수 있습니다. |
| 함수 기반 색인 | 테이블 열의 값에 적용된 함수의 출력을 저장합니다. | 예 | 표현식의 색인 |
| 고유 색인 | 열 단위로 색인 생성된 값에 UNIQUE 제약조건을 적용하는 B-tree 색인입니다. |
예 | 고유 색인 |
| 애플리케이션 도메인 색인 | 오디오/동영상 데이터, LOB 데이터, 기타 비텍스트 유형과 같은 비관계형 데이터의 색인을 생성하는 데 적합합니다. | 아니요 | 해당 없음 |
| 표시되지 않는 색인 | 옵티마이저의 의사 결정에 영향을 주지 않으면서 색인을 관리, 유지, 테스트할 수 있게 해주는 Oracle 기능입니다. | 아니요 | 다른 해결책으로, 진행 중인 활동에 영향을 주지 않고 테스트 목적으로 읽기 복제본에 추가 색인을 만들 수 있습니다. |
| 색인 정리된 테이블 | 데이터가 테이블 및 색인 수준에서 저장되는 방식을 제어하는 색인 유형입니다. | 아니요 | PostgreSQL은 색인 구성 테이블을 지원하지 않습니다. CLUSTER 문은 지정된 색인에 따라 Table Storage를 구성하도록 PostgreSQL에 지시합니다. Oracle의 색인 구성 테이블과 유사한 용도로 사용됩니다. 하지만 클러스터링은 일회성 작업이며 PostgreSQL은 이후 업데이트 시 테이블 구조를 유지하지 않습니다. 수동 주기적 클러스터링이 필요합니다. |
| 로컬 및 전역 색인 | Oracle 데이터베이스에서 파티션을 나눈 테이블의 색인을 생성하는 데 사용됩니다. 각 색인은 LOCAL 또는 GLOBAL로 정의됩니다. |
아니요 | PostgreSQL 파티션 작업 색인은 Oracle 로컬 색인과 기능이 동일합니다. 즉, 색인은 파티션 수준에서 정의되며 전역 수준은 지원되지 않습니다. |
| 파티션을 나눈 테이블의 부분 색인(Oracle 12c) | 테이블 파티션의 하위 집합에 대한 색인을 만듭니다. LOCAL 및 GLOBAL을 지원합니다. |
예 | PostgreSQL의 파티션 나누기는 하위 테이블을 상위 테이블에 연결하여 작동합니다. 하위 테이블의 하위 집합에만 색인을 만들 수 있습니다. |
CREATE/DROP INDEX |
색인 생성 및 삭제에 사용되는 명령어입니다. | 예 | PostgreSQL은 CREATE INDEX 명령어를 지원합니다. ALTER TABLE tableName ADD INDEX indexName
columnName도 지원합니다. |
ALTER INDEX ... REBUILD |
색인을 다시 구성합니다. 이로 인해 색인 생성된 테이블에 배타적 잠금을 유발될 수 있습니다. | 다른 구문 필요 | PostgreSQL은 REINDEX 문을 사용하여 색인 재구성을 지원합니다. 이 작업 중에는 테이블의 쓰기 작업이 잠기고 읽기만 허용됩니다. |
ALTER INDEX ... REBUILD ONLINE |
테이블에 배타적 잠금을 만들지 않고 색인을 다시 구성합니다. | 다른 구문 필요 | PostgreSQL은 REINDEX TABLE
CONCURRENTLY 문을 사용하여 동시 색인 재구성을 지원합니다. 이 모드에서 PostgreSQL은 최소 잠금을 사용하여 색인 재구성을 시도하지만 재구성을 완료하는 데 더 많은 시간과 리소스가 소요될 수 있는 단점이 있습니다. |
| 색인 압축 | 실제 색인 크기를 줄이는 기능입니다. | 아니요 | 해당 사항 없음 |
| 테이블 공간에 색인 할당 |
디스크 I/O 병목 현상을 줄이기 위해 테이블 데이터와 별도의 디스크에 저장할 수 있는 색인 테이블 공간을 만듭니다. | 아니요 | PostgreSQL에서는 사용자 정의 테이블 공간에 색인을 만들 수 있지만 PostgreSQL용 Cloud SQL에서는 테이블 공간을 만들 수 없으며 기본 테이블 공간에도 색인을 빌드해야 합니다. |
| 색인 메타데이터(테이블/뷰) | Oracle | DBA_INDEXES |
|
| PostgreSQL | pg_catalog.pg_index |
색인 변환 고려사항
대부분의 경우 Oracle 색인은 PostgreSQL의 B-tree 색인으로 변환할 수 있습니다. 이 색인 유형은 가장 일반적으로 사용되는 색인 유형이기 때문입니다.
Oracle 데이터베이스에서와 마찬가지로 색인은 테이블의 PRIMARY KEY 필드에 자동으로 생성됩니다. 마찬가지로 UNIQUE 색인은 UNIQUE 제약조건이 있는 필드에 자동으로 생성됩니다. 또한 보조 색인은 표준 CREATE INDEX 문을 사용하여 생성됩니다.
다음 예시에서는 색인이 생성된 여러 필드가 있는 Oracle 테이블을 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=>
테이블 파티션 나누기
Oracle과 PostgreSQL 모두 대규모 테이블을 분할하기 위한 파티션 나누기 기능을 제공합니다. 이렇게 하려면 테이블을 물리적으로 작은 부분으로 분류합니다. 그러면 각 부분에 행의 수평 하위 집합이 포함됩니다. 파티션을 나눈 테이블은 상위 테이블이라고 하며, 해당 행이 파티션에 실제로 저장됩니다. PostgreSQL에서 Oracle의 모든 파티션 유형이 지원되지는 않지만 PostgreSQL은 가장 일반적인 파티션 유형을 지원합니다.
다음 섹션에서는 PostgreSQL에서 지원하는 파티션 유형을 설명하고 각각의 유형에 해당하는 파티션을 만드는 방법을 보여주는 예시를 제공합니다.
RANGE 파티션 나누기
이 유형의 파티션은 지정된 범위 내에 있는 열 값을 기준으로 파티션에 행을 할당합니다. 각 파티션에는 파티션 나누기 표현식 값이 지정된 범위 내에 있는 행이 포함됩니다. 파티션 간에 범위가 겹치지 않는다는 점에 유의해야 합니다.
예시
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 파티션 나누기
RANGE 파티션 나누기와 마찬가지로 LIST 파티션 나누기는 사전 정의된 값 내에 있는 열 값을 기준으로 파티션에 행을 할당합니다. 각 파티션에 나타나는 키 값이 LIST 파티션에 대해 명시적으로 나열됩니다.
예시
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 파티션 나누기
HASH 파티션 나누기는 모든 파티션 간에 데이터를 고르게 배포하는 것이 목표일 때 가장 적합합니다. 열 값(또는 해싱될 열 값을 기반으로 하는 표현식)과 행 값이 해당 해시 값에 해당하는 파티션에 할당됩니다. 해시 값은 파티션에 고유하게 할당되어야 하며 삽입된 모든 값은 정확히 하나의 파티션에 매핑되어야 합니다.
예시
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);
파티션 연결 또는 분리
PostgreSQL에서는 파티션을 상위 테이블에 추가하거나 삭제할 수 있습니다. 분리된 파티션은 나중에 동일한 테이블에 다시 연결할 수 있습니다. 또한 파티션을 다시 연결할 때 새 파티션 나누기 조건을 지정하여 파티션 경계를 조정할 수 있습니다.
예시
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;
다음 표에서는 어떠한 면에서 Oracle 및 PostgreSQL용 Cloud SQL 파티션 유형이 동등하고, 어떠한 점에서 변환이 권장되는지를 설명합니다.
| Oracle 파티션 유형 | PostgreSQL에서 지원 | PostgreSQL 구현 |
|---|---|---|
RANGE 파티션 |
예 | PARTITION BY RANGE |
LIST 파티션 |
예 | PARTITION BY LIST |
HASH 파티션 |
예 | PARTITION BY HASH |
SUB-PARTITIONING |
예 | 다중 레벨 파티션 나누기 |
| 간격 파티션 | 아니요 | 지원되지 않음 |
| 파티션 자문 전문가 | 아니요 | 지원되지 않음 |
| 선호 파티션 나누기 | 아니요 | 지원되지 않음 |
| 가상 열 기반 파티션 나누기 | 아니요 | 이 문제를 해결하려면 가상 열 표현식을 직접 사용하는 파티션 나누기를 고려하세요.
|
| 자동 목록 파티션 나누기 | 아니요 | 지원되지 않음 |
| 파티션 분할 |
아니요 | 이 문제를 해결하려면 테이블 파티션을 분리하거나 연결하여 파티션 경계를 조정하세요. |
| 파티션 교환 | 예 | DETACH / ATTACH PARTITION |
| 다중 유형 파티션 나누기(복합 파티션 나누기) | 예 | 다중 레벨 파티션 나누기 |
| 파티션 메타데이터 | Oracle | DBA_TAB_PARTITIONS |
| PostgreSQL | pg_catalog.pg_class |
다음은 두 플랫폼에서 테이블 파티션 생성을 나란히 비교하는 예시입니다. PostgreSQL은 CREATE TABLE 명령어의 PARTITIONS 절에서 테이블 공간 참조를 지원하지 않습니다.
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
);
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);
임시 테이블
Oracle 데이터베이스에서는 임시 테이블을 GLOBAL TEMPORARY TABLES라고 하며, PostgreSQL에서는 간단히 임시 테이블이라고 합니다. 임시 테이블의 기본 기능은 두 플랫폼 모두에서 동일합니다. 하지만 몇 가지 중요한 차이점이 있습니다.
- Oracle은 데이터베이스를 다시 시작한 후에도 반복 사용을 위해 임시 테이블 구조를 저장하지만, PostgreSQL은 세션 동안만 임시 테이블을 저장합니다.
- Oracle 데이터베이스의 임시 테이블은 적절한 권한이 있는 다른 사용자가 액세스할 수 있습니다. 반면에 PostgreSQL의 임시 테이블은 스키마가 정규화된 이름으로 임시 테이블을 참조하지 않는 한 생성된 세션 동안만 액세스할 수 있습니다.
- Oracle 데이터베이스에서
GLOBAL임시 테이블과LOCAL임시 테이블 간에는 테이블의 콘텐츠가 전역 콘텐츠인지 아니면 세션 콘텐츠인지 여부를 지정하는 차이가 있습니다. PostgreSQL에서 호환성을 위해GLOBAL키워드와LOCAL키워드가 지원되지만 데이터의 공개 상태에는 영향을 주지 않습니다. - 임시 테이블을 만들 때
ON COMMIT절이 생략된 경우 Oracle의 기본 동작은ON COMMIT DELETE ROWS입니다. 즉, Oracle 데이터베이스에서 각 커밋 후에 임시 테이블을 자릅니다. 반면에 PostgreSQL의 기본 동작은 각 커밋 후에 임시 테이블의 행을 보존하는 것입니다.
다음 표에서는 Oracle과 PostgreSQL용 Cloud SQL 간에 임시 테이블의 차이를 보여줍니다.
| 임시 테이블 기능 | Oracle 구현 | PostgreSQL 구현 |
|---|---|---|
| 구문 | CREATE GLOBAL TEMPORARY TABLE |
CREATE TEMPORARY TABLE |
| 접근성 | 여러 세션에서 액세스 가능 | 스키마가 정규화된 이름으로 참조하지 않는 한 작성자의 세션에서만 액세스할 수 있습니다. |
| 색인 지원 | 예 | 예 |
| 외래 키 지원 | 예 | 예 |
| DDL 보존 | 예 | 아니요 |
ON COMMIT 기본 작업 |
레코드가 삭제됨 | 레코드가 보존됨 |
ON COMMIT PRESERVE ROWS |
예 | 예 |
ON COMMIT DELETE ROWS |
예 | 예 |
ON COMMIT DROP |
아니요 | 예 |
ALTER TABLE 지원 |
예 | 예 |
| 통계 수집 | DBMS_STATS.GATHER_TABLE_STATS |
ANALYZE |
Oracle 12c GLOBAL_TEMP_TABLE_STATS |
DBMS_STATS.SET_TABLE_PREFS |
ANALYZE |
미사용 열
특정 열을 UNUSED로 표시하는 Oracle의 기능은 열 데이터를 물리적으로 삭제하지 않고 테이블에서 열을 삭제하는 데 자주 사용됩니다. 이는 큰 테이블에서 열을 삭제할 때 발생할 수 있는 높은 부하를 방지하기 위한 것입니다.
PostgreSQL에서 큰 열을 삭제해도 물리적 스토리지의 열 데이터는 삭제되지 않으므로 큰 테이블에서도 빠르게 수행됩니다. Oracle 데이터베이스에서와 같이 열을 UNUSED로 표시할 필요가 없습니다. 삭제된 열이 차지하는 공간은 새로운 DML 문으로 또는 후속 VACUUM 작업 중에 회수됩니다.
읽기 전용 테이블
읽기 전용 테이블은 ALTER TABLE 명령어를 사용하여 테이블을 읽기 전용으로 표시하는 Oracle 기능입니다. Oracle 12c R2에서는 파티션과 하위 파티션이 있는 테이블에 대해서도 이 기능을 사용할 수 있습니다. PostgreSQL은 동일한 기능을 제공하지 않지만 다음과 같은 두 가지 해결책이 있습니다.
- 특정 사용자에게 테이블에 대한
SELECT권한을 부여합니다. 단, 이 권한을 부여하면 테이블 소유자가 테이블에서 DML 작업을 수행하는 것을 방지할 수 없습니다. - PostgreSQL용 Cloud SQL 읽기 복제본을 만들고 사용자가 읽기 전용 테이블인 복제본 테이블을 사용하도록 안내합니다. 이 해결책은 기존 PostgreSQL용 Cloud SQL 인스턴스에 읽기 복제본 인스턴스를 추가해야 합니다.
DML 문에서 예외를 발생시키는 데이터베이스 트리거를 만듭니다. 예를 들면 다음과 같습니다.
-- 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=>
문자 집합
Oracle과 PostgreSQL은 단일 바이트 언어 지원과 멀티바이트 언어 지원을 포함하여 다양한 문자 집합, 대조, 유니코드를 지원합니다. 또한 동일한 인스턴스에 있는 PostgreSQL 데이터베이스는 고유한 문자 집합으로 구성할 수 있습니다. PostgreSQL에서 지원되는 문자 집합 목록을 참조하세요.
Oracle 데이터베이스에서 문자 집합은 데이터베이스 수준(Oracle 12g R1 이하) 또는 플러그인 가능한 데이터베이스 수준(Oracle 12g R2 이상)에서 지정됩니다. PostgreSQL에서는 새 PostgreSQL용 Cloud SQL 인스턴스가 생성될 때 기본 문자 집합이 지정됩니다. 해당 인스턴스 내에서 생성되는 각 데이터베이스는 다른 문자 집합으로 만들 수 있습니다. 정렬 순서 및 문자 분류를 테이블 열을 기준으로 지정할 수 있습니다.
예시
-- 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은 단순 뷰와 복잡한 뷰를 모두 지원합니다. 뷰 만들기 옵션의 경우 Oracle과 PostgreSQL 간에 몇 가지 차이점이 있습니다. 다음 표는 이러한 차이점을 보여줍니다.
| Oracle 뷰 기능 | 설명 | PostgreSQL용 Cloud SQL 지원 | 변환 고려사항 |
|---|---|---|---|
FORCE |
소스 테이블/뷰가 있는지 확인하지 않고 뷰를 만듭니다. | 아니요 | 이에 상응하는 옵션이 없습니다. |
CREATE OR REPLACE |
존재하지 않는 뷰를 만들거나 기존 뷰를 덮어씁니다. | 예 | PostgreSQL은 뷰에 CREATE OR REPLACE 명령어를 지원합니다. |
WITH CHECK OPTION |
뷰에 대한 DML 작업을 수행할 때 적용 수준을 지정합니다. | 예 | 기본값은 CASCADED이며, 참조 뷰도 평가하게 됩니다.LOCAL 키워드는 현재 뷰만 평가하게 합니다. |
WITH READ-ONLY |
뷰에 대한 읽기 작업만 허용합니다. DML 작업은 금지됩니다. | 아니요 | 해결책은 모든 사용자에게 뷰에 대한 SELECT 권한을 부여하는 것입니다. |
VISIBLE | INVISIBLE(Oracle 12c) |
뷰 기반 열을 사용자에게 표시할지 여부를 지정합니다. | 아니요 | 필수 열만 있는 VIEW를 만듭니다. |
다음 변환 예시는 뷰를 Oracle에서 Cloud SQL PostgreSQL로 변환하는 방법을 보여줍니다.
-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;
-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
SET salary=salary+1000;
postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres-> FIRST_NAME,
postgres-> LAST_NAME,
postgres-> SALARY,
postgres-> DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;
-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;
-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;
ERROR: new row violates check option for view "vw_emp_dept100"
DETAIL: Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).
뷰 액세스 관리
뷰 소유자는 기본 테이블에 대한 권한이 있어야 뷰를 만들 수 있습니다. 뷰 사용자는 뷰에 대한 적절한 SELECT 권한이 필요합니다.
또한 뷰를 통해 DML 작업을 수행할 때 뷰에 대한 적절한 INSERT, UPDATE, DELETE 권한이 필요합니다. 두 경우 모두 사용자는 기본 테이블에 대한 권한은 필요하지 않습니다.
다음 단계
- PostgreSQL 사용자 계정에 대해 자세히 알아보기
- Google Cloud에 대한 참조 아키텍처, 다이어그램, 권장사항 살펴보기 Cloud 아키텍처 센터 살펴보기