# How to read the PostgreSQL documentation

The PostgreSQL documentation provides technical information about how PostgreSQL works, including the supported SQL syntax and how you can construct queries and work with PostgreSQL. In addition, it sometimes includes examples and explanations about different features.

The documentation is very extensive! You should not aim to read it like you might read a novel, but you should get familiar with it so you can find what you're looking for, when you need to.

In addition, it should be a frequently consulted resource. There is no better place to read what things actually are and what things actually mean than the documentation. Online tutorials and guides (including this one!) might get outdated over time, or sometimes they might just be wrong due to a mistaken writer.

The documentation should be correct and up to date. Check it, and check it often!

You can access the documentation here: https://www.postgresql.org/docs/12/index.html (opens new window).

This is the complete documentation and has many sections (discussed in just a moment). You can access the SQL documentation (section II of the documentation) here: https://www.postgresql.org/docs/12/sql.html (opens new window).

WARNING

The documentation covers different PostgreSQL versions. Make sure you're in the version you want to be in (often, the latest version). If you are working with an older version, remember to select that!

You can usually change the documentation version near the top of the page.

Remember there may be some pages in the documentation that are not available in some versions of the documentation, for example if the feature discussed in that page was not supported in that version.

# Sections of the documentation

The documentation has 8 main sections (referred to as "Parts"):

Part 2: The SQL Language is the main part that we'll be working on when we do Python programming and we use SQL to interact with the database.

If you are new to SQL (shouldn't be if you've been following this course!) then you could check out Part 1: Tutorial.

If you are in charge of deploying PostgreSQL to servers and maintaining those deployments (for example, ensuring the servers don't run out memory or disk space), then you'll want to become familiar with Part 3: Server Administration.

We don't cover that part in this course because we use a Cloud Provider. They have Database Administrators (DBAs) that handle the administration for us. That's why Cloud Providers are usually more expensive than administering your own database: because they do a lot of the work for you.

Part 4: Client Interfaces talks about how Clients (programming languages) can interact with PostgreSQL. For the most part, interaction with PostgreSQL is done with the C programming language, via a library called libpq^[1]. Python's psycopg2 is actually a library that interacts with libpq. libpq then interacts with PostgreSQL. We're technically still using libpq even when we use Python.

You won't need to read through the Client Interfaces section for the most part!

Part 5: Server Programming discusses how to create your own extensions, functions, procedures, and data types in the PostgreSQL deployment. This can greatly enhance and simplify your client-side work, but it can also be challenging because you may need to learn new programming languages to do server programming. Nonetheless, it can be interesting to read through some of the first pages of this section to understand more about how extensibility works^[2].

Part 6: Reference contains a very important chapter: SQL Commands^[3]. You'll be visiting this section often when you search for specific parts of the SQL language and specific clauses within queries. For example, this DROP TABLE reference (opens new window).

Part 7: Internals covers, unsurprisingly, the internal workings of PostgreSQL. This is mostly useful for developers who are working to improve and modify PostgreSQL. We won't be looking at this section at all.

Part 8: Appendixes contains some useful documents, including the PostgreSQL Error Codes (opens new window), Date/Time Support (opens new window), SQL Key Words (opens new window), SQL Conformance (opens new window), and Release Notes (opens new window).

The error codes can come in handy as you'll get errors while working with PostgreSQL, and knowing what they mean can save you a lot of searching on StackOverflow.

Date/Time support can help you store and manipulate dates and times more effectively in PostgreSQL. We'll look at this in a later section as well.

SQL Key Words and SQL Conformance help explain how PostgreSQL uses SQL, and how PostgreSQL is different from the SQL standard.

The Release Notes are good to read when new versions of PostgreSQL are released, so you can stay on top of changes to PostgreSQL just in case anything major changes and you need to make changes to your code. In addition, sometimes PostgreSQL releases new features and you may want to use them in your programs.

# Finding things in the documentation

The easiest way to find things in the documentation is to search in Google and make sure to include "postgresql" in your search terms. The official documentation often pops up, and other times you'll also find written guides and tutorials that can help as well.

If you do go through Google, make sure you select the correct version of the documentation when you click through to the official page.

Of course, you can also search directly in the documentation for whatever you need. In that case you'll skip Google's recommendations for other pages that might be useful, but you'll land in your desired PostgreSQL documentation page faster.

When searching the documentation, try to keep the search terms as simple as possible. If you're searching for how to create tables for example, you'll want to type CREATE TABLE in the search.

# Reading documentation

Here's the important part: reading the actual documentation pages!

Not such an easy thing to do. It'll take time and effort to read documentation, especially if this is the first official documentation you're reading.

Let's use DROP TABLE as an example: https://www.postgresql.org/docs/current/sql-droptable.html (opens new window).

Usually the query and clause reference (which this page is) will contain all the possible queries that you can make using DROP TABLE. At the time of writing, it was this:

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Note:

Things in between square brackets are optional.
Things in bold and italics (not shown in the code above, but shown in the official page) are arguments.
[, ...] means "more of the same"
| means "or"

This means that in order to perform a DROP TABLE query you at least need DROP TABLE and name. Those are things that are not in square brackets.

You can also do DROP TABLE IF EXISTS name.

You can do DROP TABLE if exists name, name2, name3, since the documentation suggests you can have more of the table names.

You can do any of these:

DROP TABLE name CASCADE or DROP TABLE name RESTRICT
DROP TABLE IF NOT EXISTS name CASCADE or DROP TABLE IF NOT EXISTS name RESTRICT
DROP TABLE name, name2 CASCADE (or with RESTRICT)
DROP TABLE IF NOT EXISTS name, name2 CASCADE (or with RESTRICT)

Note that you can't use CASCADE RESTRICT, because the documentation suggests you can only use one of them with CASCADE | RESTRICT.

Below this code you'll get explanations as to what each part of the code means.

For example, under CASCADE it says "Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.14 (opens new window))."

If you're not sure what this means, you should read Section 5.14 (linked) to learn more. It's totally and absolutely normal that you'll go through multiple pages of documentation, painstakingly reading it and its linked pages, until you understand what things mean. I do this daily, and you can do it too!

Afterwards you may get some examples of queries that use DROP TABLE (if you're lucky, sometimes there are no examples). The examples can be really useful, do be careful while reading them as you might learn more from them than by reading the code and explanations!

# What the documentation is not

The documentation is never meant to be a tutorial or guide. It's a technical reference, an explanation of what things are, rather than how to use them. That's the purpose of "Part 1: Tutorial" and also of resources like this course and countless other tutorials and guides published online!

# Wrapping up

That's about it for this chapter! Remember that reading documentation will be difficult, but it gets easier the more you do it!

Every documentation has a particular style, and as you get more accustomed to it, you'll go through faster. Also, as you gain more domain knowledge you'll understand what things mean more easily.

← SQL VIEW: virtual tables Adding type hinting to our application →