gQuest

My first query

In SQL, you cannot start by writing users: the database needs a table schema first.

We create the schema with a migration, add example rows with a seed, then run a query.

Migration

A migration defines the table schema before the application uses the database. It is SQL usually run during deployment or setup; CREATE TABLE creates the table.

CREATE TABLE "User" (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

Seed

A seed inserts initial or example data after the schema exists. It is also SQL, but it runs after migrations so there is a table to insert into.

INSERT INTO "User" (name, email)
VALUES
  ('Ada Lovelace', 'ada@example.com'),
  ('Grace Hopper', 'grace@example.com');

-- Collect table statistics so EXPLAIN can estimate this tiny table accurately.
ANALYZE "User";

Query

Once the table and rows exist, this SELECT reads every user back without a filter.

SELECT * FROM "User";

Explanation

Add EXPLAIN before a query to ask PostgreSQL how it plans to run it. Plain EXPLAIN does not execute the query; it only builds the plan. EXPLAIN ANALYZE is the version that actually runs the query and reports real timings.

EXPLAIN SELECT * FROM "User";
Loading plan...

Read the plan from left to right:

  • Seq Scan means sequential scan: PostgreSQL plans to read the table from beginning to end.
  • on "User" names the table being scanned.
  • 0.00 is the startup cost, 1.02 is the total cost. These numbers are internal planner units, not milliseconds.
  • rows=2 is PostgreSQL's estimated number of rows this step will return.
  • width=34 is the estimated average row size in bytes.

Postgres Terminology, Everything is a relation

In the PostgreSQL documentation, you might not find much use of terms such asseed andmigration. These are mostly conventions introduced by application frameworks and database tooling rather than fundamental PostgreSQL concepts. The termquery, on the other hand, is used extensively by PostgreSQL.

PostgreSQL and SQL have strong foundations in the relational model. One of its key concepts is a relation, together with SQL statements that operate on data.

As a developer, you can roughly think of a SQL statement as a small program expressed as text and sent to PostgreSQL for parsing, planning, and execution. This is somewhat analogous to passing JavaScript source code toeval() ornode -e. SQL is declarative, though: you generally describe the result or change you want rather than the exact sequence of operations used to produce it.

You can roughly think of a relation as the mathematical concept underlying a table or query result. A relation consists of tuples, which roughly correspond to rows in SQL.

In Postgres this is more than a mathematical analogy: relation is also the literal name for anything the system tracks in its own catalog, pg_class, with an identity of its own. Tables are relations, but so are indexes, sequences, and views — each just gets a different relkind. That is why a sequence, even though it is not a table you INSERT into, can still be read with SELECT * like one: it is a relation too.

Reading a relation

This statement produces a result containing rows from the"User" table:

SELECT * FROM "User";

Changing a relation

This statement changes the stored"User" table by adding a row.RETURNING additionally produces a result containing the inserted row:

INSERT INTO "User" (name, email)
VALUES ('Linus Torvalds', 'linus@example.com')
RETURNING *;

Conceptually, you might imagine the table state before and after theINSERT:

Before:
"User" = {
  (1, "Ada Lovelace", "ada@example.com"),
  (2, "Grace Hopper", "grace@example.com")
}
After:
"User" = {
  (1, "Ada Lovelace", "ada@example.com"),
  (2, "Grace Hopper", "grace@example.com"),
  (3, "Linus Torvalds", "linus@example.com")
}

This is a useful mathematical mental model, but PostgreSQL does not literally construct and store an entirely new table every time anINSERT occurs. PostgreSQL has its own physical storage and MVCC mechanisms for implementing these changes.

Let's select a specific user by email

Instead of reading every user, we can filter the table by a known email.

The result contains only Ada Lovelace's row.

Query

This SELECT looks for one user whose email matches the value in the WHERE clause.

SELECT * FROM "User"
WHERE email = 'ada@example.com';

Explanation

EXPLAIN SELECT * FROM "User"
WHERE email = 'ada@example.com';
Loading plan...

Compared to the first plan, two things changed:

  • PostgreSQL still uses a Seq Scan, so it still reads the whole table from beginning to end.
  • The plan now has a Filter step for the WHERE email = ... condition, so rows that do not match Ada's email are discarded after they are read.

Insert a new user

Tables are not only for reading data. You can also insert new rows into them.

INSERT adds a new row to a table that already exists.

RETURNING sends the inserted row back as the query result.

Query

This INSERT creates a new user and returns the row PostgreSQL added.

INSERT INTO "User" (name, email)
VALUES ('Linus Torvalds', 'linus@example.com')
RETURNING *;

Explanation

EXPLAIN INSERT INTO "User" (name, email)
VALUES ('Linus Torvalds', 'linus@example.com')
RETURNING *;

The plan starts with Insert on "User" because PostgreSQL is writing into the "User" table.

The Result step creates the single row from the literal values in the query; PostgreSQL does not need to scan another table first.

Loading plan...

Try Yourself

Loaded Database:my-first-query.database-init

The database for this lesson is already loaded. Write any query you want and run it directly in your browser.

Exercises

0 OF 3 DONE
1.Add the user Edger W. Dijkstra with email edger@cs.com, and return the inserted row from the INSERT.
2.Add the user Barbara Liskov with email barbara@mit.edu, also returning the inserted row.
3.Select only the user whose email is grace@example.com.

What We Learned

  • SQL command / statementMigration, seed, and query are just conventions. They are labels we use to organize SQL statements by purpose. PostgreSQL receives SQL statements; it does not receive a special migration, seed, or query object.
    A migration is a versioned set of statements that changes the database structure or data.
    A seed is a set of statements that populates predefined data, usually after the required tables exist.
    A query is a statement we run to read or change data, often a SELECT statement. These labels describe when and why the statements run, not different kinds of SQL.
  • CREATE TABLEDefines a new table and its columns and constraints.
  • ANALYZECollects table statistics that PostgreSQL uses when planning queries.
  • INSERTAdds new rows to an existing table, whether as seed data or application data.
  • SELECTReads rows from one or more tables.
  • EXPLAINShows the execution plan PostgreSQL expects to use for a query.
  • Planner cost unitsThe cost values in an EXPLAIN plan are internal planner units, not milliseconds.
  • WHEREFilters rows so only records matching a condition remain.
  • RETURNINGReturns rows affected by INSERT, UPDATE, or DELETE.