Skip to content

MySQL databases vs PostgreSQL schemas: what maps to what

Last updated

View as Markdown

Quick answer: A MySQL database is a PostgreSQL schema. MySQL has two levels below the server, database then table, and treats SCHEMA and DATABASE as the same keyword. PostgreSQL has three: database, then schema, then table. That is why cross-database joins work in MySQL and not in PostgreSQL. When you migrate to PostgreSQL, your databases become schemas inside one database. When you migrate to MySQL, your schemas become databases and search_path needs another approach.

People moving from MySQL to PostgreSQL often ask about this distinction. The confusion is understandable: both products use the word “schema” but mean different things by it.

MySQL has two levels. PostgreSQL has three.

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

Database does not map to database. A MySQL database is a PostgreSQL schema. The rest follows from that.

In MySQL, SCHEMA and DATABASE are the same keyword

Section titled “In MySQL, SCHEMA and DATABASE are the same keyword”

They are identical. You can create one with either word, and the other will show it:

CREATE SCHEMA shop_a;
CREATE DATABASE shop_b;
SHOW DATABASES;

Both appear in the list. information_schema calls them schemata, which can cause confusion:

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

You cannot nest one inside the other. Selecting a database first does not make the next CREATE SCHEMA land inside it:

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

inner_one is a sibling of shop_a, not a child.

In PostgreSQL, a schema lives inside a database

Section titled “In PostgreSQL, a schema lives inside a database”

Here, the two words refer to different objects, and both exist at once:

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

Section titled “Which is why cross-database joins work in one and not the other”

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

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 an extension (postgres_fdw or dblink), a connection to the other database, and a deliberate decision. Qualifying the name is not enough.

Joining across PostgreSQL schemas works freely, just as joining across MySQL databases does, because MySQL databases correspond to PostgreSQL schemas.

So if you are moving between them

Section titled “So if you are moving between them”

MySQL to PostgreSQL. Your databases become schemas inside one PostgreSQL database, not separate databases. If you put each database in its own PostgreSQL database, every join between them stops working. If they really 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. This is one of the few places where the vocabulary matches.

The one-line version

Section titled “The one-line version”

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

FAQ

Section titled “FAQ”
  • Is a schema the same as a database in MySQL?

    Yes. In MySQL, CREATE SCHEMA and CREATE DATABASE do exactly the same thing, and SHOW DATABASES lists whatever you created either way. MySQL has no separate schema layer inside a database.

  • What does a MySQL database map to in PostgreSQL?

    A schema, not a database. Both are the container directly above tables, both are the qualifier in a two-part name, and both can be joined across freely in a single connection. A PostgreSQL database is a level above that, with no MySQL equivalent.

  • Why can MySQL join across databases when PostgreSQL cannot?

    Because a MySQL database is not an isolation boundary in the way a PostgreSQL database is. A PostgreSQL connection is attached to exactly one database and cannot see into another, so a cross-database reference is rejected outright. MySQL databases are namespaces inside one server, so qualifying a table name with the database is enough.

  • How many parts does a fully qualified table name have?

    Two in MySQL, database.table. Three in PostgreSQL, database.schema.table, though the database part is usually left off because a connection can only reach one database anyway.

Section titled “Related”