SQL · Guides · October 30, 2026 · 12 min read

SQL data types explained: numbers, text, dates, BOOLEAN and JSON

Every column in a SQL table has a data type, and the type decides what you can do with its values: add them, compare them, sort them, extract a month from them. The types analysts meet every day fall into six groups: whole numbers (INTEGER, BIGINT), exact decimals (DECIMAL/NUMERIC, the right type for money), floating point (REAL, DOUBLE PRECISION, for measurements), text (VARCHAR, TEXT), date and time (DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL) and BOOLEAN, plus JSON for semi-structured data. Most report bugs that look like wrong logic are really type problems: an integer division that returns 0, a number stored as text that sorts “998” above “2033”, a date compared with a string.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output. Where PostgreSQL behaves differently from DuckDB, the text says so.

How to see the type of a column

The standard way is information_schema.columns, which exists in PostgreSQL, MySQL, SQL Server and DuckDB:

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name IN ('bookings', 'flights', 'tickets')
ORDER BY table_name, ordinal_position;

Notice what is text: ticket_no and book_ref look like numbers or codes, but they are identifiers, so they are stored as VARCHAR. Leading zeros (ticket 0005432000987) would be lost in an integer, and nobody adds two ticket numbers. Rule of thumb: if you would never do arithmetic on it, it is text.

The type names in data_type are the engine’s own. DuckDB says VARCHAR and DECIMAL(10,2); PostgreSQL says character varying and numeric, with precision and scale in separate columns (numeric_precision, numeric_scale).

For a single value or expression, DuckDB has typeof() and PostgreSQL has pg_typeof():

SELECT typeof(42) AS a, typeof(3000000000) AS b, typeof(19.99) AS c,
       typeof(1.5e0) AS d, typeof('2026-08-01') AS e, typeof(DATE '2026-08-01') AS f,
       typeof(TRUE) AS g, typeof(INTERVAL '2 hours') AS h;

42 is an integer, but 3 billion does not fit into a 4-byte INTEGER (maximum 2,147,483,647), so it becomes a BIGINT. 19.99 is an exact decimal, 1.5e0 (scientific notation) is a floating-point number, and '2026-08-01' in quotes is just text until something turns it into a date.

Numeric types: INTEGER, BIGINT, DECIMAL, FLOAT

In the course data, aircrafts.range is INTEGER, bookings.total_amount and ticket_flights.amount are DECIMAL(10,2) (up to 99,999,999.99 dollars, two decimal places) and airports.latitude/longitude are DOUBLE.

Why money should be DECIMAL, not FLOAT

Floating-point numbers are stored in binary, and most decimal fractions, like 0.1, have no exact binary form. The error is tiny, but it breaks equality checks and accumulates in sums:

SELECT 0.1 + 0.2 = 0.3 AS decimal_equal,
       CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) = 0.3 AS double_equal,
       CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) AS double_sum;

DECIMAL arithmetic is exact, which is what accountants expect when a report must match an invoice to the cent. Use DOUBLE when you measure (distance, duration as a fraction, a share for a chart) and never compare floats with =; compare with a tolerance, or round first.

Aggregates change types too. Check what SUM, AVG and COUNT return on the decimal total_amount:

SELECT typeof(SUM(total_amount)) AS sum_type,
       typeof(AVG(total_amount)) AS avg_type,
       typeof(COUNT(*)) AS count_type
FROM bookings;

SUM widens the precision so the total cannot overflow, and COUNT returns a BIGINT. AVG returns DOUBLE in DuckDB, while PostgreSQL returns numeric for an average of decimals. Either way, round the result for display.

Integer division: the classic analyst bug

What is 7 / 2? It depends on the database:

SELECT 7 / 2 AS slash, 7 // 2 AS int_div, 7.0 / 2 AS decimal_div, 7 % 2 AS remainder;

That is DuckDB, where / always returns a fraction and // is integer division. PostgreSQL and SQL Server return 3 for 7 / 2: when both sides are integers, the result is an integer and the fraction is thrown away. MySQL returns 3.5000 and uses DIV for integer division.

On real data this turns a cancellation rate into zero:

SELECT COUNT(*) FILTER (WHERE status = 'Cancelled') AS cancelled,
       COUNT(*) AS total,
       COUNT(*) FILTER (WHERE status = 'Cancelled') / COUNT(*) AS duckdb_ratio,
       COUNT(*) FILTER (WHERE status = 'Cancelled') // COUNT(*) AS postgres_style,
       ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'Cancelled') / COUNT(*), 2) AS pct
FROM flights;

The postgres_style column shows what 414 / 33121 gives in PostgreSQL: 0. The portable fix works everywhere: make one side a decimal before dividing, either by multiplying by 100.0 (or 1.0) first or with CAST(x AS NUMERIC). The order matters: 100.0 * a / b is safe, a / b * 100.0 in PostgreSQL still divides integers first.

Text types: VARCHAR, TEXT, CHAR

VARCHAR(n) stores text up to n characters; TEXT (and VARCHAR without a length in PostgreSQL and DuckDB) has no declared limit. In PostgreSQL they perform the same, and the length is just a constraint. CHAR(n) pads values with spaces to a fixed length and is rarely what you want. In SQL Server, VARCHAR vs NVARCHAR also decides whether the column stores Unicode; use NVARCHAR for names in many alphabets.

What analysts need to remember about text is that it compares and sorts character by character, not by value:

SELECT '10' < '9' AS text_compare,
       10 < 9 AS number_compare,
       '2026-08-01' < '2026-10-01' AS iso_text,
       '8/1/2026' < '10/1/2026' AS us_text;

'10' < '9' because the character 1 comes before 9. ISO dates (YYYY-MM-DD) happen to sort correctly as text; US-style dates do not. Seat numbers in the seats table show the same effect:

SELECT MIN(seat_no) AS min_text, MAX(seat_no) AS max_text,
       MIN(CAST(LEFT(seat_no, LENGTH(seat_no) - 1) AS INTEGER)) AS first_row,
       MAX(CAST(LEFT(seat_no, LENGTH(seat_no) - 1) AS INTEGER)) AS last_row
FROM seats
WHERE aircraft_code = 'SU9';

As text, the “smallest” seat on the Superjet is 10A and the “largest” is 9F. Cut off the letter, cast the row number to an integer, and you get the real range: rows 1 to 20. Text comparison is also case-sensitive in PostgreSQL and DuckDB ('Cancelled' = 'cancelled' is false), while MySQL and SQL Server compare case-insensitively under their default collations. More on text in SQL string functions.

Date and time types: DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL

All timestamps in the course data are plain TIMESTAMP. That is common in analytics, but it means you must know which zone they are in: a departure time is local to the airport, and the airports.timezone column tells you which zone that is. TIMESTAMPTZ stores a single point in time (PostgreSQL keeps it in UTC internally) and converts it for display, so it is the safer choice when events come from several time zones.

Subtracting gives different types for dates and timestamps:

SELECT scheduled_arrival - scheduled_departure AS duration,
       typeof(scheduled_arrival - scheduled_departure) AS t1,
       CAST(scheduled_departure AS DATE) - DATE '2026-07-01' AS days,
       typeof(CAST(scheduled_departure AS DATE) - DATE '2026-07-01') AS t2
FROM flights
WHERE flight_id = (SELECT MIN(flight_id) FROM flights);

Timestamp minus timestamp is an INTERVAL (55 minutes here); date minus date is a whole number of days (BIGINT in DuckDB, integer in PostgreSQL). Functions for both are covered in SQL date functions.

Dates written as strings

A date in quotes is text, and the database converts it when it meets a date or timestamp column. That conversion is where a subtle bug lives:

SELECT COUNT(*) FILTER (WHERE book_date = '2026-08-01') AS equals_string,
       COUNT(*) FILTER (WHERE CAST(book_date AS DATE) = '2026-08-01') AS cast_to_date,
       COUNT(*) FILTER (WHERE book_date >= '2026-08-01' AND book_date < '2026-08-02') AS half_open_range
FROM bookings;

Compared with a TIMESTAMP, '2026-08-01' becomes 2026-08-01 00:00:00, so = finds only the one booking made exactly at midnight. Either cast the column to DATE or, better for large tables, use a half-open range (>= the day, < the next day), which keeps the column untouched and can use an index (see SQL indexes explained).

Only ISO format (YYYY-MM-DD) converts reliably. CAST('08/01/2026' AS DATE) fails in DuckDB (“expected format is (YYYY-MM-DD)”), and in PostgreSQL it depends on the DateStyle setting: is it 8 January or 1 August? Parse other formats explicitly: to_date('08/01/2026', 'MM/DD/YYYY') in PostgreSQL, strptime('08/01/2026', '%m/%d/%Y') in DuckDB, STR_TO_DATE in MySQL, CONVERT(date, '08/01/2026', 101) in SQL Server.

BOOLEAN

A comparison such as status = 'Cancelled' is itself a BOOLEAN value: true, false or NULL. You can store it, filter on it, and count it by casting to an integer (true becomes 1):

SELECT aircraft_code,
       COUNT(*) AS flights,
       SUM(CAST(status = 'Cancelled' AS INTEGER)) AS cancelled,
       ROUND(100 * AVG(CAST(status = 'Cancelled' AS INTEGER)), 2) AS cancel_pct
FROM flights
GROUP BY aircraft_code
ORDER BY cancel_pct DESC
LIMIT 4;

The average of a 0/1 flag is the share of true rows, a neat way to get a rate in one expression. SUM of a boolean without the cast fails in PostgreSQL. MySQL has no real boolean (BOOLEAN is an alias for TINYINT(1)), and SQL Server uses BIT, which cannot be summed without a cast either. SQL CASE WHEN shows the same counts with CASE.

JSON

JSON columns hold nested key-value data: event properties, API payloads, settings. In the course data, tickets.contact_data is JSON, and app_events.properties is JSON stored in a VARCHAR. The ->> operator extracts a field as text, and that is the trap:

SELECT MAX(CAST(properties AS JSON) ->> 'price') AS max_as_text,
       MAX(CAST(CAST(properties AS JSON) ->> 'price' AS INTEGER)) AS max_as_number
FROM app_events
WHERE event_name = 'view_offer';

As text, “998” beats “2033”, because 9 sorts after 2. Cast extracted values to the right type before you compare, sort or aggregate them. In PostgreSQL, -> returns JSON and ->> returns text; prefer the JSONB type there, which is stored parsed and can be indexed. MySQL has JSON with the same arrows; SQL Server stores JSON in NVARCHAR and reads it with JSON_VALUE (a native json type arrived only in SQL Server 2025).

CAST, :: and TRY_CAST

To convert a value, use the standard CAST(value AS type). PostgreSQL and DuckDB also accept the shorter value::type:

SELECT CAST('2026-08-01' AS DATE) AS a,
       '2026-08-01'::DATE AS b,
       TRY_CAST('08/01/2026' AS DATE) AS c,
       strptime('08/01/2026', '%m/%d/%Y') AS d;

• CAST is portable; :: works only in PostgreSQL and DuckDB (and engines that copy them). In code that may run on SQL Server or MySQL, write CAST.
• A failed CAST stops the whole query: CAST('abc' AS INTEGER) raises “Could not convert string ‘abc’ to INT32” in DuckDB. TRY_CAST returns NULL instead and exists in DuckDB, SQL Server and Snowflake; PostgreSQL has no built-in equivalent.
• Casting a decimal to an integer rounds in PostgreSQL and DuckDB (CAST(2657.50 AS INTEGER) is 2658) but truncates in SQL Server (2657). Use ROUND, FLOOR or TRUNC explicitly when it matters.

Type names across databases

FAQ

What are the main data types in SQL?

Integers (INTEGER, BIGINT), exact decimals (DECIMAL/NUMERIC), floating-point numbers (REAL, DOUBLE PRECISION), text (VARCHAR, TEXT), dates and times (DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL) and BOOLEAN. Most modern databases also support JSON.

Should I use DECIMAL or FLOAT for money?

DECIMAL (NUMERIC). It stores decimal fractions exactly, so sums and comparisons match to the cent. FLOAT and DOUBLE are approximate: 0.1 + 0.2 is 0.30000000000000004.

What is the difference between VARCHAR and TEXT?

In PostgreSQL, almost none: VARCHAR(n) enforces a maximum length, TEXT does not, and they perform the same. In MySQL, TEXT columns have limits on indexing and defaults, so VARCHAR is the usual choice for short strings.

Why does my SQL division return 0?

Because both numbers are integers, and PostgreSQL and SQL Server do integer division: 414 / 33121 is 0. Multiply by 100.0 or cast one side to NUMERIC before dividing. DuckDB and MySQL return a fraction for /.

What does :: mean in SQL?

It is PostgreSQL’s cast operator: '2026-08-01'::DATE is the same as CAST('2026-08-01' AS DATE). DuckDB supports it too; MySQL and SQL Server do not, so use CAST in portable code.