Queries for Casting - PostgreSQL

Casting converts a value from one data type to another. In PostgreSQL, you'll most often cast strings to dates, timestamps, and numbers so you can filter, sort, and calculate on them correctly.

Quick answer: Use '2023-07-14'::date or CAST('2023-07-14' AS date) for standard formats. Use TO_DATE() or TO_TIMESTAMP() with a format string for anything else.

SQL
Copy
0SELECT '2023-07-14'::date;
1SELECT CAST('2023-07-14' AS date);
2SELECT TO_DATE('14/07/2023', 'DD/MM/YYYY');

String to Date and Timestamp

Strings containing date or time information, from user inputs or logs, are not optimized for date-time operations in databases like PostgreSQL. Date and timestamp types sort chronologically, support date math, and reject invalid values like 2023-02-30.

How to convert string to date or timestamp

PostgreSQL has these date/time data types: date, time, timestamp, their time zone-aware versions, and interval for durations. You can cast a string to any of them.

There are three ways to convert string to date or timestamp in Postgres

1. Using the :: operator
2. Using the CAST() function
3. Using TO_DATE() and TO_TIMESTAMP() functions

Cast with the double colon operator

Convert a string to a date:

SQL
Copy
0SELECT '2023-07-14'::date;

Convert a string to a timestamp:

SQL
Copy
0SELECT '2023-07-14 10:30:00'::timestamp;

Casting a string with an offset to timestamp silently drops the offset. Use timestamptz to keep it.

SQL
Copy
0SELECT '2023-07-14 15:20:30+03'::timestamp;   -- 2023-07-14 15:20:30
1SELECT '2023-07-14 15:20:30+03'::timestamptz; -- 2023-07-14 12:20:30+00 (UTC session)

Cast with the CAST function

Convert a string to a date:

SQL
Copy
0SELECT CAST('2023-07-14' AS date);

Convert a string to a timestamp:

SQL
Copy
0SELECT CAST('2023-07-14 10:30:00' AS timestamp);

Parse custom formats with TO_DATE and TO_TIMESTAMP

Use these functions when the string format isn't in the default YYYY-MM-DD or doesn't match PostgreSQL's expected timestamp structure. You pass the input's format as the second argument.

String to date conversion using to_date(date, format)

TO_DATE(text, text) converts a string to a date according to the given format.

Usage examples - to_date:

Converting a non-standard date string:

SQL
Copy
0SELECT TO_DATE('14 July, 2023', 'DD Month, YYYY');

Converting a day of the month - month - year format:

SQL
Copy
0SELECT TO_DATE('14-07-2023', 'DD-MM-YYYY');

Converting month name, day, and year:

SQL
Copy
0SELECT TO_DATE('July 14, 2023', 'Month DD, YYYY');

Converting abbreviated month:

SQL
Copy
0SELECT TO_DATE('14 Jul 2023', 'DD Mon YYYY');

Year and day of the year:

SQL
Copy
0SELECT TO_DATE('2023, 195', 'YYYY, DDD');

Year, week of the year, and day of the week (ISO 8601 format):

SQL
Copy
0SELECT TO_DATE('2023-W28-5', 'IYYY-"W"IW-ID');

Custom format with text:

SQL
Copy
0SELECT TO_DATE('Day: 14 of Month: July in Year: 2023', '"Day:" DD "of Month:" Month "in Year:" YYYY');

String to timestamp conversion using to_timestamp(datetime, format)

TO_TIMESTAMP(text, text) converts a string to a timestamp with time zone according to the given format.

Usage examples - to_timestamp:

Convert a string with specific timestamp format:

SQL
Copy
0SELECT TO_TIMESTAMP('14/07/2023 10:30:00', 'DD/MM/YYYY HH24:MI:SS');

Converting day-month-year with time:

SQL
Copy
0SELECT TO_TIMESTAMP('14-07-2023 15:20:30', 'DD-MM-YYYY HH24:MI:SS');

Using abbreviated month and 12-hour format:

SQL
Copy
0SELECT TO_TIMESTAMP('14 Jul 2023 03:20:30 PM', 'DD Mon YYYY HH:MI:SS AM');

Year, day of the year, and time:

SQL
Copy
0SELECT TO_TIMESTAMP('2023, 195 15:20:30', 'YYYY, DDD HH24:MI:SS');

Including timezone offset:

SQL
Copy
0SELECT TO_TIMESTAMP('14-07-2023 15:20:30 +03', 'DD-MM-YYYY HH24:MI:SS TZH');

Using ISO 8601 format with time:

SQL
Copy
0SELECT TO_TIMESTAMP('2023-W28-5 15:20:30', 'IYYY-"W"IW-ID HH24:MI:SS');

Custom format with text and time:

SQL
Copy
0SELECT TO_TIMESTAMP('Day: 14 of Month: July in Year: 2023 at 15:20:30', '"Day:" DD "of Month:" Month "in Year:" YYYY "at" HH24:MI:SS');

Usage examples:

Which method is the best for date/timestamp casting

• For straightforward, brief type conversions, the :: operator is the shortest to write.
• For portability and clarity, especially in complex SQL scripts or functions, CAST() is a good choice.
• When dealing with strings in non-ISO formats that need to be transformed to date/timestamp types, choose TO_DATE() or TO_TIMESTAMP().

Common errors to watch out for

• Incorrect date format: Ensure the string format aligns with what PostgreSQL or the function expects.
• Mismatched data types: Be wary of strings that don't match the expected structure.
• Out-of-range values: '2023-02-30'::date raises a date/time field value out of range error.
• Ambiguous input: '03/04/2023'::date returns March 4 or April 3 depending on the DateStyle setting. Use TO_DATE() with an explicit format.

Avoid casting indexed columns in WHERE clauses

Casting a column inside a WHERE clause stops PostgreSQL from using a regular index on that column, unless you've created an expression index on the cast. The query falls back to a full table scan. Filter on a range instead:

SQL
Copy
0-- Can't use a regular index on created_at
1SELECT * FROM events WHERE created_at::date = '2026-10-01';
2
3-- Uses the index
4SELECT * FROM events
5WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02';

String to numeric

In PostgreSQL strings can be converted to integer or double using CAST function or the :: annotation. Both are equivalent and you can choose whichever you like.

Usage example:

If a column contains money data you should keep in mind that floating point numbers should not be used to handle money due to the potential for rounding errors.

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

To store a number as money, cast from numeric: 12.34::money. PostgreSQL has no direct cast from double precision to money.

Handle formatted and invalid numeric strings

Use to_number() for strings with group separators:

SQL
Copy
0SELECT to_number('1,234.56', '9G999D99'); -- 1234.56

A decimal string can't be cast straight to integer. Cast it to numeric first:

SQL
Copy
0SELECT '3.7'::integer;           -- ERROR: invalid input syntax for type integer
1SELECT '3.7'::numeric::integer;  -- 4

In PostgreSQL 16 and later, pg_input_is_valid() checks whether a cast will succeed without raising an error:

SQL
Copy
0SELECT pg_input_is_valid('abc', 'integer'); -- false
1SELECT pg_input_is_valid('42', 'integer');  -- true

Frequently asked questions

What's the difference between :: and CAST() in PostgreSQL?

They perform the same conversion. CAST() is standard SQL, so it's more portable. :: is PostgreSQL-specific shorthand.

How do I convert a string to a date in PostgreSQL?

For ISO strings like '2023-07-14', use '2023-07-14'::date. For other layouts, use TO_DATE() with a format string, such as TO_DATE('14/07/2023', 'DD/MM/YYYY').

Does TO_TIMESTAMP() return a time zone-aware value?

Yes. TO_TIMESTAMP() returns timestamp with time zone (timestamptz). Cast the result to timestamp if you need a value without a time zone.

How do I check if a string can be cast without an error?

In PostgreSQL 16 and later, use pg_input_is_valid(value, type). It returns true or false instead of raising an error.

Casting event data in a PostgreSQL warehouse

If you load event data into PostgreSQL with RudderStack, the warehouse destination only recognizes a specific set of ISO 8601 timestamp formats. Values that can't be cast to an existing column's data type go to the rudder_discards table. Use TO_TIMESTAMP() with an explicit format to normalize other date strings at query time.