O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

How many rows in a table?

Counting is one of the easiest SQL statements, so it is also many people's first experience of a Postgres query.

How to do it...

From any interface the SQL command is, as follows:

SELECT count(*) FROM table;

which returns a single integer value as the result.

In psql this looks like the following:

postgres=# select count(*) from orders;
count
───────
345
(1 row)

How it works...

The SQL count(*) function will scan every row in the table using a technique named a Sequential Scan. We access every data block in the table one after the other, reading the number of rows in each block. If the table is on disk, then this will cause a beneficial disk access pattern, and the statement will be fairly fast.

Some people think that the count ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required