Working with Databases and Schemas in PostgreSQL

Introduction to PostgreSQL Databases and Schemas

PostgreSQL is a powerful, open-source object-relational database system. Unlike some other database systems like MySQL or MS SQL Server, PostgreSQL doesn’t have a direct USE command to switch between databases within a single connection. This tutorial explains how PostgreSQL manages databases and schemas, and how to effectively work with them. We’ll cover connecting to a specific database, and changing the search path (schema) within a session.

Understanding Databases and Schemas

In PostgreSQL, a database is a container for one or more schemas. Think of a database as a high-level organization unit, and schemas as subdivisions within that database. A schema is a namespace that organizes database objects like tables, views, functions, and procedures.

This two-level structure provides greater flexibility and organization compared to systems that rely solely on databases. Multiple users can share a single database, each working within their own schema, preventing naming conflicts and enhancing security.

Connecting to a Database

When you initially connect to a PostgreSQL server, you must specify the database you want to work with. This is typically done through the connection string or command-line arguments when using tools like psql or a database client like pgAdmin.

Using psql:

psql -d <database_name> -U <username>

Replace <database_name> with the name of the database you want to connect to and <username> with your PostgreSQL username. This command establishes a connection to the specified database immediately.

Using pgAdmin:

When creating a new server connection in pgAdmin, you’ll be prompted to specify the database to connect to. You can also change the connected database by right-clicking on the server in the object browser and selecting “Connect To…”

Changing the Search Path (Schema) Within a Session

Once connected to a database, you may need to work with different schemas. PostgreSQL uses a search_path to determine the order in which schemas are searched for objects. By default, the search_path typically includes the public schema.

To change the current schema for your session, use the SET search_path command:

SET search_path TO <schema_name>;

For example, to switch to a schema named myschema:

SET search_path TO myschema;

After executing this command, any unqualified table or function names you use will be resolved within the myschema schema.

Example:

-- Assuming you have a table named 'customers' in the 'myschema' schema
SET search_path TO myschema;
SELECT * FROM customers; -- This will now access the 'customers' table in 'myschema'

You can include multiple schemas in the search_path, separated by commas:

SET search_path TO myschema, public;

PostgreSQL will search myschema first, and if an object isn’t found there, it will then search the public schema.

Restoring the Default Search Path:

To revert to the default search_path (usually public), you can use:

SET search_path TO public;

Important Considerations

• Permanent Schema Changes: Modifying the search_path only affects the current session. To permanently change the search_path for a user, you would need to modify the user’s configuration or alter the database configuration (which is generally not recommended).

• Schema Qualification: You can always explicitly qualify table names with the schema name, regardless of the search_path:

  SELECT * FROM myschema.customers;
  

  This is a good practice to avoid ambiguity and improve code readability, especially when working with multiple schemas.

• Database vs. Schema: Remember that switching databases requires closing the current connection and opening a new one. Switching schemas, however, is done within an existing connection.