Data Types — What a Column Can Hold, and Why Results Surprise You

The situation this lesson prepares you for

You ran SELECT * on "Categories" last lesson and got this back:

The ids came back as whole numbers, the names and descriptions as words, and "Picture" was empty on every single row. Nobody typed those rules in row by row. Somebody decided them once, and what they chose is called the column's data type.

It is worth an hour of your time because it is the reason queries return answers that are wrong and look right. A comparison that quietly matches the wrong rows. A total that will not add up. A sort that puts 9 after 10.

By the end of this lesson you will be able to ask any database what a column holds, and say what that means for the query you are about to write.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

A column type is a promise

A type is a promise about what can go in a column. "UnitPrice" promises to hold a number with decimals. "ProductName" promises to hold text. The database keeps that promise: it will refuse a word where a number belongs.

The promise is made once, when the table is created, and every row obeys it. That is why you never see a price in one row and a paragraph in the next.

Ask the database what it is holding

You do not have to guess, and you should not. Every SQL database describes its own tables in a set of standard tables called the catalog, and you query the catalog exactly like any other table.

SELECT "column_name", "data_type"
FROM information_schema.columns
WHERE table_name = 'Products';

The WHERE table_name = 'Products' line is a filter — you will write those properly next lesson. For now, read it as "only the Products table". And notice 'Products' is in single quotes, because it is a text value, not the name of a column. That is the rule from last lesson, doing real work.

This one query works on PostgreSQL, MySQL, SQL Server and DuckDB. It is the first thing to run against a database you have never seen.

The five families you will actually meet

Databases ship dozens of types. In day-to-day work you meet 5.

A date is not a piece of text

Dates are the type of data people get wrong most often, so look closely.

SELECT "OrderID", "OrderDate"
FROM "Orders"
LIMIT 3;

Look at what came back. You asked for a date and got a time as well, down to the millisecond — because the catalog does not call this column a date, it calls it a timestamp: a point on a clock, not a day on a calendar. Nobody recorded a time of day for these orders, so each one sits at midnight.

That is the whole section in one result. "OrderDate" is not the text "2024-03-15" — text has no midnight to show you. It is a point in time the database understands, which is why it knows March comes before April without comparing letters.

Some people store dates as text anyway. That is a bad habit: you can no longer add 30 days to one, and the database will happily accept the 31st of February.

What the type decides

Three practical consequences, and they cover almost everything you will hit.

1. Whether you quote it. Text values go in single quotes; numbers go bare. 'Beverages' is text; 18 is a number.
2. Whether you can do arithmetic on it. Prices add and multiply. Names do not.
3. How it sorts and compares. Often a real source of bugs - bugs which, many times, cost real money.

Numbers do arithmetic

Start with the easy one. A number is a number, so you can multiply it.

SELECT "ProductName", "UnitPrice", "UnitPrice" * 2
FROM "Products"
LIMIT 3;

That third column has no name of its own. You asked for a calculation and never said what to call it, so the database invented a heading — PostgreSQL shows ?column?, DuckDB shows (UnitPrice * 2). Naming it yourself is a later lesson.

…and it is refused on text

Try exactly the same multiplication on a name:

SELECT "ProductName" * 2
FROM "Products";

SQL Error [42883]: ERROR: operator does not exist: character varying * integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 22

That error is not you doing something wrong — it is the type system doing its job. Multiplying a product name is meaningless, so the database says so instead of inventing an answer.

A postal code is text, not a number

Look at the postal codes of the German customers:

SELECT "CompanyName", "City", "PostalCode"
FROM "Customers"
LIMIT 8;

Every one of those is digits. "PostalCode" is still text, and the seventh row is why: Leipzig is 04179. Store that as a number and the leading zero is gone — 4179 is not a postal code in Leipzig or anywhere else.

The rule generalises, and it is worth remembering: if you will never do arithmetic on it, it is not a number. Postal codes, phone numbers, order references, product codes — all text, however numeric they look. Nobody would ever add two postal codes together.

Now the puzzle that catches everyone

One more query, and it is the most important one in this lesson. The same two digits on both sides. On the left they are quoted, so they are text. On the right they are bare, so they are numbers. Predict both answers before you run it.

SELECT '9' > '10', 9 > 10;

Read that again. As text, 9 is greater than 10. As numbers, it is not.

Nothing is broken. Text compares character by character, the way a dictionary does: '9' beats '1' on the very first character, so 'banana' comes after 'apple' and '9' comes after '10'. Numbers compare by value.

The wrong answer that looks right

This is where wrong answers that look right come from. Import a spreadsheet where the amounts arrived as text, ask for everything over 100, and the query is fine — the type is wrong. You get an answer that looks correct and most likely is not.

When a comparison surprises you, check the TYPE before you check your logic.

true, false — and NULL

SELECT "ProductName", "UnitsInStock", "Discontinued"
FROM "Products"
LIMIT 4;

That last column is a boolean — it answers a yes-or-no question, so its value is either true or false for every product. All 4 here are false; only 2 of the 20 products are discontinued.

In CloudBeaver you will not see the word. It draws a boolean as a checkbox: an empty box is false, a ticked one is true. Nothing is different about the data — psql prints the same value as f, and the DuckDB CLI prints false. Three tools, three renderings, one value. The type is what the database stores; how it appears is your client's decision.

And then there is NULL, which you met as that empty "Picture" column. NULL means the value is missing — nobody ever recorded it. That is not the same as zero, and not the same as empty text: a price of 0 is a real price; a price of NULL was never written down.

Missing values change how comparisons behave, which is a lesson of its own later in this series. For now: when you see NULL, read it as "missing".

Your turn, before we go on

Run the catalog query against two more tables, changing only the name in quotes:

SELECT "column_name", "data_type"
FROM information_schema.columns
WHERE table_name = 'Customers';

Then try 'Orders'. Which columns are text that you would have guessed were numbers? Which one column in "Orders" is a date? Answering that from the catalog, rather than by guessing from the values, is the skill this lesson teaches.

What you can do now

Given any table in any database:

1. SELECT "column_name", "data_type" FROM information_schema.columns WHERE table_name = 'TheTable'; to ask what it holds.
2. Sort each column into one of the five families, and you know whether to quote it, whether you can add it, and how it will compare.

Next lesson: WHERE — keeping only the rows you want. Every condition you write there is a comparison, so everything on this page is about to matter.

The koans: now you write the queries

Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _07

On macOS or Linux, run ./zeus.sh koans learnsql series1 _07 from the same folder. The 3 arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_07\DataTypesKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.

10 koans, easiest first, in the order this lesson builds them: ask the catalog how many columns a table has and what 1 column is; read what the price column promises and what it returns; meet dates and true/false; see that a postal code is text; multiply a number; predict the comparison that catches everyone; then write a catalog query from scratch. They run on DuckDB — a single file, no server to start.