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.