Query Data

On this page Carat arrow pointing down
Warning:
As of November 12, 2021, CockroachDB v20.1 is no longer supported. For more details, refer to the Release Support Policy.

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

Before you begin

Make sure you have already:

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:

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 Query Performance.

Pagination

For pagination queries, we strongly recommend keyset pagination (also known as "the seek method"). The syntax for a keyset pagination query is shown below.

icon/buttons/copy
SELECT * FROM t AS OF SYSTEM TIME ${time}
  WHERE key > ${value}
  ORDER BY key
  LIMIT ${amount};

For a tutorial explaining keyset pagination queries and showing how to write them, see Paginate through limited results.

Query optimization

For instructions showing how to optimize your SQL queries, see Optimize Query Performance.

See also

Reference information related to this task:

Other common tasks:


Yes No
On this page

Yes No