Query Data

This page has instructions for making SQL selection queries against CockroachDB from various programming languages.

Before you begin

Before reading this page, do the following:

Note:

Your application should use a retry loop to handle transaction errors that can occur under contention.

Simple selects

icon/buttons/copy
SELECT id, balance from accounts;

For more information about how to use the built-in SQL client, see the cockroach sql reference docs.

icon/buttons/copy
// 'db' is an open database connection

rows, err := db.Query("SELECT id, balance FROM accounts")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
fmt.Println("Initial balances:")
for rows.Next() {
    var id, balance int
    if err := rows.Scan(&id, &balance); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%d %d\n", id, balance)
}

For complete examples, see:

icon/buttons/copy
// ds is an org.postgresql.ds.PGSimpleDataSource

try (Connection connection = ds.getConnection()) {
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT id, balance FROM accounts");

    while (rs.next()) {
        int id = rs.getInt(1);
        int bal = rs.getInt(2);
        System.out.printf("ID: %10s\nBalance: %5s\n", id, bal);
    }
    rs.close();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n",
                      e.getSQLState(), e.getCause(), e.getMessage());
}

For complete examples, see:

icon/buttons/copy
# conn is a psycopg2 connection

with conn.cursor() as cur:
    cur.execute("SELECT id, balance FROM accounts")
    rows = cur.fetchall()
    for row in rows:
        print([str(cell) for cell in row])

For complete examples, see:

Order results

To order the results of a query, use an ORDER BY clause.

For example:

icon/buttons/copy
SELECT * FROM bank ORDER BY balance;
  id | balance |                                               payload
-----+---------+-------------------------------------------------------------------------------------------------------
   0 |    -500 | initial-dTqnRurXztAPkykhZWvsCmeJkMwRNcJAvTlNbgUEYfagEQJaHmfPsquKZUBOGwpAjPtATpGXFJkrtQCEJODSlmQctvyh
   1 |    -499 | initial-PCLGABqTvrtRNyhAyOhQdyLfVtCmRykQJSsdwqUFABkPOMQayVEhiAwzZKHpJUiNmVaWYZnReMKfONZvRKbTETaIDccE
   2 |    -498 | initial-VNfyUJHfCmMeAUoTgoSVvnByDyvpHNPHDfVoNWdXBFQpwMOBgNVtNijyTjmecvFqyeLHlDbIBRrbCzSeiHWSLmWbhIvh
   3 |    -497 | initial-llflzsVuQYUlfwlyoaqjdwKUNgNFVgvlnINeOUUVyfxyvmOiAelxqkTBfpBBziYVHgQLLEuCazSXmURnXBlCCfsOqeji
   4 |    -496 | initial-rmGzVVucMqbYnBaccWilErbWvcatqBsWSXvrbxYUUEhmOnccXzvqcsGuMVJNBjmzKErJzEzzfCzNTmLQqhkrDUxdgqDD
(5 rows)

For reference documentation and more examples, see the ORDER BY syntax page.

Limit results

To limit the results of a query, use a LIMIT clause.

For example:

icon/buttons/copy
SELECT * FROM bank LIMIT 5;
  id | balance |                                               payload
-----+---------+-------------------------------------------------------------------------------------------------------
   0 |       0 | initial-dTqnRurXztAPkykhZWvsCmeJkMwRNcJAvTlNbgUEYfagEQJaHmfPsquKZUBOGwpAjPtATpGXFJkrtQCEJODSlmQctvyh
   1 |       0 | initial-PCLGABqTvrtRNyhAyOhQdyLfVtCmRykQJSsdwqUFABkPOMQayVEhiAwzZKHpJUiNmVaWYZnReMKfONZvRKbTETaIDccE
   2 |       0 | initial-VNfyUJHfCmMeAUoTgoSVvnByDyvpHNPHDfVoNWdXBFQpwMOBgNVtNijyTjmecvFqyeLHlDbIBRrbCzSeiHWSLmWbhIvh
   3 |       0 | initial-llflzsVuQYUlfwlyoaqjdwKUNgNFVgvlnINeOUUVyfxyvmOiAelxqkTBfpBBziYVHgQLLEuCazSXmURnXBlCCfsOqeji
   4 |       0 | initial-rmGzVVucMqbYnBaccWilErbWvcatqBsWSXvrbxYUUEhmOnccXzvqcsGuMVJNBjmzKErJzEzzfCzNTmLQqhkrDUxdgqDD
(5 rows)

For reference documentation and more examples, see the LIMIT/OFFSET syntax page.

Joins

The syntax for a selection query with a two-way join is shown below.

icon/buttons/copy
SELECT
    a.col1, b.col1
FROM
    some_table AS a
    JOIN
    some_other_table AS b
    ON
    a.id = b.id
WHERE
    a.col2 > 100 AND a.col3 > now()
ORDER BY
    a.col2 DESC
LIMIT
    25;

Join performance can be a big factor in your application's performance. For more information about how to make sure your SQL performs well, see Optimize Statement Performance.

See also

Reference information related to this task:

Other common tasks:

YesYes NoNo