Public schema vs custom schemas in PostgreSQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of the public schema in PostgreSQL

What is the main purpose of the public schema in a PostgreSQL database?

A It is used only for temporary tables that disappear after the session ends.

B It is a special schema that stores system tables and cannot be modified by users.

C It is the default schema where all users can create and access tables unless specified otherwise.

D It is a schema that automatically encrypts all data stored within it.

💡 Hint

Think about what happens when you create a table without specifying a schema.

The public schema is the default schema in PostgreSQL. When you create tables without specifying a schema, they go into public. It is accessible by all users by default.

❓ query_result

intermediate

Querying tables in custom schemas

Given two schemas, public and sales, both containing a table named customers, what will the following query return?

SELECT * FROM customers;

Assume the search_path is set to "sales", public.

A Rows from the <code>sales.customers</code> table.

B Rows from the <code>public.customers</code> table.

C An error because the table name is ambiguous.

D No rows because the query does not specify a schema.

💡 Hint

Check the order of schemas in the search_path and how PostgreSQL resolves table names.

PostgreSQL searches schemas in the order listed in search_path. Since 'sales' is first, it finds sales.customers and returns its rows.

📝 Syntax

advanced

Creating a table in a custom schema

Which of the following SQL statements correctly creates a table named orders inside a custom schema called inventory?

A CREATE TABLE orders.inventory (id SERIAL PRIMARY KEY, product TEXT);

B CREATE TABLE orders (id SERIAL PRIMARY KEY, product TEXT) SET SCHEMA inventory;

C CREATE TABLE orders (id SERIAL PRIMARY KEY, product TEXT) IN SCHEMA inventory;

D CREATE TABLE inventory.orders (id SERIAL PRIMARY KEY, product TEXT);

💡 Hint

Remember the syntax to specify schema when creating a table.

To create a table in a specific schema, prefix the table name with the schema name and a dot. Options C and D use invalid syntax.

❓ optimization

advanced

Improving query performance with schemas

You have multiple schemas with tables named transactions. To optimize queries, which approach is best when you want to avoid scanning unnecessary tables?

A Create views in the public schema that join all <code>transactions</code> tables from different schemas.

B Always specify the schema explicitly in queries to target the correct table.

C Keep all tables named <code>transactions</code> in the public schema and rely on search_path.

D Use temporary tables instead of schemas to separate data.

💡 Hint

Think about how PostgreSQL resolves table names and how specifying schema affects query plans.

Specifying schema explicitly avoids ambiguity and ensures the query planner targets the correct table, improving performance.

🔧 Debug

expert

Resolving permission errors with custom schemas

A user tries to query a table inventory.products but gets a permission denied error. The user can query public.products fine. What is the most likely cause?

A The user lacks USAGE privilege on the <code>inventory</code> schema.

B The table <code>inventory.products</code> does not exist.

C The user has no SELECT privilege on <code>public.products</code>.

D The search_path does not include <code>inventory</code> schema.

💡 Hint

Remember that schema permissions control access to objects inside them.

In PostgreSQL, users need USAGE privilege on a schema to access objects inside it, even if they have privileges on the tables.

Practice

1. What is the role of the public schema in PostgreSQL?

easy

A. It is a special schema that cannot contain tables or other objects.

B. It is the default schema where database objects are created if no schema is specified.

C. It is used only for system tables and cannot be modified by users.

D. It automatically encrypts all data stored within it.

Solution

1. Step 1: Understand the default schema concept

   In PostgreSQL, the public schema is created by default in every new database.

2. Step 2: Identify the role of the public schema

   When you create tables or other objects without specifying a schema, they go into the public schema automatically.

3. Final Answer:

   It is the default schema where database objects are created if no schema is specified. -> Option B

4. Quick Check:

   Default schema = public [OK]

Hint: Remember: public schema is default for new objects [OK]

Common Mistakes:

• Thinking public schema is read-only
• Assuming public schema is for system tables only
• Believing public schema encrypts data automatically

2. Which of the following is the correct way to create a new schema named sales in PostgreSQL?

easy

A. CREATE SCHEMA sales;

B. CREATE DATABASE sales;

C. NEW SCHEMA sales;

D. MAKE SCHEMA sales;

Solution

1. Step 1: Recall the SQL syntax for creating schemas

   The correct SQL command to create a schema is CREATE SCHEMA schema_name;.

2. Step 2: Check each option

   CREATE SCHEMA sales; uses the correct syntax. MAKE SCHEMA sales;, CREATE DATABASE sales;, and NEW SCHEMA sales; are invalid SQL commands for creating schemas.

3. Final Answer:

   CREATE SCHEMA sales; -> Option A

4. Quick Check:

   CREATE SCHEMA = create new schema [OK]

Hint: Use CREATE SCHEMA to make new schemas [OK]

Common Mistakes:

• Using CREATE DATABASE instead of CREATE SCHEMA
• Using non-existent commands like NEW SCHEMA or MAKE SCHEMA
• Forgetting the semicolon at the end

3. Given the following commands:

CREATE SCHEMA marketing;
CREATE TABLE marketing.campaigns (id SERIAL PRIMARY KEY, name TEXT);
SET search_path TO marketing, public;
SELECT * FROM campaigns;

What will be the result of the SELECT query?

medium

A. It will return rows from a campaigns table in the public schema if it exists.

B. It will cause an error because the table name is not schema-qualified.

C. It will return all rows from the marketing.campaigns table.

D. It will return no rows because search_path is set incorrectly.

Solution

1. Step 1: Understand search_path usage

   The search_path is set to look first in marketing schema, then public.

2. Step 2: Identify which table is found

   The table campaigns exists in marketing schema, so the query finds and returns data from marketing.campaigns.

3. Final Answer:

   It will return all rows from the marketing.campaigns table. -> Option C

4. Quick Check:

   search_path order finds marketing first [OK]

Hint: search_path order decides which schema is used first [OK]

Common Mistakes:

• Assuming table must be schema-qualified always
• Thinking query errors without schema prefix
• Ignoring search_path order

4. You run this command:

SELECT * FROM sales.orders;

But get the error: ERROR: schema "sales" does not exist.
What is the most likely cause?

medium

A. The sales schema has not been created yet.

B. The orders table does not exist in the public schema.

C. You forgot to set the search_path to include sales.

D. You need to use double quotes around sales.

Solution

1. Step 1: Analyze the error message

   The error says the schema "sales" does not exist, meaning PostgreSQL cannot find that schema.

2. Step 2: Identify the cause

   This usually means the schema was never created or was dropped, so the database has no knowledge of it.

3. Final Answer:

   The sales schema has not been created yet. -> Option A

4. Quick Check:

   Schema missing causes this error [OK]

Hint: Check if schema exists before querying it [OK]

Common Mistakes:

• Assuming search_path affects schema existence errors
• Thinking table absence causes schema not found error
• Trying to fix with quotes unnecessarily

5. You want to organize your database by creating two schemas: hr and finance. You create tables with the same name employees in both schemas. How can you write a query to select all employees from the finance schema without changing the search_path?

hard

A. SELECT * FROM public.employees WHERE schema = 'finance';

B. SET search_path TO finance; SELECT * FROM employees;

C. SELECT * FROM employees WHERE schema = 'finance';

D. SELECT * FROM finance.employees;

Solution

1. Step 1: Understand schema-qualified table names

   When tables have the same name in different schemas, you can specify the schema explicitly before the table name.

2. Step 2: Identify the correct query

   SELECT * FROM finance.employees; uses finance.employees which directly accesses the table in the finance schema without changing search_path.

3. Final Answer:

   SELECT * FROM finance.employees; -> Option D

4. Quick Check:

   Schema-qualified names access tables directly [OK]

Hint: Use schema.table to access specific schema tables [OK]

Common Mistakes:

• Trying to filter by schema column which doesn't exist
• Changing search_path unnecessarily
• Assuming public schema contains all tables