PostgreSQL / Basic: relational foundations / Lesson 1

PostgreSQL servers, sessions and schemas

Locate a query inside the PostgreSQL architecture and resolve object names deliberately. Separate a cluster, database, schema and client before diagnosing SQL behavior.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Describe the boundaries of a PostgreSQL connection.
Inspect version, identity and schema search rules.

A cluster contains databases, not a distributed promise

In PostgreSQL administration, a database cluster is a collection of databases managed by one server installation and data directory. The word does not itself imply a high-availability cluster. A client connects to one database, which contains schemas organizing tables, views and functions. Ordinary qualified names address schema.table within that database, not arbitrary objects in another database. A web connector and psql are clients of the same server protocol; neither client owns the stored rows. An ordinary client connection is served by a dedicated backend process, while parallel queries can use additional workers. This architecture makes connection concurrency a resource decision rather than an unlimited entitlement.

Read a session fingerprint

The first query identifies the server version, selected database and execution identity. session_user normally identifies the login role, while current_user can change through an authorized SET ROLE or security-definer execution. SHOW search_path explains where unqualified object names are resolved. Actual names depend on the environment, so no hostname or account is invented as observed output. These examples are explanatory and non-runnable: the course never connects to an application database or provisions a service.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT version(), current_database(), session_user, current_user;
SHOW search_path;

Expect one context row and a separate search_path setting. A client executable version is not proof of the remote server version.

Schema lookup is a trust boundary

A schema is a namespace, not an ownership boundary by itself. Names without a schema follow search_path, with special rules for system and temporary schemas. A schema where untrusted roles can create objects should not be trusted for name resolution in privileged code. Use explicit application schemas for important objects. PostgreSQL folds unquoted identifiers to lower case; a quoted MixedCase name remains case-sensitive and must be quoted consistently. Prefer simple lower-case names to reduce avoidable quoting surprises.

Inspect visible objects without assuming their existence

The metadata query inventories ordinary visible table definitions under the synthetic quay_lab schema. An empty result can mean the schema has not been created, the names differ or privileges hide objects. It does not establish a broken server. information_schema offers standardized views; PostgreSQL-specific pg_catalog views expose additional implementation detail. Inspect the view definition for the deployed version before building monitoring around a particular catalog column, especially when upgrading between major releases.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'quay_lab'
ORDER BY table_name;

The expected shape is one row per visible table or view. Row contents depend on an explicitly prepared training fixture.

Separate client commands from SQL

psql commands beginning with a backslash, such as \dt, are interpreted by psql rather than the SQL server. A connector cannot send them as ordinary SQL. Likewise, GO is a SQL Server tool separator, and MySQL DELIMITER is not PostgreSQL SQL. Parameters and transaction defaults belong to the connector contract as well as the server configuration. Record which client executed a diagnostic, what database it targeted and whether an explicit transaction was already open before interpreting an error.

Common errors and fixes

relation does not exist (42P01)

Why it happens: The name resolved in another schema or was quoted with different case.
How to fix it: Inspect current_database and search_path, then use the intended schema-qualified name.
Prevent it next time: Use stable lower-case identifiers and explicit schemas in application migrations.

syntax error at or near "\" (42601)

Why it happens: A psql backslash command was sent through a SQL-only connector.
How to fix it: Use SQL catalog queries or execute the client directive in psql.
Prevent it next time: Label client-specific examples and distinguish them from server statements.

The wrong object answers an unqualified query

Why it happens: A writable or unexpected schema appears in the lookup path.
How to fix it: Identify the resolved object and correct the reviewed naming policy.
Prevent it next time: Keep untrusted creation schemas outside privileged lookup paths.

Check your understanding

1. A machine has psql 18 installed. What evidence proves which server and database accepted a connection?

Show worked solution

Use SELECT version(), current_database() in the intended authorized training session, and record those results separately from the local binary version. A client can connect to a different major server version. Add session identity and search_path when diagnosing object resolution; never infer a database from the directory where the shell command was launched.

2. Two schemas contain parcels. How should a reconciliation report identify the authoritative table?