postgres schema - ghdrako/doc_snipets GitHub Wiki

schema is a logical namespace that organizes database objects such as tables, views, sequences, indexes, materialized views, as well as custom data types, functions, and operators.

CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]

CREATE SCHEMA AUTHORIZATION username;  -- In this case, the schema will have the same name as the username 
CREATE SCHEMA IF NOT EXISTS schemaname AUTHORIZATION username;

Create a schema and create a table and view within it:

CREATE SCHEMA hollywood
    CREATE TABLE films (title text, release date, awards text[])
    CREATE VIEW winners AS
        SELECT title, release FROM films WHERE awards IS NOT NULL;

if the AUTHORIZATION clause is used, all the created objects will be owned by that user.

PostgreSQL allows schemas to contain objects owned by users other than the schema owner. This can happen only if the schema owner grants the CREATE privilege on their schema to someone else, or a superuser chooses to create objects in it.

According to the SQL standard, the schema owner must own all objects within it. However, Postgres allows objects within a schema to be owned by different users.

List schemas

SELECT 
  * 
FROM 
    pg_catalog.pg_namespace
ORDER BY 
    nspname;

\dn