By Rudderstack Team

Queries for Casting - PostgreSQL

String to Date and Timestamp

The TO_DATE function in PostgreSQL is used to converting strings into dates. Its syntax is TO_DATE(text, text) and the return type is date.

The TO_TIMESTAMP function converts string data into timestamps with timezone. Its syntax is to_timestamp(text, text).

Usage examples:

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:

Number::moneyAvoid converting it to float due to potential rounding errors

Get Started Image

Get started today

Start building smarter customer data pipelines today with RudderStack. Our solutions engineering team is here to help.