4  Data Types

4.1 Learning Objectives

By the end of this chapter, students will be able to:

  • Identify the most common PostgreSQL data types including text, integer, numeric, boolean, and date/time types.
  • Distinguish between fixed-length (CHAR) and variable-length (VARCHAR, TEXT) character types and select the appropriate one.
  • Choose an appropriate numeric type (SMALLINT, INT, BIGINT, NUMERIC, REAL, DOUBLE PRECISION) given the precision and range requirements of a value.
  • Explain the difference between exact numeric types (NUMERIC) and floating-point types (REAL, DOUBLE PRECISION) and the implications for precision-sensitive calculations.
  • Write SQL expressions using the :: cast operator and CAST() function to convert values between data types.
  • Apply date and time literals in SQL queries and explain the 'YYYY-MM-DD' format requirement.
  • Explain why NULL is distinct from zero, an empty string, or false and how it propagates through expressions.
  • Use pg_typeof() to inspect the inferred type of an expression in PostgreSQL.

In the previous chapter, you encountered a line that looked like this:

ROUND(height_cm / 2.54::numeric, 1) AS height_in

The ::numeric piece is a type cast: an instruction that tells PostgreSQL to treat one value as a different data type. This chapter explains why that was necessary, what data types PostgreSQL provides, and how to convert between them.

Every value stored in a database has a data type, which tells the database how to store the value, how much space to allocate, and what operations are valid on it. Adding two integers produces a different result than concatenating two strings, and dividing a whole number by another whole number produces a different result than dividing two decimals. Choosing the right data type for each column is one of the most consequential decisions you make when designing a database.

4.2 Inspecting Column Types

Before discussing types in the abstract, it helps to see what types the actors table actually uses. The most direct way to do this is to query information_schema.columns, a catalog view available in every PostgreSQL database that describes the structure of the database itself.

SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale
FROM   information_schema.columns
WHERE  table_name = 'actors'
ORDER  BY ordinal_position;
15 records
column_name data_type character_maximum_length numeric_precision numeric_scale
actor_id integer NULL 32 0
first_name character varying 50 NULL NULL
last_name character varying 50 NULL NULL
birth_name character varying 100 NULL NULL
sex character 1 NULL NULL
birth_date date NULL NULL NULL
death_date date NULL NULL NULL
birth_country character varying 50 NULL NULL
height_cm smallint NULL 16 0
oscar_nominations smallint NULL 16 0
oscar_wins smallint NULL 16 0
primary_genre character varying 20 NULL NULL
has_honorary_oscar boolean NULL NULL NULL
notable_role text NULL NULL NULL
total_box_office_usd numeric NULL 12 0

The data_type column uses standard SQL type names. character varying is the standard name for VARCHAR, and character is the standard name for CHAR. PostgreSQL accepts both forms — the catalog stores the standard names regardless of which form you used when creating the table.

An alternative approach uses the pg_typeof() function to return the data type of any expression in your query. This includes table columns, but it also works on computed expressions, literals, and the results of function calls.

SELECT
    pg_typeof(actor_id)             AS actor_id_type,
    pg_typeof(height_cm)            AS height_cm_type,
    pg_typeof(birth_date)           AS birth_date_type,
    pg_typeof(has_honorary_oscar)   AS honorary_type,
    pg_typeof(total_box_office_usd) AS box_office_type
FROM actors
LIMIT 1;
1 records
actor_id_type height_cm_type birth_date_type honorary_type box_office_type
integer smallint date boolean numeric

The sections below work through each type family, explain what they mean, and show where they appear in the actors table.

4.3 Text Types

PostgreSQL provides three types for storing text.

4.3.1 CHAR(n)

CHAR(n) stores a fixed-length string of exactly n characters. If the value you insert is shorter than n, PostgreSQL pads it with trailing spaces to reach the declared length. If it is longer, you get an error.

The sex column in the actors table is CHAR(1) — exactly one character, always either 'M' or 'F'. Fixed-length types like this made sense decades ago when storage was expensive and padding simplified low-level file management. For a single-character code that is always exactly one character long, CHAR(1) is still a reasonable choice. In most other situations, VARCHAR or TEXT is preferable.

4.3.2 VARCHAR(n)

VARCHAR(n) stores a variable-length string up to a maximum of n characters. No padding is added; the value is stored exactly as written. Most of the text columns in the actors table use VARCHAR with a length limit — first_name and last_name are VARCHAR(50), birth_country and primary_genre are shorter, and birth_name is VARCHAR(100) to accommodate longer legal names.

VARCHAR is also written as character varying in catalog queries, as you saw in the information_schema output above.

4.3.3 TEXT

TEXT stores a variable-length string with no upper limit on length. The notable_role column uses TEXT — film titles have no predictable maximum length, and there is no meaningful cap to enforce.

SELECT first_name, last_name, notable_role
FROM   actors
ORDER  BY last_name
LIMIT  10;
10 records
first_name last_name notable_role
Victoria Abril Tie Me Up! Tie Me Down!
Amy Adams Arrival
Mahershala Ali Moonlight
Bibi Andersson Persona
Harriet Andersson Summer with Monika
Julie Andrews The Sound of Music
Fred Astaire Top Hat
Amitabh Bachchan Deewar
Kevin Bacon A Few Good Men
Eric Bana Munich

In PostgreSQL, TEXT and VARCHAR without a length limit are implemented identically under the hood. The only practical difference is that VARCHAR(n) enforces a length cap, whereas TEXT does not. Some other databases deprecated TEXT years ago because it was inefficient, which led to a widespread convention of always using VARCHAR(n). PostgreSQL does not have that limitation. If you do not have a specific reason to cap the length of a column, TEXT is a reasonable default.

For more on this, see Don’t use varchar(n) by default from the PostgreSQL wiki.

4.4 Numeric Types

PostgreSQL distinguishes between exact and approximate numeric storage.

4.4.1 Integer Types

Integer types store whole numbers with no decimal component. PostgreSQL provides three sizes:

Type Storage Range
SMALLINT 2 bytes −32,768 to 32,767
INTEGER 4 bytes −2,147,483,648 to 2,147,483,647
BIGINT 8 bytes −9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

The actors table uses SMALLINT for height_cm, oscar_nominations, and oscar_wins — all values that comfortably fit within the −32,768 to 32,767 range. actor_id uses plain INTEGER. No column uses BIGINT, but it is the right choice whenever a whole-number value could exceed roughly two billion — population counts, row identifiers in very large tables, or any counter expected to grow without bound.

An overflow error occurs when you try to store a value that is too large for the column’s type. PostgreSQL raises an error like this:

SQLSTATE 22003: integer out of range

On modern hardware, BIGINT is not meaningfully more expensive to store or query than INTEGER. Many database designers use BIGINT for all whole-number columns as a precaution, to avoid having to migrate a column type later when values grow larger than anticipated.

PostgreSQL also provides SERIAL, SMALLSERIAL, and BIGSERIAL as shorthand for auto-incrementing integer columns, commonly used for primary keys. Modern PostgreSQL (version 10 and later) prefers GENERATED ALWAYS AS IDENTITY for new tables, but you will see SERIAL frequently in existing schemas. Both are covered in the chapter on creating tables.

4.4.2 NUMERIC and DECIMAL

NUMERIC(precision, scale) stores exact decimal numbers. The precision is the total number of significant digits, and scale is the number of digits to the right of the decimal point. DECIMAL is a synonym for NUMERIC; they are interchangeable.

The total_box_office_usd column is NUMERIC(12, 0) — up to twelve significant digits with no decimal places, suitable for whole-dollar box office figures up to $999,999,999,999.

SELECT first_name, last_name,
       total_box_office_usd,
       total_box_office_usd / 1000000 AS millions
FROM   actors
ORDER  BY total_box_office_usd DESC
LIMIT  10;
10 records
first_name last_name total_box_office_usd millions
Chris Hemsworth 9.2e+09 9200
Cate Blanchett 5.1e+09 5100
Tom Hanks 4.8e+09 4800
Hugh Jackman 4.6e+09 4600
Benedict Cumberbatch 4.5e+09 4500
Jennifer Lawrence 4.2e+09 4200
Brad Pitt 4.1e+09 4100
Nicole Kidman 3.8e+09 3800
Robert De Niro 3.5e+09 3500
Julia Roberts 3.4e+09 3400

Because NUMERIC uses exact arithmetic, it is the correct choice for financial and monetary data where rounding errors are unacceptable. The result of dividing two NUMERIC values is always exact, unlike floating-point arithmetic.

4.4.3 MONEY

PostgreSQL includes a MONEY type specifically for currency amounts. It stores values as an 8-byte integer internally and always displays them with exactly two decimal places, formatted according to the database server’s locale setting.

SELECT
    1234.56::money                  AS amount,
    pg_typeof(1234.56::money)       AS stored_as,
    1234.56::money::text            AS text_representation;
1 records
amount stored_as text_representation
0 money $1,234.56

At first glance, MONEY looks convenient for financial data. In practice, it has several rough edges worth understanding.

Locale-dependent output. The formatted output of a MONEY value depends on the lc_monetary locale configured on the server. Two databases with different locale settings will display the same stored value differently, and a value accepted as valid input on one server may be rejected on another.

Arithmetic produces unexpected types. Dividing a MONEY value by an integer returns MONEY, which is intuitive. Dividing a MONEY value by another MONEY value, however, returns DOUBLE PRECISION, not MONEY or NUMERIC. This silently introduces floating-point approximation into a calculation you might expect to be exact.

SELECT
    1234.56::money / 2                    AS divided_by_int,
    pg_typeof(1234.56::money / 2)         AS type_after_int_division,
    1234.56::money / 100::money           AS divided_by_money,
    pg_typeof(1234.56::money / 100::money) AS type_after_money_division;
1 records
divided_by_int type_after_int_division divided_by_money type_after_money_division
0 money 12.3456 double precision

The PostgreSQL wiki’s “Don’t Do This” guide lists MONEY as a type to avoid. For financial data, NUMERIC(precision, 2) is the safer and more portable choice. You will encounter MONEY in existing databases, so it is important to recognize it, but new columns storing currency should use NUMERIC.

4.4.4 REAL and DOUBLE PRECISION

REAL (4 bytes) and DOUBLE PRECISION (8 bytes) store floating-point numbers — approximations rather than exact values. They follow the IEEE 754 standard used by most programming languages for float and double.

SELECT
    pg_typeof(3.14)            AS uncast_literal,
    pg_typeof(3.14::real)      AS real_type,
    pg_typeof(3.14::float8)    AS double_type;
1 records
uncast_literal real_type double_type
numeric real double precision

Floating-point types are appropriate for scientific measurements, geographic coordinates, and other values where a tiny approximation error is acceptable. They are not appropriate for financial amounts.

Floating-point arithmetic can introduce tiny rounding errors that accumulate with repeated calculations. 0.1::real + 0.2::real does not always equal 0.3 exactly. Use NUMERIC for any values that require exact decimal arithmetic.

4.5 Boolean

The BOOLEAN type stores a truth value: TRUE, FALSE, or NULL. The has_honorary_oscar column in the actors table is a boolean — each actor either has received an honorary Oscar or has not.

SELECT first_name, last_name, has_honorary_oscar, oscar_wins
FROM   actors
WHERE  has_honorary_oscar = true
ORDER  BY last_name;
17 records
first_name last_name has_honorary_oscar oscar_wins
Julie Andrews TRUE 1
Kirk Douglas TRUE 0
Clint Eastwood TRUE 0
Whoopi Goldberg TRUE 1
Cary Grant TRUE 0
Alec Guinness TRUE 1
Audrey Hepburn TRUE 1
Charlton Heston TRUE 1
Gene Kelly TRUE 0
Sophia Loren TRUE 1
Paul Newman TRUE 1
Peter O’Toole TRUE 0
Laurence Olivier TRUE 1
Gregory Peck TRUE 1
Sidney Poitier TRUE 1
James Stewart TRUE 1
Elizabeth Taylor TRUE 2

PostgreSQL displays boolean values as t and f in the terminal, though it accepts several input forms: TRUE/FALSE, 't'/‘f’,‘yes’/‘no’,‘1’/‘0’, and‘on’/‘off’. Applications and reporting tools typically render them astrue/false` or checkboxes depending on context.

Boolean values also arise naturally whenever you write a comparison expression. The following query adds a computed boolean column that is TRUE for actors with more than one Oscar win:

SELECT
    first_name || ' ' || last_name AS full_name,
    oscar_wins,
    oscar_wins > 1 AS multiple_wins
FROM actors
ORDER BY oscar_wins DESC
LIMIT 10;
10 records
full_name oscar_wins multiple_wins
Katharine Hepburn 4 TRUE
Jack Nicholson 3 TRUE
Daniel Day-Lewis 3 TRUE
Walter Brennan 3 TRUE
Meryl Streep 3 TRUE
Ingrid Bergman 3 TRUE
Cate Blanchett 2 TRUE
Bette Davis 2 TRUE
Marlon Brando 2 TRUE
Denzel Washington 2 TRUE

The expression oscar_wins > 1 evaluates to a BOOLEAN for each row. This is the same kind of expression you write in a WHERE clause — the difference here is that we are selecting it as a column rather than using it to filter rows.

4.6 Date and Time Types

PostgreSQL provides dedicated types for dates, times, and durations. Using these types instead of plain text or integers allows the database to validate values, perform date arithmetic, and handle time zones correctly.

4.6.1 DATE

DATE stores a calendar date: year, month, and day, with no time component. The birth_date and death_date columns in the actors table are both DATE.

SELECT first_name, last_name, birth_date, death_date
FROM   actors
WHERE  death_date IS NOT NULL
ORDER  BY death_date DESC
LIMIT  10;
10 records
first_name last_name birth_date death_date
Alain Delon 1935-11-08 2024-08-18
Gina Lollobrigida 1927-07-04 2023-01-16
Sidney Poitier 1927-02-20 2022-01-06
Jean-Paul Belmondo 1933-04-09 2021-09-06
Sean Connery 1930-08-25 2020-10-31
Max von Sydow 1929-04-10 2020-03-08
Kirk Douglas 1916-12-09 2020-02-05
Doris Day 1922-04-03 2019-05-13
Bibi Andersson 1935-11-11 2019-04-14
Albert Finney 1936-05-09 2019-02-07

Date literals in PostgreSQL are written as quoted strings in ISO 8601 format: '2024-12-16'. The database validates that the string represents a real date — '2024-02-30' would produce an error.

4.6.2 TIME

TIME stores a time of day without an associated date. It comes in two variants: TIME WITHOUT TIME ZONE (also written as TIME) and TIME WITH TIME ZONE (TIMETZ). In practice, TIME WITH TIME ZONE is rarely useful on its own, because a time of day without a date provides limited context for time-zone conversions. TIMESTAMP is the more common choice when both date and time are needed.

4.6.3 TIMESTAMP

TIMESTAMP stores both a date and a time down to the microsecond. It also has two variants: TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ).

If you are coming from MySQL or SQL Server, you may be looking for a DATETIME type. PostgreSQL does not have one — TIMESTAMP covers the same use case.

The function NOW() returns the current date and time as a TIMESTAMPTZ, and CURRENT_DATE returns today’s date as a DATE.

SELECT
    NOW()         AS current_timestamp,
    CURRENT_DATE  AS today,
    CURRENT_TIME  AS time_now;
1 records
current_timestamp today time_now
2026-05-06 18:10:14 2026-05-06 18:10:14.821266

4.6.4 INTERVAL

INTERVAL stores a span of time — a duration rather than a specific point on the calendar. Intervals are written as quoted strings with a unit: INTERVAL '1 year', INTERVAL '3 months', INTERVAL '45 days'.

Intervals are most useful for date arithmetic. You can add an interval to a date to compute a future or past date, or subtract two dates to compute the duration between them.

SELECT
    first_name || ' ' || last_name      AS full_name,
    birth_date,
    birth_date + INTERVAL '50 years'    AS fiftieth_birthday,
    CURRENT_DATE - birth_date           AS days_since_birth
FROM actors
WHERE death_date IS NULL
ORDER BY birth_date
LIMIT 10;
10 records
full_name birth_date fiftieth_birthday days_since_birth
Eva Marie Saint 1924-07-04 1974-07-04 37196
Gene Hackman 1930-01-30 1980-01-30 35160
Clint Eastwood 1930-05-31 1980-05-31 35039
Harriet Andersson 1932-02-14 1982-02-14 34415
Kim Novak 1933-02-13 1983-02-13 34050
Michael Caine 1933-03-14 1983-03-14 34021
Shirley MacLaine 1934-04-24 1984-04-24 33615
Sophia Loren 1934-09-20 1984-09-20 33466
Brigitte Bardot 1934-09-28 1984-09-28 33458
Judi Dench 1934-12-09 1984-12-09 33386

Notice that subtracting two DATE values produces an INTEGER representing the number of days between them, while adding an INTERVAL to a DATE produces a TIMESTAMP. PostgreSQL promotes the result to a timestamp because an interval can include a time component.

Some database systems and documentation use the term DURATION for this concept. In PostgreSQL, the type is always called INTERVAL.

4.6.5 EXTRACT

EXTRACT pulls a single component — year, month, day, and others — out of a date or timestamp as a number. The syntax is:

EXTRACT(field FROM date_or_timestamp)

Common fields include year, month, day, hour, minute, second, quarter, week, and dow (day of week, where 0 is Sunday).

SELECT
    first_name || ' ' || last_name  AS full_name,
    birth_date,
    EXTRACT(year  FROM birth_date)  AS birth_year,
    EXTRACT(month FROM birth_date)  AS birth_month,
    EXTRACT(day   FROM birth_date)  AS birth_day
FROM actors
ORDER BY birth_date
LIMIT 10;
10 records
full_name birth_date birth_year birth_month birth_day
Edward Robinson 1893-12-12 1893 12 12
Walter Brennan 1894-07-25 1894 7 25
Ruth Gordon 1896-10-30 1896 10 30
Fred Astaire 1899-05-10 1899 5 10
James Cagney 1899-07-17 1899 7 17
Humphrey Bogart 1899-12-25 1899 12 25
Spencer Tracy 1900-04-05 1900 4 5
Alastair Sim 1900-10-09 1900 10 9
Thelma Ritter 1902-02-14 1902 2 14
Cary Grant 1904-01-18 1904 1 18

A practical use is computing an actor’s age in whole years. Subtracting birth years alone is imprecise because it ignores whether the birthday has occurred yet in the current year. The expression below accounts for that by comparing the month and day:

SELECT
    first_name || ' ' || last_name                          AS full_name,
    birth_date,
    EXTRACT(year FROM CURRENT_DATE) -
    EXTRACT(year FROM birth_date) -
    CASE WHEN (EXTRACT(month FROM CURRENT_DATE),
               EXTRACT(day   FROM CURRENT_DATE)) <
              (EXTRACT(month FROM birth_date),
               EXTRACT(day   FROM birth_date))
         THEN 1 ELSE 0 END                                  AS age
FROM actors
WHERE death_date IS NULL
ORDER BY birth_date
LIMIT 10;
10 records
full_name birth_date age
Eva Marie Saint 1924-07-04 101
Gene Hackman 1930-01-30 96
Clint Eastwood 1930-05-31 95
Harriet Andersson 1932-02-14 94
Kim Novak 1933-02-13 93
Michael Caine 1933-03-14 93
Shirley MacLaine 1934-04-24 92
Sophia Loren 1934-09-20 91
Brigitte Bardot 1934-09-28 91
Judi Dench 1934-12-09 91

PostgreSQL also provides DATE_PART('field', value), which is an older function that does the same thing as EXTRACT. The two are interchangeable for dates and timestamps: EXTRACT(year FROM birth_date) and DATE_PART('year', birth_date) produce identical results. EXTRACT is the SQL standard form and is preferred in new code.

4.7 Type Conversion

Sometimes a value stored as one type needs to be treated as another. This is called casting. You saw an example in the previous chapter: dividing height_cm (a SMALLINT) by 2.54 required a cast to NUMERIC to get a decimal result rather than integer division.

PostgreSQL provides two syntaxes for casting, which are fully interchangeable.

4.7.1 The CAST() Function

CAST(value AS type) is the ANSI SQL standard form, supported by virtually every relational database.

SELECT
    first_name || ' ' || last_name          AS full_name,
    height_cm,
    ROUND(CAST(height_cm AS numeric) / 2.54, 1) AS height_in
FROM actors
ORDER BY height_cm DESC
LIMIT 10;
10 records
full_name height_cm height_in
Max von Sydow 196 77.2
Clint Eastwood 193 76.0
Gregory Peck 191 75.2
Charlton Heston 191 75.2
Chris Hemsworth 190 74.8
James Stewart 188 74.0
Idris Elba 188 74.0
Sidney Poitier 188 74.0
Hugh Jackman 188 74.0
Henry Fonda 188 74.0

4.7.2 The :: Operator

The :: operator is PostgreSQL’s native shorthand for the same operation. value::type is identical in behavior to CAST(value AS type), but more compact.

SELECT
    first_name || ' ' || last_name          AS full_name,
    height_cm,
    ROUND(height_cm::numeric / 2.54, 1)     AS height_in
FROM actors
ORDER BY height_cm DESC
LIMIT 10;
10 records
full_name height_cm height_in
Max von Sydow 196 77.2
Clint Eastwood 193 76.0
Gregory Peck 191 75.2
Charlton Heston 191 75.2
Chris Hemsworth 190 74.8
James Stewart 188 74.0
Idris Elba 188 74.0
Sidney Poitier 188 74.0
Hugh Jackman 188 74.0
Henry Fonda 188 74.0

Both queries produce identical results. The :: form is widely used in PostgreSQL code because it is concise and reads naturally inline with other expressions. The CAST() form is more portable if you ever need to run the same query on a different database system.

4.7.3 Common Casting Patterns

A few casting patterns come up repeatedly in practice.

Numeric to text for string building. The || concatenation operator requires both operands to be text. Casting a number to text before concatenating is the standard solution.

SELECT
    first_name || ' ' || last_name || ' — $' ||
    (total_box_office_usd / 1000000)::text || 'M'  AS bio_label
FROM actors
ORDER BY total_box_office_usd DESC
LIMIT 10;
10 records
bio_label
Chris Hemsworth — $9200.0000000000000000M
Cate Blanchett — $5100.0000000000000000M
Tom Hanks — $4800.0000000000000000M
Hugh Jackman — $4600.0000000000000000M
Benedict Cumberbatch — $4500.0000000000000000M
Jennifer Lawrence — $4200.0000000000000000M
Brad Pitt — $4100.0000000000000000M
Nicole Kidman — $3800.0000000000000000M
Robert De Niro — $3500.0000000000000000M
Julia Roberts — $3400.0000000000000000M

Text to a typed value. SQL queries often receive values from external sources as text strings. Casting converts them to the appropriate type for arithmetic or comparison.

SELECT
    '1956'::integer        AS year_as_int,
    '3.14159'::numeric     AS pi_as_numeric,
    '2024-12-16'::date     AS date_from_string,
    'true'::boolean        AS bool_from_string;
1 records
year_as_int pi_as_numeric date_from_string bool_from_string
1956 3.14159 2024-12-16 TRUE

Rounding versus truncating. Casting a NUMERIC value to INTEGER rounds to the nearest integer. If you want to discard the decimal component entirely and always round toward zero, use TRUNC() instead. The difference only shows when the decimal portion is 0.5 or greater. Converting heights from centimeters to whole feet makes the distinction visible:

SELECT DISTINCT ON (height_cm)
    first_name || ' ' || last_name              AS actor,
    height_cm,
    ROUND(height_cm::numeric / 30.48, 2)        AS exact_feet,
    TRUNC(height_cm::numeric / 30.48)::integer  AS truncated_feet,
    ROUND(height_cm::numeric / 30.48)::integer  AS rounded_feet
FROM actors
WHERE height_cm BETWEEN 163 AND 173
ORDER BY height_cm, last_name;
7 records
actor height_cm exact_feet truncated_feet rounded_feet
Victoria Abril 163 5.35 5 5
Bibi Andersson 165 5.41 5 5
Juliette Binoche 167 5.48 5 5
Julie Andrews 168 5.51 5 6
Monica Bellucci 170 5.58 5 6
Sandra Bullock 171 5.61 5 6
Jessica Lange 173 5.68 5 6

4.7.4 Implicit Casting

PostgreSQL can often cast automatically without you having to write it explicitly. When you compare a DATE column to a text literal like '1950-01-01', the database recognizes that the literal can be interpreted as a date and converts it silently. This is called an implicit cast.

-- PostgreSQL implicitly casts the string to a DATE for comparison
SELECT first_name, last_name FROM actors WHERE birth_date = '1956-07-09';

Implicit casting is convenient, but it can occasionally produce surprising results or performance problems in complex queries. Being explicit with casts is always safe; being implicit is fine when the conversion is obvious.

4.7.5 When Casts Fail

A cast fails if the value cannot be converted to the target type. Attempting to cast a non-numeric string to an integer produces an error.

SELECT CAST('Forrest Gump' AS integer);
Error: Failed to prepare query : ERROR:  invalid input syntax for type integer: "Forrest Gump"
LINE 1: SELECT CAST('Forrest Gump' AS integer);
                    ^

PostgreSQL does not provide a built-in “try cast” function that returns NULL on failure instead of raising an error. Handling potentially invalid input requires either cleaning the data before it enters the database or using more advanced techniques such as regular expression validation, which are covered in a later chapter.