Schema

What is a schema in PostgreSQL?

Schema is a collection of logical structures of data. In PostgreSQL, schema is a named collection of tables, views, functions, constraints, indexes, sequences etc.

PostgreSQL supports having multiple schemas in a single database there by letting you namespace different features into different schemas.

For example, you can have a database named postgres and have multiple schemas based on your application like ecommerce, auth etc.

Here's the hierarchy:

  • PostgreSQL can have multiple databases in each instance.
  • Each database can have multiple schemas.
  • Each schema can have multiple tables.

Creating a Schema in PostgreSQL

The SQL syntax for creating a schema in PostgreSQL looks like:

CREATE SCHEMA <schema_name>;

where you can replace <schema_name> with a name of choice.

Head to the psql terminal window and execute the following to create a new schema.

CREATE SCHEMA ecommerce;

You should see the following output:

postgres=# CREATE SCHEMA ecommerce;
CREATE SCHEMA

Dropping a Schema in PostgreSQL

The SQL syntax for dropping a schema in PostgreSQL looks like:

DROP SCHEMA <schema_name>;

and in case you want to cascade delete all referenced objects, you can make use of

DROP SCHEMA <schema_name> CASCADE;

and replace <schema_name> with the name of choice of your schema.

Applying this to our newly created schema, this would translate to,

DROP SCHEMA ecommerce;

Fundamentally, schemas let users namespace their various application features, especially third-party stuff to have their own space and not interfere with the primary data source.

Especially with role based access, it's easier to restrict access to schemas.

Default Schema in PostgreSQL

By default, the public schema is used in PostgreSQL when you set it up for the first time. Of course, you can create and drop more.

Any SQL queries executed will run against the public schema by default unless explicitly mentioned.

Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin