What’s the difference between a database, schema, and a table? Let’s break it down.
In relational databases, data is organized in a hierarchy, like files and folders. Databases have schemas. Schemas have tables. Tables have columns.
The standard flow is you connect to a database, browse schemas, and query a table’s columns. There are a few differences for this organization for Postgres, Redshift, and MySQL so here are some tips to navigating your database.
Postgres & Redshift
In Postgres and Redshift, you connect to a single database and can only access schemas and their tables within that database.
For example, in Wagon’s demo database, there is a schema named dvd containing tables such as products, orders, and customers.
To query one of these tables, you need to prefix your table with dvd.:
select * from dvd.orders;
If you are frequently querying the same schema, it can be tedious to retype the schema over and over. You can inform the query engine that you’d like to use a specific schema by using set search_path to schema_name;:
select * from orders; -- will generate “relation "orders" does not exist” error!
set search_path to dvd;
select * from orders; -- this works now!
Pro tip: You can set search_path to multiple schemas too: set search_path to schema1, schema2, schema3.
A good question: Is every table in a schema? Tables that are created without a specific schema are put in a default schema called public. This is the default search_path when you first connect to your database.
MySQL
In MySQL, databases and schemas are synonyms. You connect to a specific database, but can still query tables in other databases.
To query data from another database, you must prefix the database name. For example, if you are connected to the dvd database and want to query the baseball database, you must prefix table names with baseball.:
select * from baseball.batting;
To switch databases, run the use command:
use new_database;