You already know what SQL is and how it's written. It's time to understand what it acts on: the structure where the data lives. In this lesson we'll take the whole hierarchy apart —server, database, schema, table, row, column—, see why the spreadsheet analogy helps at first but breaks down quickly, and walk in detail through PostgreSQL's catalogue of data types. That last point matters more than it looks: choosing a type badly is a mistake you pay for over years, and the case of money stored in floating point is the canonical example of why. We'll finish by learning to inspect tables that already exist, a skill you'll need every time you face somebody else's database.

1. The hierarchy: server, database, schema, table
2. Tables, rows and columns
3. The spreadsheet analogy (and where it breaks)
4. Numeric data types
5. Why money never goes in floating point
6. Text types
7. Date and time types
8. Booleans, UUID and JSONB
9. NULL: the absence of a value
10. Equivalences between PostgreSQL, MySQL and SQLite
11. How to inspect existing tables
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

In PostgreSQL, objects are organised into four levels:

Two practical consequences of this hierarchy:

1. You can't query two databases at once. If greenstore and sql_tests live on the same server, a query can't combine them directly (you'd need extensions like dblink or postgres_fdw). In MySQL, on the other hand, writing SELECT ... FROM other_database.table is common, because there "database" and "schema" are almost synonyms.
2. Schemas do combine freely. Inside greenstore you could have a sales schema and an analytics schema and query them together without any trouble.

Every PostgreSQL database is born with a schema called public. If you create a table without specifying a schema, it ends up there, and if you query it without a prefix it's looked up there. These two statements are equivalent in our setup:

The lookup order is determined by the search_path parameter:

It means: "look first for a schema named after the connected user; if it doesn't exist, look in public". Schemas are there to organise large databases (splitting by functional area, by client, by environment) and to avoid name collisions. All of GreenStore lives in public, so we won't have to worry about this again.

A table is a collection of rows that share the same structure. Every table is defined by:

• A name (products).
• An ordered set of columns, each with a name and a data type.
• Optionally, constraints that limit which values are valid (module 5).

A simplified view of products:

Formal terminology versus everyday terminology:

Three fundamental properties worth burning in from the start:

1. Every row has the same columns. There's no such thing as a row "with an extra field". If a piece of data doesn't apply, NULL is stored.
2. Each column has a single type. You can't store 'tomorrow' in a DATE column.
3. Rows have no intrinsic order. A table is a set. If you don't ask for ORDER BY explicitly, the engine can hand them back in any order, and that order can change tomorrow. Relying on the "natural order" is a classic mistake.

At first it helps to think of a table as an Excel sheet: the first row holds the headers (columns) and the following ones the data (rows). The analogy works... up to a point.

The deepest conceptual difference is strict typing. In Excel, a column of prices can hold 12,50, 12.50, "12.50 €" and twelve fifty, and nobody warns you until the total comes out wrong. In PostgreSQL, if the column is NUMERIC(10,2), the engine rejects anything that isn't a number:

That error, which can look like a nuisance, is exactly the value a database brings: incorrect data never gets in.

About NUMERIC(p,s):

• p (precision) is the total number of digits.
• s (scale) is how many of those digits go after the decimal point.
• NUMERIC(10,2) allows up to 99,999,999.99 → eight integer digits and two decimals.

In GreenStore we use NUMERIC(10,2) for price, cost, shipping_cost, unit_price, amount and salary, and NUMERIC(4,2) for discount (a fraction between 0 and 1, with two decimals).

Note on SERIAL: it isn't a real type, but a shortcut that creates an INTEGER plus a sequence that auto-increments it. Since PostgreSQL 10 the form recommended by the standard is GENERATED BY DEFAULT AS IDENTITY, which is the one the course script uses. You'll see it in detail in module 5.

REAL and DOUBLE PRECISION store numbers in binary floating point (the IEEE 754 standard). The problem is that many decimals that are exact in base 10 are recurring in base 2: 0.1 in binary is infinite, just as 1/3 is in decimal. The computer stores an approximation, and those tiny errors accumulate.

Check it for yourself:

And the case that would wreck an accounting close for you:

Applied to GreenStore: if we stored price as REAL and added up the 47 order lines in the database, the total might come out as 143.20999999998 instead of 143.21. Multiply that by thousands of orders a month and you have an accounting mismatch that's impossible to justify.

A rule with no exceptions: money → NUMERIC. Never FLOAT, REAL or DOUBLE PRECISION. The speed penalty is irrelevant next to a lost cent.

There's also a MONEY type in PostgreSQL, but it's not recommended: it depends on the server's regional settings and doesn't handle multiple currencies well. Use NUMERIC.

One PostgreSQL quirk that surprises people coming from other engines: TEXT and VARCHAR have exactly the same performance. Internally they're the same type; VARCHAR(n) merely adds a length check. There is no speed advantage in setting a limit.

So when should you use VARCHAR(n)? When the limit means something:

• country VARCHAR(60): a country name, a reasonable limit.
• email VARCHAR(120): there's a known practical maximum.
• description TEXT: we don't know how much anybody will write.
• comment TEXT: a review can be long.

Avoid CHAR(n). It pads with trailing spaces and causes surprising comparisons:

The value is stored as 'ES ' but trailing spaces are ignored when comparing, which creates constant confusion when exporting or concatenating.

Dialect note: in MySQL there are performance and storage differences between CHAR, VARCHAR and TEXT (TEXT columns are stored outside the row and don't accept a default value). What makes no difference here does make a difference there.

The distinction between TIMESTAMP and TIMESTAMPTZ is the one that causes the most trouble in production:

• TIMESTAMP stores literally what you give it. If a French customer and a Spanish one both record "18:30", they're stored identically even though they're different moments.
• TIMESTAMPTZ converts to UTC on write and to the client's zone on read. It represents a real instant in time.

Rule of thumb: if the moment has real relevance (when something happened), use TIMESTAMPTZ. If it's a calendar date with no time (an order date, a date of birth), use DATE. GreenStore uses DATE for all its dates because they're calendar dates, not instants.

Dialect: MySQL has DATETIME (no zone) and TIMESTAMP (with UTC conversion, but limited to 2038). SQLite has no date type: it stores ISO text, numbers or julian days, and the date functions operate on those representations.

Stores TRUE, FALSE or NULL. Takes 1 byte. In GreenStore it's used by products.active and suppliers.active.

That NULL has business meaning: "active = TRUE" is a product on sale, "FALSE" a discontinued one, and NULL would mean "we haven't decided yet". That's why booleans in SQL have three states, not two.

A 128-bit universal identifier, in the style of a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11. Takes 16 bytes.

GreenStore uses INTEGER for its id columns because it's a small, didactic database where being able to write WHERE id = 7 is a learning advantage.

PostgreSQL lets you store JSON documents in a column. JSON stores the text as-is; JSONB stores it in an indexed binary format, and it's the one used in practice. It's for semi-structured data whose shape varies (attributes specific to each product, responses from a payment gateway, configurations).

We mention it here so you know it exists; its full use is covered in lesson 10-06. And a warning: JSONB is no excuse for not designing your tables properly. What is structured belongs in columns.

NULL is not zero, nor an empty string, nor FALSE. It means "there's no value here" or "it isn't known".

In GreenStore there are three nulls with a well-defined business meaning:

The essential thing right now is to understand that NULL propagates through any operation:

Any calculation that touches a NULL returns NULL, because operating on something unknown produces something unknown. And as you saw in the previous lesson, to check whether something is null you use IS NULL, never = NULL.

Tell these three cases apart, because they aren't the same thing:

The full treatment of nulls —how they affect aggregations, JOINs and filters— is lesson 04-03. For now the concept is enough.

If you have to port a schema or read somebody else's code, this table will save you time:

The most dangerous difference is in the last row. SQLite uses type affinity: if you declare a column as INTEGER and insert the text 'hello' into it, it stores it without complaint. That's convenient for prototyping and disastrous for guaranteeing integrity. It's the main reason this course uses PostgreSQL.

When you join a new project, the first thing to do is understand its schema. There are two routes.

A single command gives you columns, types, nullability, defaults, indexes, foreign keys and which other tables point at this one. Use \d+ products to see the size and comments as well.

The SQL standard defines a metadata schema called information_schema, available in PostgreSQL, MySQL and SQL Server. Its advantage over \d is that it's ordinary SQL: you can filter it, sort it and use it from any language.

Useful information_schema views:

And a query you'll use often to draw yourself a quick map of an unfamiliar database:

information_schema is the standard, but PostgreSQL also has its own catalogue pg_catalog (pg_tables, pg_class, pg_attribute), more complete and faster, though not portable. The \d metacommands query precisely that catalogue.

• Storing money in FLOAT or REAL. The most expensive mistake in this lesson. Always NUMERIC(10,2).
• Storing dates as text. VARCHAR for a date stops you sorting properly, computing differences and validating. Use DATE or TIMESTAMPTZ.
• Storing numbers that aren't quantities as numbers. A postcode (03001), a phone number or a tax id is text: if you store them as INTEGER you'll lose the leading zero and the +34.
• Writing VARCHAR(255) out of habit. The 255 comes from old MySQL. In PostgreSQL use TEXT unless there's a real business limit.
• Using CHAR(n). The space padding causes subtle errors. It's practically never the right choice.
• Confusing NULL with 0 or ''. They're three different things and they behave differently in filters and aggregations.
• Relying on row order. Without ORDER BY no order is guaranteed, however sorted they may come out today.
• Tip: choose the type with five years in mind. Changing a column's type on millions of rows in production is a delicate operation (module 5).
• Tip: \d table is your first command on anybody else's database. Before writing a query, look at the structure.
• Tip: use plural table names and singular column names. products.name reads better than product.names.

For each piece of GreenStore data, pick the most suitable PostgreSQL type and justify it in one line:

1. A product's selling price.
2. A supplier's country code in ISO format (ES, PT, FR).
3. The comment on a review.
4. A review's rating (1 to 5).
5. The date a customer signed up.
6. Whether a product is active or not.
7. The exact instant a payment was confirmed, with customers in three countries.
8. The discount applied to an order line (a fraction from 0 to 1, two decimals).

Run these expressions and explain what each one demonstrates:

Write a query against information_schema that shows every numeric column in the greenstore database, stating which table it belongs to and with what precision and scale. Sort them by table and by position within the table.

Solution 1

Solution 2

Solution 3

Expected result on GreenStore:

Notice that data_type returns 'numeric' in lowercase, and that precision and scale come in separate columns: information_schema normalises types to the vocabulary of the SQL standard, not to the name you wrote when creating the table.

In this lesson you've walked through the structure SQL acts on:

• The hierarchy server → database → schema (public) → table → columns and rows, and why you can't query two databases at once in PostgreSQL.
• A table is a set of rows with the same structure, with no intrinsic order and one type per column checked by the engine: that's the big difference from a spreadsheet.
• PostgreSQL's catalogue of types: numeric (INTEGER, BIGINT, NUMERIC(p,s), REAL), text (TEXT, VARCHAR(n), CHAR(n)), date and time (DATE, TIMESTAMP, TIMESTAMPTZ, INTERVAL), BOOLEAN, UUID and JSONB.
• The non-negotiable rule: money goes in NUMERIC, never in floating point, because 0.1 + 0.2 <> 0.3 in binary.
• NULL as the absence of a value, distinct from 0 and from '', propagating through every operation.
• The type equivalences between PostgreSQL, MySQL and SQLite, and the danger of SQLite's dynamic type system.
• How to inspect somebody else's database with \dt, \d table and information_schema.columns.

In the next lesson, The Relational Model: Primary and Foreign Keys, we'll see how tables stop being isolated and start relating to one another: what a primary key is and why we use surrogate id columns, how foreign keys guarantee there are no orders from non-existent customers, what happens when you delete a record that others depend on, how 1:1, 1:N and N:M cardinalities are represented —with order_lines as a real example of a bridge table— and how normalisation explains why GreenStore's schema is split into nine tables and not one.