Using your schema in PostgreSQL

You follow an example of how to create an external table in PostgreSQL using your own schema.

  1. Using Postgres, create external tables based on a user-defined schema.
    CREATE SCHEMA bob;
    CREATE TABLE bob.country
    (
        id   int,
        name varchar(20)
    );
    
    insert into bob.country
    values (1, 'India');
    insert into bob.country
    values (2, 'Russia');
    insert into bob.country
    values (3, 'USA');
    
    CREATE SCHEMA alice;
    CREATE TABLE alice.country
    (
        id   int,
        name varchar(20)
    );
    
    insert into alice.country
    values (4, 'Italy');
    insert into alice.country
    values (5, 'Greece');
    insert into alice.country
    values (6, 'China');
    insert into alice.country
    values (7, 'Japan');
  2. Create a user and associate them with a default schema <=> search_path.
    CREATE ROLE greg WITH LOGIN PASSWORD 'GregPass123!$';
    ALTER ROLE greg SET search_path TO bob;
  3. Grant the necessary permissions to be able to access the schema.
    GRANT USAGE ON SCHEMA bob TO greg;
    GRANT SELECT ON ALL TABLES IN SCHEMA bob TO greg;