# MySQL databases vs PostgreSQL schemas: what maps to what

Everybody coming to PostgreSQL from MySQL asks this within the first hour, and
the confusion is not their fault. Both products use the word "schema" and they do
not mean the same thing by it.

**MySQL has two levels. PostgreSQL has three.**

```
MySQL          server  →  database  →  table
PostgreSQL     cluster →  database  →  schema  →  table
```

The trap is assuming database maps to database. It does not. **A MySQL database
is a PostgreSQL schema.** Everything below follows from that one line.

## In MySQL, SCHEMA and DATABASE are the same keyword

Not similar. The same. You can create one with either word and the other will
show it:

```sql
CREATE SCHEMA shop_a;
CREATE DATABASE shop_b;
SHOW DATABASES;
```

Both appear in the list. `information_schema` calls them schemata, which is where
some of the confusion starts:

```sql
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('shop_a', 'shop_b');
```

There is no way to nest one inside the other, and this is the part that surprises
people. Selecting a database first does not make the next `CREATE SCHEMA` land
inside it:

```sql
USE shop_a;
CREATE SCHEMA inner_one;
SHOW DATABASES LIKE 'inner_one';
```

`inner_one` is a sibling of `shop_a`, not a child. There is no level to nest into.

## In PostgreSQL, a schema lives inside a database

Here the two words mean two different things, and both exist at once:

<!-- sql-check: postgres -->
```sql
CREATE SCHEMA sales;
CREATE SCHEMA billing;

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('sales', 'billing');
```

Those are containers **inside** the database you are connected to. Create a table
and the full name has three parts, `database.schema.table`, though you will
rarely write the first one.

## Which is why cross-database joins work in one and not the other

This is the practical consequence, and the reason the mapping matters rather than
being trivia.

In MySQL, two databases on one server are namespaces. Qualify the table name and
join them in a single query:

```sql
CREATE DATABASE crm;
CREATE DATABASE billing;

CREATE TABLE crm.customers (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE billing.orders (id INT PRIMARY KEY, customer_id INT, total INT);

INSERT INTO crm.customers VALUES (1, 'Acme');
INSERT INTO billing.orders VALUES (10, 1, 500);

SELECT c.name, o.total
FROM crm.customers c
JOIN billing.orders o ON o.customer_id = c.id;
```

That returns a row. In PostgreSQL the equivalent is refused before it runs, and
the error says so plainly:

```
ERROR:  cross-database references are not implemented: "otherdb.public.t"
```

A PostgreSQL connection is attached to exactly one database and cannot see into
another. Reaching across needs `postgres_fdw` or `dblink`, which are extensions,
a connection to the other database, and a deliberate decision. It is not a
qualified name away.

**Joining across PostgreSQL schemas is free**, exactly like joining across MySQL
databases, because that is the level they actually correspond to.

## So if you are moving between them

**MySQL to PostgreSQL.** Your databases become schemas inside one PostgreSQL
database, not separate databases. Do it the other way and every join you rely on
stops working. If they genuinely are separate applications sharing a server,
separate databases is right, and you should expect to lose the joins.

**PostgreSQL to MySQL.** Your schemas become databases. The three-part name
flattens to two. Anything relying on `search_path` needs another approach, since
MySQL resolves unqualified names against the one database `USE` selected, and
there is no search path to fall back through.

**Reading either.** `information_schema.schemata` and
`information_schema.tables` exist in both and mean the container above tables in
both. It is one of the few places the vocabulary lines up.

## The one-line version

A MySQL database is a PostgreSQL schema. A PostgreSQL database is a level MySQL
does not have.