PostgreSQL Creating Schema
The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:
GRANT CREATE ON DATABASE videodb TO student; |
As the student user, you create the app schema with the following syntax:
CREATE SCHEMA app; |
Then, you can query the result as follows:
SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname; |
You should see the following:
nspname | nspowner | nspacl
--------------------+----------+-------------------------------------
app | 16390 |
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
pg_temp_1 | 10 |
pg_toast | 10 |
pg_toast_temp_1 | 10 |
public | 10 | {postgres=UC/postgres,=UC/postgres}
(7 rows) |
If you create a revision_history table without a schema name, it is automatically placed in the public schema, which means an attempt to describe the table will return an error. For example, you create a revision_history table with the following command:
CREATE TABLE app.revision_history ( revision_history_id serial , session_id VARCHAR , TABLE_NAME VARCHAR , revision_id INTEGER ); |
You describe the revision_history table with the following command:
\d revision_history |
It will show the following because there is no revision_history table in the public schema and the default search path only include a schema that shares the name with the student user and the public schema.
Did not find any relation named "revision_history". |
You can show the search path with the following:
show search_path; |
It should return the following, which is a schema that shares the user’s name and public.
search_path ----------------- "$user", public (1 row) |
You set the search path as follows:
SET search_path TO app, "$user", public; |
After you set the search_path, a standard attempt to describe the table will find the table whether it is in the app or public schema. That means the following command:
\d revision_history |
Shows:
Table "app.revision_history"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
revision_history_id | integer | | not null | nextval('revision_history_revision_history_id_seq'::regclass)
session_id | character varying | | |
table_name | character varying | | |
revision_id | integer | | | |
As always, I hope this helps somebody looking for an answer.
Subscribe via RSS