Contents

• pg_try_cast
  • Why?
  • Usage
    • try_cast(value text, fallback anyelement) → anyelement
    • try_convert(fallback anyelement, value text) → anyelement
    • Real-world example: cleaning an import
    • SQL Server → PostgreSQL cheat sheet
  • Installation
    • Linux (Debian / Ubuntu)
    • Linux (RHEL / Rocky / Fedora)
    • macOS (Homebrew)
    • Docker (any OS, including Windows)
    • Enable it in your database
  • How it behaves
  • Limitations
  • Performance
  • Doesn’t PostgreSQL already have this?
  • Running the tests
  • Under the hood
  • Contributing
  • License

pg_try_cast

TRY_CAST and TRY_CONVERT for PostgreSQL. Convert a value to any type and get NULL (or a fallback of your choice) when it doesn’t fit, instead of an error that aborts the whole query.

🇧🇷 Leia em português

SELECT try_cast('42',  NULL::int);   -- 42
SELECT try_cast('abc', NULL::int);   -- NULL   (no error!)
SELECT try_cast('abc', 0);           -- 0      (fallback value)

Why?

One bad row is enough to break a query in PostgreSQL:

SELECT id, price::numeric FROM staging_import;
-- ERROR:  invalid input syntax for type numeric: "R$ 10,00"

SQL Server has TRY_CAST and TRY_CONVERT for this, and Oracle has CAST ... DEFAULT ... ON CONVERSION ERROR. PostgreSQL has neither yet. The usual workarounds are a regex in front of every cast, which is fragile and different for each type, or a PL/pgSQL function with an EXCEPTION block, which opens a subtransaction on every row and is slow.

pg_try_cast is a small C extension that does it properly. It works with any type that PostgreSQL can read from text, including integers, numerics, dates, timestamps, uuid, json/jsonb, arrays, ranges, enums, domains and composite types. It is also ~16× faster than the PL/pgSQL approach.

Usage

try_cast(value text, fallback anyelement) → anyelement

The type of fallback decides what value is converted to. Its value is what you get back when the conversion fails.

SELECT try_cast('2026-10-17', NULL::date);      -- 2026-10-17
SELECT try_cast('2026-02-30', NULL::date);      -- NULL
SELECT try_cast('99999999999', NULL::int);      -- NULL (out of range)
SELECT try_cast('n/a', 0);                      -- 0
SELECT try_cast('{"a":', '{}'::jsonb);          -- {}
SELECT try_cast(NULL, 0);                       -- NULL (NULL in, NULL out)

try_convert(fallback anyelement, value text) → anyelement

The same function with SQL Server’s argument order, to make migrations easier:

SELECT try_convert(NULL::int, '42');            -- 42
SELECT try_convert(NULL::int, 'abc');           -- NULL

Real-world example: cleaning an import

SELECT id,
       try_cast(raw_amount, NULL::numeric(12,2)) AS amount,
       try_cast(raw_date,   NULL::date)          AS paid_on,
       try_cast(raw_email,  NULL::varchar(120))  AS email
FROM   staging_import;

-- find the rows that need attention
SELECT * FROM staging_import
WHERE  raw_amount IS NOT NULL
AND    try_cast(raw_amount, NULL::numeric) IS NULL;

SQL Server → PostgreSQL cheat sheet

SQL Server	pg_try_cast
TRY_CAST(x AS int)	try_cast(x, NULL::int)
TRY_CAST(x AS decimal(10,2))	try_cast(x, NULL::numeric(10,2))
TRY_CONVERT(date, x)	try_convert(NULL::date, x)
ISNULL(TRY_CAST(x AS int), 0)	try_cast(x, 0)
TRY_CAST(x AS uniqueidentifier)	try_cast(x, NULL::uuid)

Installation

Requires PostgreSQL 16 or newer, because it relies on the “soft error” input functions added in PostgreSQL 16.

Linux (Debian / Ubuntu)

sudo apt install build-essential postgresql-server-dev-17   # use your PostgreSQL major version
git clone https://github.com/SEU-USUARIO/pg_try_cast.git
cd pg_try_cast
make
sudo make install

Linux (RHEL / Rocky / Fedora)

sudo dnf install gcc make postgresql17-devel   # package name may vary with your repo
make PG_CONFIG=/usr/pgsql-17/bin/pg_config
sudo make install PG_CONFIG=/usr/pgsql-17/bin/pg_config

macOS (Homebrew)

brew install postgresql@17
make PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config
make install PG_CONFIG=$(brew --prefix postgresql@17)/bin/pg_config

Docker (any OS, including Windows)

docker build -t pg_try_cast .                    # PostgreSQL 17 by default
docker build --build-arg PG_MAJOR=18 -t pg_try_cast:18 .
docker run -d --name pgtry -e POSTGRES_PASSWORD=postgres -p 5432:5432 pg_try_cast

Build error mentioning clang or llvm? Some PostgreSQL packages were built with JIT support and try to compile extra LLVM bitcode. You don’t need it. Build with make with_llvm=no && sudo make with_llvm=no install.

Enable it in your database

CREATE EXTENSION pg_try_cast;

The extension is marked trusted, so any user with CREATE privilege on the database can enable it. Superuser is not required.

How it behaves

• NULL in, NULL out. try_cast(NULL, 0) returns NULL, like CAST(NULL AS int).
• The input is text. Other types can be passed with ::text, for example try_cast(my_col::text, NULL::int).
• Type modifiers are respected, following the same rules as INSERT or COPY into a column of that type:
  • try_cast('abcd', NULL::varchar(3)) returns NULL because the value does not fit. A plain CAST would truncate it to 'abc'.
  • try_cast('123.456', NULL::numeric(5,2)) returns 123.46, rounded as numeric(5,2) does.
  • try_cast('12345.6', NULL::numeric(5,2)) returns NULL (overflow).
• Domain CHECK constraints are checked. If CREATE DOMAIN positive AS int CHECK (VALUE > 0), then try_cast('-5', NULL::positive) returns NULL.
• The fallback type must be known. try_cast('1', NULL) is an error. Write NULL::int.

Limitations

• A few input functions do not support soft errors, so they still raise an error:
  • regtype with a syntactically invalid type name (for example '###'). Unknown but valid names return NULL.
  • Data types from other extensions whose authors have not adopted the soft-error API.
• Only conversion from text is supported, because that is what dirty data usually is. Casts such as numeric → int with rounding are a different operation and are not covered here.

Performance

1,000,000 rows, 10% invalid, PostgreSQL 16, no parallel workers, median of 5 runs:

Method	Time
try_cast(v, NULL::int)	~110 ms
CASE WHEN pg_input_is_valid(v, 'int4') THEN v::int END	~117 ms
PL/pgSQL function with BEGIN ... EXCEPTION ... END	~1,790 ms

try_cast runs as fast as the built-in pg_input_is_valid() check, because both use the same mechanism. The advantage is that one call does both the check and the conversion, works with a fallback value and knows the type modifier of the target. Run it yourself with psql -f bench/benchmark.sql.

Doesn’t PostgreSQL already have this?

Not yet. PostgreSQL 16 added pg_input_is_valid() and pg_input_error_info(), which tell you whether a value is valid but do not convert it. The SQL-standard syntax CAST(x AS int DEFAULT 0 ON CONVERSION ERROR) has been discussed on pgsql-hackers, but it is not part of PostgreSQL 19 (as of RC 1). pg_try_cast works today, from PostgreSQL 16 onward.

Running the tests

make
sudo make install
make installcheck          # needs a running server you can connect to

Under the hood

A walkthrough of the C code, written for people who have never built a PostgreSQL extension, is available in Portuguese: docs/COMO_FUNCIONA.md.

Contributing

Issues and pull requests are welcome. Please add a regression test in test/sql/ and its expected output in test/expected/ for any change in behaviour.

License

Released under the PostgreSQL License.