🔄

PostgreSQL Type Casting Functions

Complete reference for PostgreSQL type casting covering the CAST function, the :: shorthand operator, and implicit conversion rules. Every entry includes syntax, examples of casting between text, integer, numeric, date, boolean, and JSON types, and common casting errors. Updated for PostgreSQL 16.

14 functions

Jump to function

array cast boolean cast CAST format integer / int cast numeric cast pg_input_error_info pg_input_is_valid pg_input_is_valid pg_typeof text (cast to text)to_char to_date to_number

What are PostgreSQL Type Casting Functions?

PostgreSQL type casting functions convert values from one data type to another. The CAST(value AS type) function and the shorthand :: operator (e.g. '42'::integer or '2024-01-01'::date) are the two main approaches. Type casting is essential when comparing columns of different types, importing text data from CSV, or converting between date formats and Unix epoch timestamps.

array cast

PG 7.4+→ target type array

Casts an array to another array type by applying the element-wise cast.

DeveloperData Eng

Signatures

text[]::integer[] → integer[]

ARRAY[...]::type[] → type[]

Parameters

Parameter	Type	Description
expression	array	Source array

Examples

sql

SELECT ARRAY['1','2','3']::integer[];

→{1,2,3}

sql

SELECT '{2025-01-01,2025-06-15}'::date[];

→{2025-01-01,2025-06-15}

sql

SELECT ARRAY['3.14','2.71']::numeric[];

→{3.14,2.71}

Upcast integer array to bigint

sql

SELECT ARRAY[1,2,3]::bigint[];

→{1,2,3}

⚠Anti-Pattern— Casting a text array with invalid elements raises an error for the whole batch

If any single element of a text array is not a valid integer (e.g., empty string or non-numeric text), the entire `::integer[]` cast fails with no partial result.

✓ Instead: Validate or clean the array elements first using `array_remove` or `unnest` + CASE filtering before casting, to avoid whole-batch failures from a single dirty value.

When using `string_to_array` to split CSV input, cast the result to a typed array in one step: `string_to_array('1,2,3', ',')::integer[]`.

example

SELECT string_to_array(csv_ids, ',')::bigint[] AS id_array FROM imports;

→Properly typed bigint arrays from CSV

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

boolean cast

PG 7.4+→ boolean

Casts a value to boolean. Accepts: true/false, 't'/'f', 'yes'/'no', 'on'/'off', '1'/'0' for text input.

Developer

Signatures

expression::boolean → boolean

'true'::boolean → true

1::boolean → true

Parameters

Parameter	Type	Description
expression	text or integer	Value to convert to boolean

Examples

sql

SELECT 'yes'::boolean;

→true

sql

SELECT 0::boolean;

→false

sql

SELECT 'on'::boolean, 'off'::boolean;

→true | false

Case-insensitive text input

sql

SELECT 'TRUE'::boolean;

→true

Reverse cast: boolean back to integer

sql

SELECT false::integer;

→0

⚠Anti-Pattern— Casting arbitrary integers to boolean hides data quality issues

Only 0 and 1 cast cleanly to boolean. Integers like 2 or -1 raise an error when cast to boolean, which can surface unexpectedly in ETL pipelines that treat any non-zero value as 'true'.

✓ Instead: Use a CASE expression — `CASE WHEN flag_col <> 0 THEN true ELSE false END` — to explicitly handle the full integer domain before surfacing as boolean.

If a legacy table stores flags as 0/1 integers, cast them for proper boolean logic: `WHERE flag::boolean` is cleaner than `WHERE flag = 1`. Use `bool_and`, `bool_or` on the cast column in aggregates.

example

SELECT user_id, bool_and(active::boolean) FROM legacy_flags GROUP BY user_id;

→True if all flags are 1

CAST

PG 7.4+→ target_type

Converts a value to the specified data type. SQL-standard syntax. Equivalent to the :: operator in PostgreSQL.

DeveloperDBA

Signature

CAST ( expression AS type ) → type

Parameters

Parameter	Type	Description
expression	any	Value to convert
target_type	type name	The destination data type

Examples

sql

SELECT CAST('42' AS integer);

→42

Truncates toward zero

sql

SELECT CAST(3.7 AS integer);

→3

sql

SELECT CAST('2025-03-07' AS date);

→2025-03-07

Casting NULL to any type stays NULL

sql

SELECT CAST(NULL AS integer);

→NULL

sql

SELECT CAST(true AS integer);

→1

⚠Anti-Pattern— Casting in WHERE on indexed columns breaks index usage

Wrapping an indexed column in CAST inside a WHERE clause forces a sequential scan because PostgreSQL cannot use the index on the raw column.

✓ Instead: Cast the literal to match the column type, not the other way around. E.g., use `WHERE created_at = '2025-01-01'::date` rather than `WHERE CAST(created_at AS date) = '2025-01-01'`.

`CAST(x AS int)` is SQL standard and works in all databases. `x::int` is PostgreSQL-specific shorthand. Use `::` in scripts, `CAST` in code meant to run on multiple database engines.

example

SELECT '3.14'::numeric, CAST('3.14' AS numeric);

→3.14 | 3.14 (identical results)

format

PG 9.1+→ text

Formats arguments according to a format string. Similar to C's printf. Supports %s (text), %I (quoted identifier), %L (quoted literal).

DeveloperDBASecurity

Signature

format ( formatstr text [, formatarg any [, ...] ] ) → text

Parameters

Parameter	Type	Description
formatstr	text	Format string with %s, %I, %L, %% placeholders
formatarg	any	Arguments to substitute into placeholders

Examples

sql

SELECT format('Hello, %s!', 'world');

→Hello, world!

sql

SELECT format('INSERT INTO %I VALUES (%L)', 'my_table', 'val');

→INSERT INTO my_table VALUES ('val')

sql

SELECT format('%s is %s years old', name, age) FROM users;

→Alice is 30 years old

Positional argument references

sql

SELECT format('%1$s and %1$s again', 'repeat');

→repeat and repeat again

%I safely double-quotes the identifier

sql

SELECT format('DROP TABLE %I', 'user input; DROP TABLE secrets--');

→DROP TABLE "user input; DROP TABLE secrets--"

⚠Anti-Pattern— String concatenation for dynamic SQL enables SQL injection

Building dynamic SQL with `'SELECT * FROM ' || table_name || ' WHERE id = ' || user_input` is vulnerable to SQL injection because neither identifiers nor values are properly escaped.

✓ Instead: Always use `format('SELECT * FROM %I WHERE id = %L', table_name, user_input)`. The `%I` specifier double-quotes and escapes identifiers; `%L` single-quotes and escapes literal values.

`%I` quotes an identifier (table/column name) and `%L` quotes a literal value — both prevent SQL injection in dynamic SQL. Always use these instead of manual string concatenation when building queries in PL/pgSQL.

example

EXECUTE format('SELECT * FROM %I WHERE %I = %L', table_name, col, val);

→Safe dynamic SQL with properly quoted identifiers and values

integer / int cast

PG 7.4+→ integer

Casts a value to an integer type. Truncates floating-point values toward zero. Raises an error if the text cannot be parsed.

DeveloperDBA

Signatures

expression::integer → integer

expression::int → integer

expression::bigint → bigint

expression::smallint → smallint

Parameters

Parameter	Type	Description
expression	numeric, text, or boolean	Value to convert to integer

Examples

sql

SELECT '42'::integer;

→42

Truncates, does not round

sql

SELECT 3.9::integer;

→3

sql

SELECT true::integer;

→1

Exceeds integer range; use bigint

sql

SELECT 2147483648::bigint;

→2147483648

Round first, then cast, to avoid truncation surprise

sql

SELECT round(3.9)::integer;

→4

⚠Anti-Pattern— Casting float to integer silently truncates instead of rounding

Developers expecting rounding are surprised that `3.9::integer` returns 3, not 4. This is truncation toward zero, not rounding.

✓ Instead: Use `round(value)::integer` when you need nearest-integer rounding, and document the behaviour explicitly in code that converts financial or measurement values.

Casting text to integer raises an error if the string isn't a valid integer. Use a CASE expression or pg_input_is_valid() (PG16+) to check first, or catch the error in a DO block.

example

SELECT CASE WHEN value ~ '^-?[0-9]+
    
    
    
  

 THEN value::integer ELSE NULL END AS safe_int FROM raw_data;

→NULL for non-numeric strings, integer for valid ones

Common Gotchas

⚠

Integer division truncates silently — no error, no rounding

Dividing two integers in PostgreSQL always returns an integer, truncated toward zero. 5/2 = 2, not 2.5.

numeric cast

PG 7.4+→ numeric

Casts a value to exact decimal numeric type. Optionally specify precision (total digits) and scale (decimal places).

DeveloperData EngDBA

Signatures

expression::numeric → numeric

expression::numeric(p, s) → numeric with precision p and scale s

expression::decimal → numeric

Parameters

Parameter	Type	Description
expression	number, text	Value to convert
precision	integer	Total significant digits (optional)
scale	integer	Digits after decimal point (optional)

Examples

sql

SELECT 1::numeric / 3;

→0.33333333333333333333

sql

SELECT 3.14159::numeric(5,2);

→3.14

sql

SELECT avg(score)::numeric(5,1) FROM results;

→73.4

Rounds to fit scale, then checks precision

sql

SELECT '9999.999'::numeric(6,2);

→10000.00

⚠Anti-Pattern— Specifying numeric(p,s) with insufficient precision causes errors on valid data

Casting a value to `numeric(5,2)` means at most 5 total digits, 2 after the decimal point — so values above 999.99 raise 'numeric field overflow'. This is easy to hit in financial or scientific columns.

✓ Instead: Use `numeric` (unconstrained) for storage and intermediate calculations. Only apply `numeric(p,s)` at the display or output layer where you control the data range.

Integer division truncates: `5 / 2 = 2`. Cast at least one operand to numeric: `5::numeric / 2 = 2.5`. Always do this when computing percentages or rates from integer columns.

example

SELECT count(*) FILTER (WHERE paid)::numeric / count(*) * 100 AS paid_pct FROM invoices;

→Percentage as decimal, not truncated integer

Common Gotchas

⚠

Integer division truncates silently — no error, no rounding

Dividing two integers in PostgreSQL always returns an integer, truncated toward zero. 5/2 = 2, not 2.5.

pg_input_error_info

PG16+→ record (message text, detail text, hint text, sql_error_code text)

Returns detailed error information if the input string is invalid for the specified type, or a NULL record if the string is valid. Companion to pg_input_is_valid() — provides the error message that would have been raised. Added in PostgreSQL 16.

DeveloperDBA

Signature

pg_input_error_info ( string text, type text ) → record (message text, detail text, hint text, sql_error_code text)

Parameters

Parameter	Type	Description
string	text	The input string to validate
type	text	PostgreSQL type name as text

Examples

Detailed error for invalid integer

sql

SELECT * FROM pg_input_error_info('abc', 'integer');

→

message: invalid input syntax for type integer: "abc"
detail: NULL
hint: NULL
sql_error_code: 22P02

Out-of-range date value

sql

SELECT * FROM pg_input_error_info('2026-13-01', 'date');

→message: date/time field value out of range: "2026-13-01"

Valid input returns all NULLs

sql

SELECT * FROM pg_input_error_info('123', 'integer');

→(NULL,NULL,NULL,NULL)

Collect error messages for bad timestamp values

sql

SELECT col, (pg_input_error_info(col,'timestamptz')).message AS error FROM staging WHERE NOT pg_input_is_valid(col,'timestamptz');

→(rows)

Error code and message for all invalid numeric values

sql

SELECT col, (ei).sql_error_code, (ei).message FROM staging, pg_input_error_info(col,'numeric') ei WHERE (ei).message IS NOT NULL;

→(rows)

⚠Anti-Pattern— Using pg_input_error_info() for all rows when only checking validity

pg_input_error_info() returns a full record including error strings even for valid input (all NULLs). For a simple pass/fail check, pg_input_is_valid() is faster.

✓ Instead: Use pg_input_is_valid() for filtering; use pg_input_error_info() only when you need the error message text for logging or reporting.

pg_input_error_info() returns the exact error message PostgreSQL would have thrown. Use it to build data quality reports that show users not just which rows are invalid, but why — with the exact SQLSTATE code for programmatic error handling.

example

SELECT row_num, col_name, col_value, (pg_input_error_info(col_value, target_type)).message AS reason FROM staging_audit_view WHERE is_invalid;

→Audit report with row numbers, column names, values, and exact error reasons

pg_input_is_valid

PG16+→ boolean

Tests whether a text string is valid input for the specified PostgreSQL data type. Returns true if the string can be cast to the type without error, false otherwise. Does NOT raise an exception on invalid input. Added in PostgreSQL 16.

DeveloperDBA

Signature

pg_input_is_valid ( string text, type text ) → boolean

Parameters

Parameter	Type	Description
string	text	The input string to validate
type	text	PostgreSQL type name as text, e.g. 'integer', 'timestamptz', 'uuid'

Examples

Valid integer string

sql

SELECT pg_input_is_valid('123', 'integer');

→true

Not a valid integer

sql

SELECT pg_input_is_valid('abc', 'integer');

→false

Valid date string

sql

SELECT pg_input_is_valid('2026-03-16', 'date');

→true

Invalid UUID format

sql

SELECT pg_input_is_valid('not-a-uuid', 'uuid');

→false

Find all non-numeric values in a staging column

sql

SELECT col, pg_input_is_valid(col, 'numeric') AS is_numeric FROM staging_data WHERE NOT pg_input_is_valid(col, 'numeric');

→(rows with bad values)

⚠Anti-Pattern— Using TRY/CATCH or DO blocks to validate casting

Wrapping casts in DO blocks with EXCEPTION WHEN to test validity is slow (exceptions are expensive) and verbose.

✓ Instead: Use pg_input_is_valid(col, 'integer') directly in WHERE or CASE — no exception overhead, works inline in queries.

pg_input_is_valid() lets you filter or flag rows with invalid values BEFORE a batch INSERT/CAST fails. Use it in a WHERE clause or a CASE to route bad rows to an error table without stopping the whole load.

example

INSERT INTO clean_data SELECT id, col::integer FROM staging WHERE pg_input_is_valid(col,'integer');
INSERT INTO error_log SELECT id, col, 'not integer' FROM staging WHERE NOT pg_input_is_valid(col,'integer');

→Valid rows inserted, bad rows logged separately

pg_input_is_valid

PG 16+→ boolean

Tests whether a string is a valid input representation for the specified data type. Returns true if the string can be cast to that type without error, false otherwise — no exception is raised. Allows safe type validation before a cast operation. Added in PostgreSQL 16.

DeveloperData EngDBA

Signature

pg_input_is_valid ( string text, type text ) → boolean

Parameters

Parameter	Type	Description
string	text	Input string to test
type	text	Target data type name (e.g., 'integer', 'date', 'uuid', 'inet', 'numeric')

Examples

sql

SELECT pg_input_is_valid('42', 'integer');

→true

sql

SELECT pg_input_is_valid('not_a_number', 'integer');

→false — invalid, no exception raised

sql

SELECT pg_input_is_valid('2025-03-16', 'date');

→true

sql

SELECT pg_input_is_valid('192.168.1.999', 'inet');

→false — octet 999 out of range

sql

SELECT raw_value, CASE WHEN pg_input_is_valid(raw_value, 'numeric') THEN raw_value::numeric ELSE NULL END AS safe_num FROM import_staging;

→NULL for invalid values, parsed numeric for valid ones

⚠Anti-Pattern— Using exception blocks in PL/pgSQL to test cast validity

The traditional pre-PG16 pattern for safe casting was wrapping the cast in a DECLARE/BEGIN/EXCEPTION block. This is verbose, has overhead from exception handling, and cannot be used in plain SQL. Use `pg_input_is_valid` in both SQL and PL/pgSQL for cleaner, faster validation.

✓ Instead: -- Pre-PG16 (verbose): BEGIN value::integer; EXCEPTION WHEN invalid_text_representation THEN NULL; END -- PG16+ (clean): CASE WHEN pg_input_is_valid(value, 'integer') THEN value::integer ELSE NULL END

Pair `pg_input_is_valid` (boolean check) with `pg_input_error_info` (error details when invalid) for a complete validation pattern. Both functions use the actual type input parser, so they match exactly what a cast would accept.

example

SELECT v, pg_input_is_valid(v, 'date') AS ok, (pg_input_error_info(v, 'date')).message AS err FROM (VALUES ('2025-03-16'), ('2025-13-01'), ('not-a-date')) t(v);

→true/NULL | false/'date out of range' | false/'invalid input syntax for type date'

pg_typeof

PG 7.4+→ regtype

Returns the OID of the data type of the supplied expression, as a regtype. Useful for debugging type resolution.

DeveloperDBA

Signature

pg_typeof ( any ) → regtype

Parameters

Parameter	Type	Description
expression	any	Expression whose type to inspect

Examples

sql

SELECT pg_typeof(42);

→integer

sql

SELECT pg_typeof(42.0);

→numeric

sql

SELECT pg_typeof(now());

→timestamp with time zone

sql

SELECT pg_typeof(ARRAY[1,2,3]);

→integer[]

⚠Anti-Pattern— Assuming literal type without verification leads to overload surprises

Unqualified numeric literals like `1` are integer, not bigint or numeric. Passing them to overloaded functions may resolve to the wrong overload depending on which signatures exist.

✓ Instead: Use `pg_typeof(expr)` in a development query to confirm the inferred type, then add an explicit cast (e.g., `1::bigint`) to pin the overload you intend.

Use `pg_typeof` to understand what type PostgreSQL inferred for an expression. This is invaluable when debugging function overload resolution or unexpected implicit casts.

example

SELECT pg_typeof(1), pg_typeof(1.0), pg_typeof(1::bigint);

→integer | numeric | bigint

text (cast to text)

PG 7.4+→ text

Converts any value to its text representation. Equivalent to CAST(x AS text).

Developer

Signature

expression::text → text

Parameters

Parameter	Type	Description
expression	any	Value to convert to text

Examples

sql

SELECT 42::text;

→'42'

sql

SELECT now()::text;

→'2025-03-07 14:23:01.123456+00'

sql

SELECT ARRAY[1,2,3]::text;

→'{1,2,3}'

sql

SELECT true::text;

→'true'

sql

SELECT 3.14::numeric::text;

→'3.14'

⚠Anti-Pattern— Using ::text instead of to_char for dates and numbers in reports

Casting a timestamp or number to `::text` yields an internal canonical format (e.g., '2025-03-07 14:23:01.123456+00') that is rarely suitable for end-user display.

✓ Instead: Use `to_char(ts, 'YYYY-MM-DD HH24:MI')` or `to_char(num, 'FM9,999.00')` to produce display strings with controlled format, timezone, and precision.

Casting to `::text` gives a canonical PostgreSQL representation. Use `to_char()` when you need a specific format for display. `42::text` = '42' but `to_char(42, 'FM000')` = '042'.

example

SELECT status::text FROM orders WHERE id = 1;

→Enum or other type as plain text

to_char

PG 7.4+→ text

Formats a numeric value as a text string using a format pattern with tokens like 9, 0, FM, D, G, L, PR, etc.

DeveloperData Eng

Signatures

to_char ( numeric, text ) → text

to_char ( double precision, text ) → text

Parameters

Parameter	Type	Description
number	numeric or float	Value to format
format	text	Format pattern: 9=digit, 0=zero-padded digit, .=decimal, ,=group separator, L=locale currency, FM=fill mode, etc.

Examples

sql

SELECT to_char(1234567.89, 'FM9,999,999.99');

→1,234,567.89

sql

SELECT to_char(0.5, 'FM990.00%');

→50.00%

sql

SELECT to_char(-42, '9999PR');

→<42>

sql

SELECT to_char(1234, 'L9,999');

→$1,234 (locale-dependent)

⚠Anti-Pattern— Using ::text instead of to_char loses format control

Casting a number directly to text with `::text` produces PostgreSQL's canonical representation, which ignores locale, thousand separators, and decimal precision.

✓ Instead: Use `to_char(value, 'FM9,999,999.00')` whenever you need display formatting. Reserve `::text` for internal serialization where raw representation is acceptable.

Format patterns like `9,999.99` pad with spaces to the left. Add `FM` (fill mode) at the start to suppress leading/trailing spaces: `FM9,999,999.99`.

example

SELECT to_char(42.5, 'FM9,999.99') AS compact, to_char(42.5, '9,999.99') AS padded;

→'42.5' vs ' 42.50' (with padding)

to_date

PG 7.4+→ date

Converts a text string to a date using a format pattern.

DeveloperData Eng

Signature

to_date ( text, text ) → date

Parameters

Parameter	Type	Description
string	text	Date string to parse
format	text	Format pattern: YYYY, MM, DD, Mon, Day, etc.

Examples

sql

SELECT to_date('2025-03-07', 'YYYY-MM-DD');

→2025-03-07

sql

SELECT to_date('07/Mar/2025', 'DD/Mon/YYYY');

→2025-03-07

sql

SELECT to_date('20250307', 'YYYYMMDD');

→2025-03-07

sql

SELECT to_date('March 7, 2025', 'Month DD, YYYY');

→2025-03-07

⚠Anti-Pattern— Relying on implicit date casting misreads locale-specific strings

Implicit casting of a date string (`'07/03/2025'::date`) uses the `datestyle` session setting. In a deployment with mixed client locales this produces wrong dates silently.

✓ Instead: Always supply a format pattern explicitly via `to_date(str, 'DD/MM/YYYY')` so parsing is deterministic regardless of session settings.

When importing data with ambiguous date formats (e.g., '03/07/25' — US or EU format?), always specify the format explicitly in `to_date`. Don't rely on implicit casting which uses session settings.

example

SELECT to_date(raw_date, 'MM/DD/YY') FROM us_imports;

→Unambiguously parsed as US date format

to_number

PG 7.4+→ numeric

Converts a text string to a numeric value using a format pattern.

DeveloperData Eng

Signature

to_number ( text, text ) → numeric

Parameters

Parameter	Type	Description
string	text	String containing the number to parse
format	text	Format pattern matching the input (e.g., '999,999.99')

Examples

sql

SELECT to_number('1,234.56', '9,999.99');

→1234.56

sql

SELECT to_number('$1,234.56', 'L9,999.99');

→1234.56

sql

SELECT to_number('42%', '999%');

→42

S prefix handles sign

sql

SELECT to_number('-007', 'S999');

→-7

European decimal/group separator style

sql

SELECT to_number('1.234,56', '9G999D99');

→1234.56

⚠Anti-Pattern— Locale-dependent format strings cause silent mis-parses

`to_number` uses the session locale to interpret G (group separator) and D (decimal point). A format that works in a US locale may silently produce wrong values in a European locale where the roles of '.' and ',' are reversed.

✓ Instead: Always test `to_number` with explicit locale context, or pre-clean input with `regexp_replace` to normalise separators before parsing.

Use `to_number` to parse localized number strings from CSV imports or user input (e.g., '1.234,56' European format). The format pattern describes the input, not the output.

example

SELECT to_number(imported_price, 'FM999G999G999D99') AS price FROM csv_import;

→Parsed numeric values from formatted import

PostgreSQL Type Casting Functions

Jump to function

What are PostgreSQL Type Casting Functions?

array cast

Signatures

Parameters

Examples

Common Gotchas

boolean cast

Signatures

Parameters

Examples

CAST

Signature

Parameters

Examples

format

Signature

Parameters

Examples

integer / int cast

Signatures

Parameters

Examples

Common Gotchas

numeric cast

Signatures

Parameters

Examples

Common Gotchas

pg_input_error_info

Signature

Parameters

Examples

pg_input_is_valid

Signature

Parameters

Examples

pg_input_is_valid

Signature

Parameters

Examples

pg_typeof

Signature

Parameters

Examples

text (cast to text)

Signature

Parameters

Examples

to_char

Signatures

Parameters

Examples

to_date

Signature

Parameters

Examples

to_number

Signature

Parameters

Examples

Related PostgreSQL Categories