# Managing custom casts in RDS for PostgreSQL
**Type casting** in PostgreSQL is the process of converting a value from one data type to another. PostgreSQL provides built-in casts for many common conversions, but you can also create custom casts to define how specific type conversions should behave.
A cast specifies how to perform a conversion from one data type to another. For example, converting text `'123'` to integer `123`, or numeric `45.67` to text `'45.67'`.
For comprehensive information about PostgreSQL casting concepts and syntax, refer to the [PostgreSQL CREATE CAST Documentation](https://www.postgresql.org/docs/current/sql-createcast.html).
Starting with RDS for PostgreSQL versions 13.23, 14.20, 15.15, 16.11, 17.7, and 18.1, you can use the rds\$1casts extension to install additional casts for built-in types, while still being able to create your own casts for custom types.
**Topics**
+ [Installing and using the rds\$1casts extension](#PostgreSQL.CustomCasts.Installing)
+ [Supported casts](#PostgreSQL.CustomCasts.Supported)
+ [Creating or dropping casts](#PostgreSQL.CustomCasts.Creating)
+ [Creating custom casts with proper context strategy](#PostgreSQL.CustomCasts.BestPractices)
## Installing and using the rds\$1casts extension
To create the `rds_casts` extension, connect to your RDS for PostgreSQL DB instance as an `rds_superuser` and run the following command:
```
CREATE EXTENSION IF NOT EXISTS rds_casts;
```
## Supported casts
Create the extension in each database where you want to use custom casts. After creating the extension, use the following command to view all available casts:
```
SELECT * FROM rds_casts.list_supported_casts();
```
This function lists the available cast combinations (source type, target type, coercion context, and cast function). For example, if you want to create `text` to `numeric` as an `implicit` cast. You can use the following query to find if the cast is available to create:
```
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
```
The rds\$1casts extension provides two types of conversion functions for each cast:
+ *\$1inout functions* - Use PostgreSQL's standard I/O conversion mechanism, behaving identically to casts created with the INOUT method
+ *\$1custom functions* - Provide enhanced conversion logic that handles edge cases, such as converting empty strings to NULL values to avoid conversion errors
The `inout` functions replicate PostgreSQL's native casting behavior, while `custom` functions extend this functionality by handling scenarios that standard INOUT casts cannot accommodate, such as converting empty strings to integers.
## Creating or dropping casts
You can create and drop supported casts using two methods:
### Cast creation
**Method 1: Using native CREATE CAST command**
```
CREATE CAST (text AS numeric)
WITH FUNCTION rds_casts.rds_text_to_numeric_custom
AS IMPLICIT;
```
**Method 2: Using the rds\$1casts.create\$1cast function**
```
SELECT rds_casts.create_cast(10);
```
The `create_cast` function takes the ID from the `list_supported_casts()` output. This method is simpler and ensures you're using the correct function and context combination. This id is guaranteed to remain the same across different postgres versions.
To verify the cast was created successfully, query the pg\$1cast system catalog:
```
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
```
The `castcontext` column shows: `e` for EXPLICIT, `a` for ASSIGNMENT, or `i` for IMPLICIT.
### Dropping casts
**Method 1: Using DROP CAST command**
```
DROP CAST IF EXISTS (text AS numeric);
```
**Method 2: Using the rds\$1casts.drop\$1cast function**
```
SELECT rds_casts.drop_cast(10);
```
The `drop_cast` function takes the same ID used when creating the cast. This method ensures you're dropping the exact cast that was created with the corresponding ID.
## Creating custom casts with proper context strategy
When creating multiple casts for integer types, operator ambiguity errors can occur if all casts are created as IMPLICIT. The following example demonstrates this issue by creating two implicit casts from text to different integer widths:
```
-- 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.
```
The error occurs because PostgreSQL cannot determine which implicit cast to use when comparing an integer column with a text value. Both the int4 and int8 implicit casts are valid candidates, creating ambiguity.
To avoid this operator ambiguity, use ASSIGNMENT context for smaller integer widths and IMPLICIT context for larger integer widths:
```
-- 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)
```
With this strategy, only the int8 cast is implicit, so PostgreSQL can unambiguously determine which cast to use.