Today I Learned

hashrocket A Hashrocket project

Default Schema

Schemas can be used to organize tables within a database. In postgres, we can see all the schemas our database has like so

> select schema_name from information_schema.schemata;
(6 rows)

When you create a new table, it will need to be placed under one of these schemas. So if we have a create table posts (...), how does postgres know what schema to put it under?

Postgres checks your search_path for a default.

> show search_path;
 "$user", public
(1 row)

From our first select statement, we see that there is no schema with my user name, so postgres uses public as the default schema.

If we set the search path to something that won't resolve to a schema name, postgres will complain

> set search_path = '$user';
> create table posts (...);
ERROR:  no schema has been selected to create in
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.