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:
- Create a CockroachDB Standard cluster or start a local cluster.
- Install a Driver or ORM Framework.
- Connect to the database.
- Insert data that you now want to run queries against.
When running under the default SERIALIZABLE isolation level, your application should use a retry loop to handle transaction retry errors that can occur under contention.
Simple selects
SELECT id, balance from accounts; For more information about how to use the built-in SQL client, see the cockroach sql reference docs.
// '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:
// 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:
# 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:
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:
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.
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: