Drew C King

Lately I’ve been forcing myself to learn PostgreSQL as a replacement for MySQL. In my experimentation, one of the first things I had to figure out how to do was simulate auto-incrementing. In MySQL it’s as simple as sticking the word AUTO_INCREMENT in the column definition, but PostgreSQL doesn’t have that. What it has instead is a totally separate database object called a SEQUENCE, which is essentially just a single-row table used especially to provide for this type of functionality.

For example, in order to create a simple table to store user records, you’d first create the sequence that will be used to generate the unique user IDs:

CREATE SEQUENCE user_seq

Then create the user table and tell its user_id column to use the sequence as the default value:

CREATE TABLE user
(
  user_id INTEGER DEFAULT NEXTVAL('user_seq'),
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

After this, the first user record that is inserted will get a value of 1, and successive insertions will be incremented appropriately, just like AUTO_INCREMENT.

Because this is such a common practice, PostgreSQL lets you take a little shortcut, via the SERIAL column type (thought technically speaking, it’s not actually a true data type). Using this, you can simply skip the sequence creation, and just specify your table like so:

CREATE TABLE user
(
  user_id SERIAL,
  email CHARACTER VARYING(50),
  password CHARACTER(40)
)

This will create a sequence called user_user_id_seq behind the scenes (i.e., table_column_seq)

Jody clued me into a convenient method of paginating a result set from MySQL. Since he hasn’t blogged about it yet, I will >:-)

In the past, I’d issue two queries back to back, similar to this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

<?php
    $page = isset($_GET['page']) ? (int) $_GET['page']: 1;
    $size = 10;
    $offset = ($page - 1) * $size;
 
    $db = new mysqli("localhost", "drew", "mypass", "db");
 
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
 
    $users = array();
    $total = 0;
 
    if ($db->multi_query($sql) && $res = $db->store_result())
    {
        // fetch this page of user records
        while (list($name) = $res->fetch_row())
        {
            $users[] = $name;
        }
 
        // fetch the TOTAL number of users
        $db->next_result();
        list($total) = $db->store_result()->fetch_row();
    }
 
    $pages = ceil($total/$size);
 
    echo "Total Users: $total<br>";
    echo "Total Pages: $pages<br>";
    echo "Current Page: $page<br><br>";
 
    foreach ($users as $u) echo "$u<br>";
 
    if ($page > 1) 
        echo '<a href="?page=', $page-1, '">&laquo; Prev</a>';
 
    if ($page > 1 && $page < $pages) 
        echo " | ";
 
    if ($page < $pages) 
        echo '<a href="?page=', $page+1, '">Next &raquo;</a>';
?>

Take note of Line #8. The first query grabs the specified page of results, the second simply does a full count of all rows so I could calculate the total number of pages.

What sucks, however, is if the query is more complex, especially if the various parts of it have to be dynamically generated, e.g., table joins, conditions in the where clause, etc. In such a case you’d have to change the second query to get rid of the column list SELECT column1, column 2... and make it SELECT COUNT(*). Also, you’d have to toss out any ORDER BY and LIMIT clauses.

MySQL allows you to simplify this extremely common scenario by using the FOUND_ROWS() function. In the above code, you can simply modify the query itself:

1
2
3
4
5
6
7
8
9

<?php
    // ... change this:
    $sql = "SELECT username FROM user LIMIT $offset, $size;"
         . "SELECT COUNT(*) FROM user";
 
    // ... to this:
    $sql = "SELECT SQL_CALC_FOUND_ROWS username FROM user LIMIT $offset, $size;"
         . "SELECT FOUND_ROWS()";
?>

The rest of the code remains the same and will give you the exact same results.

The speed of this approach can vary depending on how you have your table(s) indexed, and the complexity of the query itself. In other words, in some circumstances it may be faster to actually run the LIMITed query followed by the appropriate SELECT COUNT(*) FROM ... query as in the first example, but the primary benefit is in the simplification of the query itself.