Skip to main content

​
Overview

Metadb provides each user with a personal workspace—a database schema with the same name as the username. In this schema, users have full privileges to create and modify tables, making it ideal for saving query results, importing external datasets, or building personal data transformations.
Users generally do not have privileges to modify or create tables in shared schemas. Your personal workspace is the designated area for these operations.

​
Creating Tables in Your Workspace

You can create tables in your personal schema using standard SQL commands.

​
Example: Saving Query Results

Suppose you’re the user celia and want to save a filtered dataset:
1

Create a table from a query

CREATE TABLE celia.westgroup AS
    SELECT * 
    FROM library.patrongroup 
    WHERE __origin = 'west';
This creates a new table in your workspace containing only the filtered data.
2

Query your saved table

SELECT * 
FROM celia.westgroup 
LIMIT 20;
Access your saved data at any time.

​
Use Cases for Workspaces

Query Results

Save intermediate results from complex analyses for later use

External Data

Import datasets from external sources for integration with Metadb data

Data Transformations

Create derived tables with custom business logic

Personal Reports

Build summarized tables for recurring analysis needs

​
Sharing Workspace Data

By default, other users cannot access tables in your workspace. You can grant specific privileges to enable controlled sharing.

​
Granting Read Access

1

Grant schema access

First, allow the user to access your schema:
GRANT USAGE ON SCHEMA celia TO rosalind;
2

Grant table permissions

Then grant specific permissions on the table:
GRANT SELECT ON celia.westgroup TO rosalind;
The user rosalind can now query the table.
3

Other users can access the table

User rosalind can now run:
SELECT * FROM celia.westgroup;
The GRANT USAGE ON SCHEMA command only needs to be run once per user. Subsequent table grants for the same user don’t require repeating this step.

​
Sharing with Multiple Users

You can grant access to multiple users in a single statement: 
GRANT USAGE ON SCHEMA celia TO rosalind, james, maria;

GRANT SELECT ON celia.westgroup TO rosalind, james, maria;

​
Permission Levels

While read-only access (SELECT) is most common, you can grant other permissions as needed:
PermissionDescriptionUse Case
SELECTRead data from tableAllow others to query your results
INSERTAdd new rowsCollaborative data collection
UPDATEModify existing rowsShared data maintenance
DELETERemove rowsCollaborative data curation
ALLAll permissionsFull collaboration on a table
Be cautious when granting INSERT, UPDATE, or DELETE permissions. These allow other users to modify your data.

​
Best Practices

​
Organize Your Workspace

Use clear, descriptive table names: 
-- Good naming
CREATE TABLE celia.loan_summary_2023 AS ...
CREATE TABLE celia.patron_demographics AS ...

-- Avoid unclear names
CREATE TABLE celia.temp1 AS ...
CREATE TABLE celia.data AS ...

​
Document Shared Tables

Use PostgreSQL comments to document tables you share with others: 
COMMENT ON TABLE celia.westgroup IS 
    'Patron groups from western region only. Updated: 2023-01-15. Source: library.patrongroup';

​
Clean Up Temporary Tables

Regularly remove tables you no longer need: 
DROP TABLE IF EXISTS celia.temp_analysis;
DROP TABLE IF EXISTS celia.old_results;

​
Consider Storage Limits

Workspace tables consume database storage. Coordinate with your system administrator if you need to store large datasets.

​
Examples

​
Example 1: Building a Personal Mart

-- Create a frequently-used summary table
CREATE TABLE maria.monthly_circulation AS
SELECT 
    DATE_TRUNC('month', loan_date) AS month,
    item_id,
    COUNT(*) AS loan_count
FROM folio_circulation.loan__t
WHERE loan_date >= '2022-01-01'
GROUP BY DATE_TRUNC('month', loan_date), item_id;

-- Share with the analytics team
GRANT USAGE ON SCHEMA maria TO analytics_user;
GRANT SELECT ON maria.monthly_circulation TO analytics_user;

​
Example 2: Combining Multiple Sources

-- Combine data from different origins
CREATE TABLE john.combined_patrons AS
SELECT 
    __origin AS campus,
    groupname,
    COUNT(*) AS patron_count
FROM library.patrongroup
WHERE __origin IN ('east', 'west', 'north')
GROUP BY __origin, groupname;

​
Example 3: Staging External Data

-- Create a table for imported CSV data
CREATE TABLE sarah.external_book_ratings (
    isbn VARCHAR(13),
    rating NUMERIC(3,2),
    review_count INTEGER,
    import_date DATE DEFAULT CURRENT_DATE
);

-- Then use COPY or INSERT to populate it

​
Integration with Reporting Tools

Workspace tables integrate seamlessly with database tools like CloudBeaver:
  1. Create summarized or filtered tables in your workspace
  2. Grant read access to report users
  3. Reference workspace tables in dashboards and reports
  4. Users query your prepared datasets without accessing raw data
Combine user workspaces with database functions for powerful, shareable analytics.

Build docs developers (and LLMs) love

Get started for freeTalk to us