Postgres Reference

Postgres rewards precision. Small choices in types, constraints, joins, and indexing often matter, and the command-line experience adds a second layer: SQL plus psql meta-commands. This reference page is designed for the daily work developers actually do in PostgreSQL: inspect databases, list relations, describe schema, query rows, and define constraints without reopening a full manual every time.

If you searched for a Postgres reference tutorial, an example of Postgres references foreign key, or a reminder about REFERENCES ON DELETE CASCADE, you are in the right place. The aim here is practical PostgreSQL, not abstract SQL theory.

How to use this reference

1. Decide whether you are in SQL mode or psql mode. Both matter in PostgreSQL workflows.
2. Inspect the schema before writing a complex query. Meta-commands are often the fastest path.
3. Keep constraints and data behavior in mind together. In Postgres, design and querying are tightly connected.

Useful Postgres and psql patterns

Start with psql when the schema is unclear

```sql

\l

\c app_db

\dt

\d orders

SELECT id, customerid, status, createdat

FROM orders

ORDER BY created_at DESC

LIMIT 20;

```

This sequence is often faster than guessing table names or column types. PostgreSQL developers sometimes underuse psql meta-commands, but they are one of the reasons Postgres is so productive from the command line. A practical reference should keep both SQL and psql in view.

Foreign keys and REFERENCES in PostgreSQL

A foreign key in Postgres expresses a relationship that the database enforces. The REFERENCES clause points to the target table and column, usually a primary key. That gives you reliable integrity at the storage layer instead of hoping the application always behaves correctly.

```sql

CREATE TABLE orders (

id BIGSERIAL PRIMARY KEY,

customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

);

```

When developers compare Postgres REFERENCES vs foreign key, the practical answer is that REFERENCES is part of the foreign-key definition. You can write it inline on the column or as a named table constraint. The underlying concept is the same: enforce a relationship.

When to use ON DELETE CASCADE

ON DELETE CASCADE can be helpful when child rows should disappear automatically with the parent. For example, deleting an order might reasonably delete its order items. But cascade rules should reflect real ownership, not convenience. If the child row has value or history on its own, automatic deletion may be the wrong rule.

This is why a practical Postgres reference should not only show the syntax, but also the design question behind it. The SQL is easy to copy. The data lifecycle decision is where mistakes usually happen.

Querying effectively in PostgreSQL

Postgres is flexible, but a few habits keep your queries easier to reason about:

• inspect relation structure with \d before joining
• use explicit column lists while exploring
• keep ordering and limit clauses visible during investigation
• use EXPLAIN ANALYZE when performance questions appear
• distinguish clearly between SQL syntax and psql convenience commands

That last point matters more than it seems. A command like \dt is not SQL. It is a psql feature. Keeping that mental boundary clear makes it easier to move between clients and scripts.

Common mistakes to avoid

Common questions

What is the best way to reference the PostgreSQL manual?

Use the full PostgreSQL manual when you need deep semantics or version-specific behavior. Use a page like this when you need the common command or design pattern first.

What does REFERENCES do in Postgres?

It declares a foreign-key relationship so a value in one table must match a valid row in another table.

Is REFERENCES different from a foreign key?

REFERENCES is part of how you express a foreign key. Inline column syntax and explicit table constraints both create the same kind of relationship.

When should I use ON DELETE CASCADE?

Use it when child rows are owned by the parent and should not outlive it. Avoid it when child records need separate retention or audit value.

Related references

Use the MySQL reference for dialect comparison and shared SQL tasks, the VS Code shortcut reference for editor-side navigation while reviewing schema code, the Vim reference for database file editing without leaving the keyboard, or the Bash reference when psql commands become shell automation.