How to Read Postgres Docs

March 21, 2022 - 3 minutes read - 527 words

Have you ever tried to read the documentation for a Postgres command? Although many consider Postgres’ docs best-in-class, they include conventions that might challenge newbies. In this post, I’ll paraphrase Postgres’ conventions page to help us understand what the documentation for a command is saying.

To check the syntax of a command, I start with psql, the REPL, or ‘Read-Evaluate-Print-Loop’, of Postgres. I query using \help , and get output like this for update:

jakes_db=# \help update
Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/14/sql-update.html

I recommend reading docs in the REPL when you can. irb for Ruby, node or the DevTools console for JavaScript, etc. With history enabled, you’ll get a searchable record of the pages you’ve read, and you can guarantee you’re reading the docs for your version of the software.

Let’s read through this REPL output. We receive the following pieces of information:

Command
Description
Syntax
URL

Command is the thing I searched for. SQL convention presents tokens in capitalized format, so that’s why I asked for help about update and Postgres gave me help about UPDATE. That format is optional.

Next we have our description: “update rows of a table”.

Into the heart of the page, the syntax. For this, we’ll need to familiarize ourselves with Postgres’ conventions. These include:

Brackets ([ and ]): optional parts.
Braces ({ and }) and vertical lines (|): choose one.
Dots (...): the preceding element can be repeated.
All other symbols, including parentheses: taken literally.

I’ve found that these syntax guides are helpful if you read through them slowly aloud. Here’s how I’d do that for the UPDATE page:

“With UPDATE, I can optionally start with a recursive WITH statement. Then I’m into my statement, which I apply to a table using table_name. Next, I can SET my new value in several ways. I can use FROM to include columns from other tables. Then, I can limit what I update using WHERE. Finally, I can use RETURNING to select from what I return.”

With this knowledge, I can build my query:

=> update films set kind = 'dramatic' where kind = 'drama';

Most of the time, you’ll be using just a few of these tokens.

You can click on the included URL to visit the online docs page, where each token is described in detail. I think Postgres’ docs are some of the best around, so the answers are there if you keep looking.

These pages are approachable with patience and curiosity. SQL is a quirky language and competence takes time. Learning to read these docs is a great first step.

Postgresql