User-Defined Data Type Conversion
Description
There are three data type conversion modes for PostgreSQL: implicit conversion, assignment conversion, and explicit conversion. They correspond to i (Implicit), a (Assignment), and e (Explicit) in the pg_cast system catalog.
- Implicit conversion: a conversion from low bytes to high bytes of the same data type, for example, from int to bigint
- Assignment conversion: a conversion from high bytes to low bytes of the same data type, for example, from smallint to int
- Explicit conversion: a conversion between different data types
How to Use
- Before converting data types, you can run the following command to check whether RDS for PostgreSQL supports data type conversion:
select * from pg_catalog.pg_cast ; oid | castsource | casttarget | castfunc | castcontext | castmethod -------+------------+------------+----------+-------------+------------ 11277 | 20 | 21 | 714 | a | f 11278 | 20 | 23 | 480 | a | f 11279 | 20 | 700 | 652 | i | f 11280 | 20 | 701 | 482 | i | f ......
- Run the following command to check whether int4 can be converted to text:
select * from pg_catalog.pg_cast where castsource = 'int4'::regtype and casttarget = 'bool'::regtype; oid | castsource | casttarget | castfunc | castcontext | castmethod -------+------------+------------+----------+-------------+------------ 11311 | 23 | 16 | 2557 | e | f (1 row)
The conversion is supported, and the conversion type is implicit conversion.
If no built-in conversion functions are available, customize a conversion function to support the conversion. For details, see User-Defined Data Type Conversion.
User-Defined Data Type Conversion
- Use double colons (::) to perform a forcible conversion.
select '10'::int,'2023-10-05'::date; int4 | date ------+------------ 10 | 2023-10-05 (1 row)
- Use the CAST function to convert the type.
select CAST('10' as int),CAST('2023-10-05' as date); int4 | date ------+------------ 10 | 2023-10-05 (1 row)
- Customize a data type conversion.
For details, see https://www.postgresql.org/docs/14/sql-createcast.html.
Adding a custom type conversion will affect the existing execution plans of RDS for PostgreSQL. Therefore, customizing type conversions are not recommended.
- Conversion between time and character types
- Conversion between boolean types and numeric types
- Conversion between numeric types and character types
Example: Convert text to date.
create or replace function public.text_to_date(text) returns date as $$ select to_date($1,'yyyy-mm-dd'); $$ language sql strict; create cast (text as date) with function public.text_to_date(text) as implicit; select text '2023-09-09' + 1; ?column? ------------ 2023-09-10 (1 row)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot