Type Conversion

12 Functions

Type conversion (also known as "casting") is the process of changing a value from one data type to another. This is essential for cleaning data, performing mathematical operations on text-based numbers, and formatting dates for display in your application's UI.

Table of Contents

CAST():: (PostgreSQL Cast)TO_CHAR()TO_NUMBER()TO_DATE()TO_TIMESTAMP()TO_JSON() / TO_JSONB()ARRAY_TO_STRING()STRING_TO_ARRAY()CONVERT()ASCII()CHR()
1.

CAST()

Description

The standard SQL function to convert an expression to a specific data type.

Syntax
CAST(expression AS target_type)
When to Use

Converting a numeric ID stored as text into an integer so you can sort it numerically.

Example Query
SELECT CAST('100' AS INTEGER);
💡 Pro Tip

This is the most "portable" way to cast, meaning the code will work on most other databases (MySQL, SQL Server) if you ever migrate from PostgreSQL.

2.

:: (PostgreSQL Cast)

Description

A PostgreSQL-specific shorthand for the CAST() function.

Syntax
expression::target_type
When to Use

Quickly casting a value inside a complex query for better readability.

Example Query
SELECT total_amount::NUMERIC FROM orders;
💡 Pro Tip

This is the "Postgres way." It is much more concise than CAST() and is used by almost every PostgreSQL developer for daily tasks.

3.

TO_CHAR()

Description

Converts a timestamp, interval, integer, or numeric value to a string based on a specific format.

Syntax
TO_CHAR(value, 'format')
When to Use

Formatting a raw price into a currency string (e.g., adding $ sign and 2 decimals).

Example Query
SELECT TO_CHAR(1250.5, 'L9,999.99');
💡 Pro Tip

Use this function when the data is leaving the database to be displayed directly on a user's screen or a report.

4.

TO_NUMBER()

Description

Converts a string to a numeric/decimal value using a specified format.

Syntax
TO_NUMBER(string, 'format')
When to Use

Converting a string like '$1,250.00' back into a number so you can add it to another value.

Example Query
SELECT TO_NUMBER('1,250.50', '9,999.99');
💡 Pro Tip

The format string must match the input string's structure (commas, periods) exactly for the conversion to succeed.

5.

TO_DATE()

Description

Converts a string to a date value based on a format.

Syntax
TO_DATE(string, 'format')
When to Use

Converting a user's text input like '20-Jan-2026' into a proper database Date type.

Example Query
SELECT TO_DATE('20260120', 'YYYYMMDD');
💡 Pro Tip

This is safer than using ::DATE because it allows you to handle non-standard date formats explicitly.

6.

TO_TIMESTAMP()

Description

Converts a string or a Unix epoch (seconds) to a full timestamp.

Syntax
TO_TIMESTAMP(double_precision)
When to Use

Converting a timestamp from a JavaScript API (Unix epoch) into a PostgreSQL format.

Example Query
SELECT TO_TIMESTAMP(1739274000);
💡 Pro Tip

This is the standard way to handle "seconds since 1970" data which is common in many web technologies.

7.

TO_JSON() / TO_JSONB()

Description

Converts a SQL value (like a row or an array) into a JSON object.

Syntax
TO_JSON(expression)
When to Use

Converting a whole table row into a JSON format to be sent to a mobile app.

Example Query
SELECT TO_JSON(ROW(1, 'XQLora', true));
💡 Pro Tip

TO_JSONB is generally preferred because it stores data in a binary format, making it faster to search and process later.

8.

ARRAY_TO_STRING()

Description

Converts an array of elements into a single string, separated by a delimiter.

Syntax
ARRAY_TO_STRING(array, delimiter)
When to Use

Turning a list of tags (stored as an array) into a comma-separated list for a UI badge.

Example Query
SELECT ARRAY_TO_STRING(ARRAY['SQL', 'Postgres'], ', ');
💡 Pro Tip

You can also provide a third argument as a replacement for NULL values in the array, e.g., ARRAY_TO_STRING(arr, ', ', 'N/A').

9.

STRING_TO_ARRAY()

Description

The inverse of ARRAY_TO_STRING; splits a string into a PostgreSQL array based on a delimiter.

Syntax
STRING_TO_ARRAY(string, delimiter)
When to Use

Parsing a CSV-style text column into individual searchable array elements.

Example Query
SELECT STRING_TO_ARRAY('SQL,Postgres,Data', ',');
💡 Pro Tip

Combined with UNNEST(), this is a powerful tool for breaking a single comma-separated cell into multiple rows.

10.

CONVERT()

Description

Primarily used in PostgreSQL for converting string encoding (e.g., from UTF-8 to LATIN1).

Syntax
CONVERT(string, 'src_encoding', 'dest_encoding')
When to Use

Handling legacy data from an old system that uses a different character encoding.

Example Query
SELECT CONVERT('text_in_utf8', 'UTF8', 'LATIN1');
💡 Pro Tip

Do not confuse this with SQL Server's CONVERT. In Postgres, use CAST or :: for data type changes, and CONVERT only for character set encoding.

11.

ASCII()

Description

Converts the first character of a string into its integer ASCII code.

Syntax
ASCII('character')
When to Use

Creating a sorting algorithm based on the raw character codes.

Example Query
SELECT ASCII('A'); -- Result: 65
💡 Pro Tip

This is useful for debugging character encoding issues or for creating custom sort orders.

12.

CHR()

Description

The inverse of ASCII(); converts an integer code into its corresponding character.

Syntax
CHR(integer)
When to Use

Inserting special characters or symbols (like line breaks or tabs) into a text report.

Example Query
SELECT CHR(65); -- Result: 'A'
💡 Pro Tip

Use CHR(10) for a newline and CHR(9) for a tab character when building formatted text outputs.