Data type conversion

Conversion functions and operators

Hologres supports the following type conversion functions and operators. For how each method behaves and for examples, see the sections that follow.

Conversion methods

Hologres provides three ways to convert data types. They differ mainly in what happens when a value cannot be converted.

For the data types that Hologres supports, see Data types.

Explicit conversion

Explicit conversion uses the CAST function or the :: operator. CAST(expr AS type) is the standard SQL form and expr::type is the PostgreSQL shorthand. The two are equivalent.

Common conversions:

-- String to integer. Both forms return 123.
SELECT CAST('123' AS int);
SELECT '123'::int;

-- Integer to string. Returns 123.
SELECT CAST(123 AS text);

-- String to NUMERIC. Returns 12.34.
SELECT CAST('12.34' AS numeric);

-- String to boolean. Returns t, the default display form of a PostgreSQL boolean.
SELECT CAST('true' AS boolean);

-- String to date. Returns 2026-08-13.
SELECT CAST('2026-08-13' AS date);

-- String to JSONB. Returns {"a": 1}.
SELECT CAST('{"a":1}' AS jsonb);

-- Boolean to integer. Returns 1.
SELECT CAST(true AS int);

-- Returns 2 (2.5 rounds to the nearest even integer, 2).
SELECT CAST(2.5::float8 AS int);

-- Returns 4 (3.5 rounds to the nearest even integer, 4).
SELECT CAST(3.5::float8 AS int);

-- Returns 3 (rounds half away from zero).
SELECT CAST(2.5::numeric AS int);

Fault-tolerant conversion

Fault-tolerant conversion uses the TRY_CAST function, which converts TEXT data to a target type. TRY_CAST returns NULL instead of raising an error when a value does not match the target type, which makes it a good fit for sources that contain malformed values. For the syntax, supported target types, and version requirements, see Data type conversion function.

TRY_CAST has the following type restrictions.

• The source type must be TEXT: applying TRY_CAST to any other source type raises ERROR: cannot try cast type <source type> to <target type> instead of returning NULL. For example, applying it to a NUMERIC column returns ERROR: cannot try cast type numeric to integer. Use CAST or :: for these cases.

• Some target types are not supported: TEXT and JSON target types raise ERROR: cannot try cast type ... instead of returning NULL.

The following example builds a table that contains malformed values.

CREATE TABLE type_convert_test (
  k int,
  v text
);

INSERT INTO type_convert_test VALUES
  (1, '123'),
  (2, 'abc'),
  (3, '12.7'),
  (4, ''),
  (5, NULL);

TRY_CAST returns NULL for every value it cannot convert.

SELECT k, v,
       TRY_CAST(v AS int) AS v_int,
       TRY_CAST(v AS numeric) AS v_numeric
FROM type_convert_test
ORDER BY k;

The result is as follows.

 k |  v   | v_int |     v_numeric
---+------+-------+-------------------
 1 | 123  |   123 | 123.000000000000000
 2 | abc  |       |
 3 | 12.7 |       |  12.700000000000000
 4 |      |       |
 5 |      |       |
(5 rows)

Keep the following behavior in mind when you use TRY_CAST.

• No truncation or rounding: a string with a decimal point returns NULL when converted to an integer rather than being rounded. In the preceding example, 12.7 converted to INT returns NULL. Convert to NUMERIC first if you need to keep the value.

• Out-of-range values also return NULL: a value that exceeds the range of the target type returns NULL instead of raising an error.

• Empty strings return NULL: an empty string is not valid input for numeric or datetime types, so the result is NULL.

• NUMERIC and DECIMAL results carry 15 decimal places: specify the precision explicitly if you need a fixed scale, such as TRY_CAST(v AS numeric)::numeric(10,2).

• Converting to DATE drops the time part: 2026-08-13 10:20:30 converted to DATE returns 2026-08-13.

Formatted conversion

Formatted conversion uses the TO_CHAR, TO_NUMBER, TO_DATE, and TO_TIMESTAMP functions. These functions are compatible with PostgreSQL and convert between strings and numbers or datetimes according to a template string: TO_CHAR renders a number or datetime as a formatted string, while TO_NUMBER, TO_DATE, and TO_TIMESTAMP parse a string into NUMERIC, DATE, and TIMESTAMPTZ respectively.

For the full syntax of the datetime functions, the template patterns they accept, and their version requirements, see Date and time functions.

-- Returns ' 125'. The leading position is reserved for the sign, so a positive number starts with a space.
SELECT TO_CHAR(125, '999');

-- Returns ' 0125'. Pads with zeros.
SELECT TO_CHAR(125, '0999');

-- Returns ' 1,234'. Groups thousands.
SELECT TO_CHAR(1234, '9,999');

-- Returns '125'. The FM prefix removes padding spaces.
SELECT TO_CHAR(125, 'FM999');

-- Returns '-125'. S shows the sign.
SELECT TO_CHAR(-125, 'S999');

-- Returns '125-'. MI places the minus sign at the end.
SELECT TO_CHAR(-125, '999MI');

-- L shows the currency symbol, which depends on the lc_monetary setting of the instance.
-- Returns '$ 125' when lc_monetary is en_US.UTF-8. Run SHOW lc_monetary; to check the current setting.
SELECT TO_CHAR(125, 'L999');

-- Returns ' 1.23e+03'. EEEE uses scientific notation.
SELECT TO_CHAR(1234.5, '9.99EEEE');

-- Returns -12454.8. Parses the thousands separator and the trailing minus sign.
SELECT TO_NUMBER('12,454.8-', '99G999D9S');

Which method to choose

• Prefer :: or CAST when the data is clean and column types are under your control. Malformed values fail fast instead of being hidden behind NULL.

• Use TRY_CAST for TEXT columns ingested from logs, tracking events, and similar sources. Pair it with a COUNT of NULL values to monitor how much of the data is malformed.

• Use TO_CHAR when you need to control the output format, such as zero padding, thousands separators, currency symbols, or scientific notation. Do not assemble the format with string concatenation.

• Conversion costs extra computation. For columns that are queried frequently, store the target type at write time rather than converting on every query.