Anatomy of a Select Statement

The simplest select statement in PostgreSQL is as follows:

In other systems, the from clause is required, and sometimes a dummy table with a single row is provided so that we can select from this table.

Projection (output): select

The SQL select clause introduces the list of output columns. This is the list of data that we’re going to send back to the client application, so it’s quite important: the only reason the server is executing any query is to return a result set where each row presents the list of columns specified in the select clause. This is called a projection.

Adding a column to the select list might involve a lot of work, such as the following:

• Fetching data on disk
• Possibly uncompressing data that is stored externally to the main table on-disk structure and loading those uncompressed bytes into the memory of the database server
• Sending the data over the network back to the client application

Given that, it’s usually frowned upon to use either the infamous select * notation or the classic approach to fully hydrate the application objects just in case.

The following shortcut is nice to have in interactive mode only:

The actual standard syntax for limit is a little more complex:

It gives the following result:

─[ RECORD 1 ]──────────────────────────────────────────────────────
raceid    │ 1
year      │ 2009
round     │ 1
circuitid │ 1
name      │ Australian Grand Prix
date      │ 2009-03-29
time      │ 06:00:00
url       │ http://en.wikipedia.org/wiki/2009_Australian_Grand_Prix

Note that ...