RDS for PostgreSQL에서 사용자 지정 캐스트 관리
PostgreSQL의 유형 캐스팅은 값을 한 데이터 유형에서 다른 데이터 유형으로 변환하는 프로세스입니다. PostgreSQL은 대다수 일반적인 변환을 위한 내장 캐스트를 제공하지만 사용자 지정 캐스트를 생성하여 특정 유형 변환의 작동 방식을 정의할 수도 있습니다.
캐스트는 한 데이터 유형에서 다른 데이터 유형으로 변환하는 방법을 지정합니다. 예를 들어 텍스트 '123'을 정수 123으로 변환하거나 숫자 45.67을 '45.67'로 변환합니다.
PostgreSQL 캐스팅 개념 및 구문에 대한 자세한 내용은 PostgreSQL CREATE CAST 설명서
RDS for PostgreSQL 버전 13.23, 14.20, 15.15, 16.11, 17.7 및 18.1부터 rds_casts 확장을 사용하여 내장 유형에 대한 추가 캐스트를 설치할 수 있으며, 사용자 지정 유형에 대한 자체 캐스트를 생성할 수도 있습니다.
rds_casts 확장 설치 및 사용
rds_casts 확장을 생성하려면 RDS for PostgreSQL DB 인스턴스에 rds_superuser로 연결하고 다음 명령을 실행합니다.
CREATE EXTENSION IF NOT EXISTS rds_casts;
지원되는 캐스트
사용자 지정 캐스트를 사용하려는 각 데이터베이스에서 확장을 생성합니다. 확장을 생성한 후 다음 명령을 사용하여 사용 가능한 모든 캐스트를 봅니다.
SELECT * FROM rds_casts.list_supported_casts();
이 함수는 사용 가능한 캐스트 조합(소스 유형, 대상 유형, 강제 컨텍스트 및 캐스트 함수)을 나열합니다. 예를 들어 implicit 캐스트로 text에서 numeric으로의 변환을 생성하려는 경우 다음 쿼리를 사용하여 캐스트를 생성할 수 있는지 확인할 수 있습니다.
SELECT * FROM rds_casts.list_supported_casts() WHERE source_type = 'text' AND target_type = 'numeric'; id | source_type | target_type | qualified_function | coercion_context ----+-------------+-------------+--------------------------------------+------------------ 10 | text | numeric | rds_casts.rds_text_to_numeric_custom | implicit 11 | text | numeric | rds_casts.rds_text_to_numeric_custom | assignment 13 | text | numeric | rds_casts.rds_text_to_numeric_custom | explicit 20 | text | numeric | rds_casts.rds_text_to_numeric_inout | implicit 21 | text | numeric | rds_casts.rds_text_to_numeric_inout | assignment 23 | text | numeric | rds_casts.rds_text_to_numeric_inout | explicit
rds_casts 확장은 각 캐스트에 대해 두 가지 유형의 변환 함수를 제공합니다.
-
_inout 함수 - PostgreSQL의 표준 I/O 변환 메커니즘을 사용하며, INOUT 메서드로 생성된 캐스트와 동일하게 동작합니다.
-
_custom 함수 - 변환 오류를 방지하기 위해 빈 문자열을 NULL 값으로 변환하는 등 엣지 사례를 처리하는 향상된 변환 로직을 제공합니다.
inout 함수는 PostgreSQL의 기본 캐스팅 동작을 재현하는 한편, custom 함수는 빈 문자열을 정수로 변환하는 등 표준 INOUT 캐스팅으로는 불가능한 시나리오를 처리하여 이 기능을 확장합니다.
캐스트 생성 또는 삭제
다음 두 가지 방법을 사용하여 지원되는 캐스트를 생성하고 삭제할 수 있습니다.
캐스트 생성
방법 1: 기본 CREATE CAST 명령 사용
CREATE CAST (text AS numeric) WITH FUNCTION rds_casts.rds_text_to_numeric_custom AS IMPLICIT;
방법 2: rds_casts.create_cast 함수 사용
SELECT rds_casts.create_cast(10);
create_cast 함수는 list_supported_casts() 출력에서 ID를 가져옵니다. 이 방법은 더 간단하며 올바른 함수와 컨텍스트 조합을 사용할 수 있도록 합니다. 이 ID는 여러 postgres 버전에서 동일하게 유지됩니다.
캐스트가 성공적으로 생성되었는지 확인하려면 pg_cast 시스템 카탈로그를 쿼리합니다.
SELECT oid, castsource::regtype, casttarget::regtype, castfunc::regproc, castcontext, castmethod FROM pg_cast WHERE castsource = 'text'::regtype AND casttarget = 'numeric'::regtype; oid | castsource | casttarget | castfunc | castcontext | castmethod --------+------------+------------+--------------------------------------+-------------+------------ 356372 | text | numeric | rds_casts.rds_text_to_numeric_custom | i | f
castcontext 열에는 EXPLICIT을 나타내는 e, ASSIGNMENT를 나타내는 a, IMPLICIT을 나타내는 i가 표시됩니다.
캐스트 삭제
방법 1: DROP CAST 명령 사용
DROP CAST IF EXISTS (text AS numeric);
방법 2: rds_casts.drop_cast 함수 사용
SELECT rds_casts.drop_cast(10);
drop_cast 함수는 캐스트를 생성할 때 사용된 것과 동일한 ID를 사용합니다. 이 방법을 통해 해당 ID로 생성된 정확한 캐스트를 삭제할 수 있습니다.
적절한 컨텍스트 전략을 사용하여 사용자 지정 캐스트 생성
정수 유형에 대해 여러 캐스트를 생성할 때 모든 캐스트가 IMPLICIT으로 생성되면 연산자 모호성 오류가 발생할 수 있습니다. 다음 예제에서는 텍스트에서 서로 다른 정수 너비로 두 개의 암시적 캐스트를 생성하여 이 문제를 보여줍니다.
-- Creating multiple IMPLICIT casts causes ambiguity postgres=> CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS IMPLICIT; CREATE CAST postgres=> CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT; CREATE CAST postgres=> CREATE TABLE test_cast(col int); CREATE TABLE postgres=> INSERT INTO test_cast VALUES ('123'::text); INSERT 0 1 postgres=> SELECT * FROM test_cast WHERE col='123'::text; ERROR: operator is not unique: integer = text LINE 1: SELECT * FROM test_cast WHERE col='123'::text; ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
이 오류는 PostgreSQL이 정수 열을 텍스트 값과 비교할 때 사용할 암시적 캐스팅을 결정할 수 없기 때문에 발생합니다. int4와 int8 암시적 캐스트가 모두 유효한 후보이므로 모호성이 발생합니다.
이 연산자 모호성을 방지하려면 작은 정수 너비에는 ASSIGNMENT 컨텍스트를 사용하고 큰 정수 너비에는 IMPLICIT 컨텍스트를 사용합니다.
-- Use ASSIGNMENT for smaller integer widths CREATE CAST (text AS int2) WITH FUNCTION rds_casts.rds_text_to_int2_custom(text) AS ASSIGNMENT; CREATE CAST (text AS int4) WITH FUNCTION rds_casts.rds_text_to_int4_custom(text) AS ASSIGNMENT; -- Use IMPLICIT for larger integer widths CREATE CAST (text AS int8) WITH FUNCTION rds_casts.rds_text_to_int8_custom(text) AS IMPLICIT; postgres=> INSERT INTO test_cast VALUES ('123'::text); INSERT 0 1 postgres=> SELECT * FROM test_cast WHERE col='123'::text; col ----- 123 (1 row)
이 전략에서는 int8 캐스트만 암시적이므로 PostgreSQL이 사용할 캐스트를 명확하게 결정할 수 있습니다.
