intro
In this blog, we will find out everything you need to know about the CREATE SCHEMA PostgreSQL statement
All database administrators have heard about schemas: for some DBAs (MySQL, MariaDB, and Percona Server come to mind), schemas are synonymous with databases, but for others, things aren’t so simple. What do you think the CREATE SCHEMA PostgreSQL statement would translate to? It would create a database, right? Wrong. To answer why, we have to move back and understand the CREATE SCHEMA schemas in PostgreSQL…
Schemas in PostgreSQL
To understand what the CREATE SCHEMA PostgreSQL statement does, we need to take a step back and remember schemas in other database management systems. Those of you who are well-versed in MySQL will be quick to note that in MySQL, schemas are databases, so creating a schema should be the same thing as creating a database, right? Not in PostgreSQL! Check out our PostgreSQL vs MySQL guide to find out all the differences between the two databases.
Once you look at the PostgreSQL documentation, you will be quick to notice that CREATE SCHEMA defines a new schema; attentive readers will also notice that schemas in PostgreSQL are not directly synonymous to databases as is the case in other database management systems as PostgreSQL says that this statement creates a schema inside of an existing database. So, saying that a schema is a database in PostgreSQL would not be true.
So, what is a schema in PostgreSQL? It’s not a database — rather, it’s a namespace; a namespace is not a database: a namespace is a bunch of identifiers within a scope. PostgreSQL’s namespace contains tables, data types, functions, and various operators.
For easier understanding, a schema in PostgreSQL can be imagined like so:
In PostgreSQL, we can create a schema by running the CREATE SCHEMA Postgres statement like so (replace schema_name_here with the actual name of your schema):
1
CREATE SCHEMA schema_name_here;
If the CREATE SCHEMA PostgreSQL query will execute successfully, you will now have a schema in your PostgreSQL database:
Before you run the CREATE SCHEMA query though, there are two things you should keep in mind:
Working with Schemas in PostgreSQL
After you run a couple of CREATE SCHEMA PostgreSQL statements, you will quickly notice that the CREATE SCHEMA query isn’t simple at all; it has multiple options that can be defined to create schemas more efficiently, specify their owner (if you don’t want the user that creates the schema to own it), and various privileges can be granted to grant access to the schema too.
Authorization to schemas can be granted by using the CREATE SCHEMA Postgres statement like this:
1
CREATE SCHEMA [schema_name] AUTHORIZATION your_user;
Granting authorization to a schema is the same as owning the schema. In other words, the user that is authorized to the schema owns the schema.
If you have roles, you can also grant all users with specific role access to a specific schema like so (here schema_name denotes the name of your schema, and role_name denotes the role name):
1
GRANT ALL ON SCHEMA schema_name TO role_name;
After you know how schemas work, you can create objects inside of them. To create a table inside of the schema we just created (demo_schema), we will use an SQL query like so:
1
CREATE TABLE demo_schema.products (
2
product_id INT primary key,
3
product_title CHARACTER (50),
4
customer CHARACTER (50)
5
);
More Schema Secrets in PostgreSQL
PostgreSQL is a very interesting beast — so interesting, in fact, that it can provide you with all the schemas in itself after you run a single query. Not bad, right? Run this query:
1
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;
Since pg_namespace stores all namespaces, after we run this SQL query, DbVisualizer (the PostgreSQL database used in the picture above) will return all the schemas in PostgreSQL. Here:
To create a schema for a specific user, first create a role (make use of a strong password, too):
1
CREATE ROLE [role_name] LOGIN PASSWORD '[Password here]';
Then create a schema for a specific user while making use of the AUTHORIZATION statement like so:
1
CREATE SCHEMA AUTHORIZATION [username];
After that, create a new schema called demo_schema (replace the schema name with a name of your choice) that will be owned by your user (we’ll call the user “Jack” for the purposes of this example):
1
CREATE SCHEMA IF NOT EXISTS demo_schema AUTHORIZATION jack;
Done!
To create objects inside of a schema, make use of the CREATE SCHEMA in conjunction with CREATE [OBJECT] capabilities like so:
1
CREATE SCHEMA demo_schema
2
CREATE TABLE demo_products (
3
customer_id INT NOT NULL,
4
product_date DATE NOT NULL,
5
ship_date DATE NOT NULL,
6
customer CHARACTER (50) NOT NULL
7
)
8
CREATE VIEW product_views AS
9
SELECT ID, ship_date
10
FROM demo_products
11
WHERE ship_date <= CURRENT_DATE;
Then, your schema should have one table — demo_products — and one view called product_views. Cool, yeah? We think so too.
Don’t forget that if you use a top-rated PostgreSQL client — the one developed by the team here at DbVisualizer — you will also be able to see all schemas and their contents over at the left-hand side too:
Best Practices for the CREATE SCHEMA PostgreSQL Statement
Since schemas will support many things in your PostgreSQL architecture, there are a couple of things you should be wary of. First, update data sparingly — too many updates will necessitate updates to existing data and indexes; fewer updates mean less overhead for your database.
Design your database schemas around your queries too — when designing your schema, think of the operations your database will complete and work on and make sure to leverage your indexes, return reasonable amounts of results (e.g. return 50 results if only 50 are necessary by making use of the LIMIT and OFFSET clauses), and make use of caching.
Basically remember that performance basics apply after your schemas are running in your database too.
Summary
This blog has walked you through the ups and downs of the CREATE SCHEMA PostgreSQL statement, as well as prepared you for other things that may occur when you find yourself working with the database that is PostgreSQL. We hope that you’ve found this blog to be informational and useful; make sure to come back to our blog over at TheTable to learn more about database development in the future, follow us on social networks like Twitter and LinkedIn, and until next time.
FAQ
What is the CREATE SCHEMA PostgreSQL statement?
The CREATE SCHEMA PostgreSQL statement allows a user to create schemas in PostgreSQL. Schemas should be thought of as namespaces within PostgreSQL, and they are not to be confused with databases as in MySQL and related database management systems.
What data types are available in PostgreSQL?
PostgreSQL can offer monetary, binary, enumerated data types, network address types, bit string types, text search types, the UUID type unique for Universally Unique Identifiers that identify information in systems with a 128-bit key, the XML data type, and many more data types. Discover all PostgreSQL data types.
What objects go Into a schema in PostgreSQL?
Schemas in PostgreSQL contain various objects ranging from tables, views, triggers, rules, and many other things — see examples in this blog.
How to list all schemas available in PostgreSQL?
All schemas available to be used in PostgreSQL can be listed with an SQL statement like so:
1
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;
This SQL query will list all namespaces ordered by their name (nspname stands for “namespace name”.) Similarly, check out our articles:
What is Vacuuming in PostgreSQL?
Vacuuming is a practice that removes rows marked to be deleted by PostgreSQL and thus helps you save disk space. Vacuuming can be done with a query like VACUUM VERBOSE. If you want to make use of automated vacuuming operations (that is, PostgreSQL will decide what and when to vacuum itself), look into the autovacuum option inside of postgresql.conf and set this option to ON.
How to Observe the Activity within PostgreSQL?
To keep an eye on the queries running within PostgreSQL, track the output of a query like SELECT * FROM pg_stat_activity; — this SQL query will provide you with invaluable information in regards to the current activity going on within PostgreSQL schema, similar to SHOW STATUS in MySQL.
Where can I learn more about PostgreSQL?
To learn more about the specifics and secrets of PostgreSQL and other database management systems, stay updated on our blog — TheTable — and also check out the database channel Database Dive for news around the database space.
