Lesson 1.5 · Reading time: ~7 min

Besides numbers, text and dates, databases have complex data types for values with an inner structure: documents, lists, files, unique identifiers, locations and vectors for AI search. This lesson is a short tour: what each type stores, an example value, and which databases support it. You don't need to remember the syntax — the goal is to recognize these types when you meet them in a table.

Complex Data Types in SQL

In the previous lesson every column held one simple value: a number, a string or a date. Some data doesn't fit that well: a product's set of attributes, a scanned contract, a point on a map. For such data, databases offer special types.

JSON: Documents

A JSON column stores a whole document with keys, values, nested objects and lists. In the University database, a research project keeps its grants in one JSON column:

[{ "source": "Private Foundation", "amount": 244542.96, "year": 2020}]

• MySQL and MariaDB have the JSON type; PostgreSQL has json and the faster binary jsonb; SQL Server and SQLite store JSON as text and read it with JSON functions.
• JSON suits attributes that differ from row to row or come from another system as a document.

XML is the older document format with the same purpose; PostgreSQL and SQL Server have an xml type for it.

Arrays and Nested Structures

An array is a list of values in one cell, like [1, 2, 3]. PostgreSQL supports arrays of any type (integer[], text[]), and analytical databases such as DuckDB add LIST, STRUCT and MAP. MySQL, MariaDB, SQL Server and SQLite have no array type: a list goes into JSON or, more often, into a separate table with one row per item.

ENUM and SET: a Fixed List of Values

ENUM allows exactly one value from a list declared with the column. In Sakila, film.rating is ENUM('G', 'PG', 'PG-13', 'R', 'NC-17'), so a film can't get a rating outside this list. SET, a MySQL and MariaDB type, allows any combination of listed values: film.special_features holds values like Trailers,Deleted Scenes.

PostgreSQL creates enum types separately (CREATE TYPE ... AS ENUM); other databases use a regular column with a check or a lookup table.

BLOB: Binary Data

BLOB (Binary Large Object) stores bytes: images, PDF files, archives. In Sakila, staff.picture is a MEDIUMBLOB with an employee's photo. The type is called BLOB in MySQL, MariaDB and SQLite, bytea in PostgreSQL and VARBINARY(MAX) in SQL Server.

Large files make a database heavy to back up and slow to copy, so applications often keep files in file storage and save only the path or URL in a text column.

UUID: Unique Identifiers

A UUID is a 128-bit identifier like a1c7abde-ccc7-11f1-92da-da8de233f452, unique across tables, servers and systems. It's used when rows are created in many places at once and a simple counter can't be shared. PostgreSQL and MariaDB have a uuid type, SQL Server calls it UNIQUEIDENTIFIER, MySQL keeps UUIDs in CHAR(36) or BINARY(16).

Spatial Types: Points and Shapes

Spatial types store geometry: points, lines and polygons. In the Bookings database each airport has coordinates of the point type, such as (129.77, 62.09) for Yakutsk. MySQL and MariaDB have GEOMETRY and POINT, SQL Server has geometry and geography, and PostgreSQL gets full support from the PostGIS extension. Spatial functions measure distances, find what lies inside an area or which objects are nearest.

VECTOR: Embeddings for AI Search

A VECTOR is a fixed-length list of numbers, for example [0.1, 0.2, 0.3]. AI models turn texts, images and products into such vectors (embeddings) so that similar things get close vectors. A database can then find the rows whose vectors are nearest to a query vector: "documents similar to this question". This is the basis of semantic search and RAG applications.

• MariaDB has VECTOR(n) since version 11.7, with functions such as VEC_DISTANCE_COSINE.
• MySQL added VECTOR in version 9, SQL Server in version 2025.
• PostgreSQL gets vectors from the pgvector extension.

Complex Types in Different Databases

When to Use a Complex Type

A complex type is a good choice when the value is used as a whole or needs special search: a document that is stored and returned as is, a photo, an embedding for similarity search. If you often filter, join or count by a part of the value — a key inside JSON, an item of an array — that part usually deserves its own column or table. This keeps queries simple and lets the database use ordinary indexes.

Key Takeaways:

• Complex types store values with inner structure: documents, lists, files, identifiers, locations and vectors.
• JSON holds documents; PostgreSQL also has jsonb, arrays and xml.
• ENUM and SET limit a column to a declared list of values.
• BLOB, bytea and VARBINARY store binary files; large files are often kept outside the database.
• UUID gives identifiers unique across systems; spatial types store points and shapes.
• VECTOR stores AI embeddings for similarity search; support depends on the database and its version.

Frequently Asked Questions

Should I store images in a BLOB column?

Small images are fine, but large files make the database big and backups slow. A common approach is to keep files in file storage and save their path in the database.

Is it better to use JSON or separate columns?

Use separate columns for data that every row has and that you filter or join on. Use JSON for attributes that vary between rows or arrive as a ready document.

What is a vector in a database?

A fixed-length list of numbers that represents the meaning of a text, image or other object. Similar objects have close vectors, so the database can find the nearest ones to a query.

Interview Questions

What complex data types do you know, and when would you use them?

JSON for semi-structured documents, arrays for lists in PostgreSQL, ENUM for a fixed set of values, BLOB for binary files, UUID for globally unique identifiers, spatial types for locations and VECTOR for embeddings in similarity search. Each is used when the value is handled as a whole or needs its special operations.

What are the downsides of storing data in JSON instead of columns?

Values inside JSON are harder to validate, constrain, join and index; queries become longer, and type errors are found only when the data is read.

Why do vector databases matter for AI applications?

AI models represent texts and images as embeddings. A database with a vector type and nearest-neighbor search finds the most similar items quickly, which is the core of semantic search and retrieval-augmented generation (RAG).