🔄

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.

11 functions

Jump to function

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

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.

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)

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_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

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

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

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

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

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

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

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

PostgreSQL Type Casting Functions

Jump to function

What are PostgreSQL Type Casting Functions?

CAST

Signature

Parameters

Examples

to_char

Signatures

Parameters

Examples

to_number

Signature

Parameters

Examples

to_date

Signature

Parameters

Examples

pg_typeof

Signature

Parameters

Examples

text (cast to text)

Signature

Parameters

Examples

integer / int cast

Signatures

Parameters

Examples

numeric cast

Signatures

Parameters

Examples

boolean cast

Signatures

Parameters

Examples

array cast

Signatures

Parameters

Examples

format

Signature

Parameters

Examples

Related PostgreSQL Categories