Public schema vs custom schemas in PostgreSQL - Quick Revision & Key Differences

Recall & Review

beginner

What is the public schema in PostgreSQL?

The public schema is the default schema created in every new PostgreSQL database. It is a shared space where database objects like tables and functions are stored if no other schema is specified.

beginner

Why would you create a custom schema in PostgreSQL?

Custom schemas help organize database objects into separate namespaces. This improves clarity, security, and management by grouping related objects and avoiding name conflicts.

intermediate

How does PostgreSQL find a table when you run a query without specifying a schema?

PostgreSQL searches schemas in the order listed in the search_path setting. By default, it looks in the public schema first, then others if configured.

intermediate

Can multiple schemas have tables with the same name in PostgreSQL?

Yes. Schemas act like folders. Different schemas can have tables with the same name without conflict because the full name includes the schema name.

beginner

How do you specify a table in a custom schema in a SQL query?

Use the format schema_name.table_name. For example, sales.orders refers to the orders table in the sales schema.

What is the default schema in a new PostgreSQL database?

A custom

B default

C main

D public

The default schema created in every new PostgreSQL database is called 'public'.

Why use custom schemas instead of only the public schema?

A To organize objects and avoid name conflicts

B To slow down queries

C To delete the public schema

D To store backups

Custom schemas help organize database objects and prevent name conflicts by grouping related objects.

If two schemas have a table named 'users', how does PostgreSQL know which one to use?

A It throws an error

B It merges both tables automatically

C It uses the search_path order or explicit schema name

D It always uses the public schema

PostgreSQL uses the search_path order or requires you to specify the schema explicitly to know which table to use.

How do you write a query to select from a table named 'orders' in a schema named 'sales'?

A SELECT * FROM sales.orders;

B SELECT * FROM orders.sales;

C SELECT * FROM public.orders;

D SELECT * FROM orders;

The correct format is schema_name.table_name, so 'sales.orders' selects the orders table in the sales schema.

What happens if you create a table without specifying a schema in PostgreSQL?

A The creation fails

B It is created in the public schema by default

C It is created in a new schema

D It is created in the root directory

If no schema is specified, PostgreSQL creates the table in the public schema by default.

Explain the difference between the public schema and custom schemas in PostgreSQL.

Think about why you might want to separate tables into different groups.

Describe how PostgreSQL uses the search_path to find tables when no schema is specified.

Imagine looking for a file in folders one by one.