Comparing and Converting Data Types

Why do we even need all this — comparing and converting types? Imagine you have a string "42" in your table, and you want to compare it to the number 42. At first glance — looks the same. But for the database, these can be totally different things. PostgreSQL won’t just magically guess what you meant. And if you don’t get how it compares values of different types, you might get a weird result or even an error.

Same goes for conversions. Sometimes you need to turn text into a number to count how many of something you have. Or the other way around — you want to display a number nicely as a string. Or maybe you want to take a date and show it as "01.01.2025", because that’s what users are used to seeing.

Or here’s another example: you store an exact value in NUMERIC, but for scientific calculations you need to use FLOAT. In cases like this, you can’t avoid explicit conversion.

The good news is, PostgreSQL is really good at this stuff. It gives you flexible and clear tools for these tasks. The main thing is to know how to use them, and not be afraid to peek under the hood a bit. That’s what we’re gonna do here.

Comparing Data Types

PostgreSQL tries to be smart — if it sees you’re comparing, say, INTEGER and NUMERIC, it’ll calmly bring them to a common denominator and compare them. That’s fine, since both are numbers.

But if you try to compare values whose types really don’t mix — say, an actual TEXT column with an INTEGER — PostgreSQL will throw an error. The string literal '42' is a special case: it has type unknown and gets coerced to INTEGER by context, so SELECT '42' = 42; actually returns t (true). The error only shows up when both sides have firm, incompatible types.

What does this look like in practice? Here’s an example that will throw an error:

SELECT 'abc'::text = 42; -- ERROR: operator does not exist: text = integer

But this will work just fine:

SELECT '42'::INTEGER = 42; -- TRUE

Here we’re saying explicitly: “Please, first turn '42' into a number.” That’s what the ::Type syntax is for, which we already mentioned. PostgreSQL likes it when you talk to it clearly.

Comparing Numeric Types

Numeric data types (INTEGER, NUMERIC, REAL) are usually compatible with each other, so you can compare them without much hassle:

SELECT 42 = 42.0; -- TRUE
SELECT 42::REAL = 42.0; -- TRUE
SELECT 42.0::NUMERIC = 42; -- TRUE

Be careful with floating point numbers (REAL/DOUBLE PRECISION). Because of limited precision they can act weird. In PostgreSQL the literals 0.1, 0.2, 0.3 default to numeric (exact arithmetic), so the classic 0.1 + 0.2 ≠ 0.3 only shows up once you cast to float:

SELECT 0.1 + 0.2 = 0.3;                        -- t (true), numeric exact arithmetic
SELECT 0.1::float + 0.2::float = 0.3::float;   -- f (false), classic float representation issue

Isn’t this the most famous programming puzzle? With floating point, the comparison returns FALSE because of how fractional numbers are stored in computer memory.

Comparing Text Types

When working with text types, you can compare CHAR, VARCHAR, and TEXT, since PostgreSQL will automatically convert them to compatible types:

SELECT 'Hello' = 'Hello'::TEXT; -- TRUE
SELECT 'World'::CHAR(5) = 'World'::VARCHAR; -- TRUE

Watch out for the length of characters in CHAR(n): if the string is shorter than the specified length, PostgreSQL will pad it with spaces.

Converting Data Types

PostgreSQL gives you a few ways to convert data types. Let’s break down the two main methods:

Method #1: Explicit Conversion (CAST)

The CAST operator lets you specify how to convert one type to another. Here’s an example:

SELECT CAST('42' AS INTEGER); -- Converts the string '42' to the number 42

This method is especially handy if you want your SQL code to be more readable.

Method #2: Short Syntax (::)

PostgreSQL offers an alternative syntax for type conversion — using ::. It’s the same operation, just written shorter:

SELECT '42'::INTEGER; -- Same as CAST('42' AS INTEGER)

Automatic Conversion

In many cases, PostgreSQL will convert data automatically. For example, using numbers in string fields:

SELECT '42' = 42::TEXT; -- TRUE

But relying on automatic conversion isn’t always safe, since it might surprise other developers. For example, with dates and strings it’s better to use explicit conversion.

Examples of Converting Different Data Types

Converting Numbers to Text

Sometimes you need to convert numbers to text (like for building messages):

SELECT 42::TEXT; -- Converts the number 42 to the string '42'
SELECT 3.14::TEXT; -- Converts the number 3.14 to the string '3.14'

Converting Text to Numbers

If the string contains a valid number, you can convert it to a numeric type:

SELECT '123'::INTEGER; -- Converts the string '123' to the number 123
SELECT '3.14'::FLOAT;  -- Converts the string '3.14' to the number 3.14

But what if the text can’t be converted? For example:

SELECT 'Hello'::INTEGER; -- Error: can't convert 'Hello' to a number

To avoid errors like this, check your data beforehand — for example, with a regex (value ~ '^[0-9]+$') or CASE WHEN ... THEN ... ELSE NULL END. PostgreSQL doesn’t have a built-in TRY_CAST function (that one lives in SQL Server / T-SQL).

Converting Dates to Text and Back

When converting dates, you can use the TO_CHAR() and TO_DATE() functions:

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'); -- Converts a date to a string
SELECT TO_DATE('2023-10-25', 'YYYY-MM-DD'); -- Converts a string to a date

Converting Between BOOLEAN and Text

The boolean data type BOOLEAN can also be converted to strings:

SELECT TRUE::TEXT; -- 'true'
SELECT FALSE::TEXT; -- 'false'

Or the other way around:

SELECT 'true'::BOOLEAN; -- TRUE
SELECT 'false'::BOOLEAN; -- FALSE

Heads up: strings like 'yes' or 'no' won’t be converted automatically.

Practice: Everything with Real Examples

Let’s create a table that shows off different data types:

Now let’s run some conversion operations:

-- Converting text to number
SELECT number_as_text::INTEGER FROM data_types_demo WHERE number_as_text = '42';

-- Converting date to text
SELECT TO_CHAR(actual_date, 'DD/MM/YYYY') FROM data_types_demo;

-- Converting string to date
SELECT TO_DATE(date_as_text, 'YYYY-MM-DD') FROM data_types_demo;

Common Mistakes When Converting Data

Common mistakes include:

1. Trying to convert data that doesn’t match the expected format (like the string 'Hello' to INTEGER).
2. Rounding and precision issues when working with floating point numbers.
3. Incorrect use of formats when converting dates.

To avoid mistakes, it’s a good idea to:

• Always check your data before converting.
• Use defensive expressions (CASE WHEN ... ELSE NULL END, regex checks).
• Specify the format explicitly when converting dates.

-- Checking data before converting
SELECT 
    CASE 
        WHEN number_as_text ~ '^\d+$' THEN number_as_text::INTEGER
        ELSE NULL
    END AS safe_integer
FROM data_types_demo;

Use this approach to protect your queries from unexpected situations!

At this point, you’ve got the basics for comparing and converting data types in PostgreSQL. Now it’s just practice, practice, and more practice. See you in the next lectures!