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:
- Install CockroachDB.
- Start a local cluster, or create a CockroachDB Cloud cluster.
- Install a Postgres client.
- Connect to the database.
- Insert data that you now want to run queries against.
Your application should use a retry loop to handle transaction 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:
- Selection queries
SELECT- Joins
- Paginate through limited results
- Understanding and Avoiding Transaction Contention
Other common tasks:
- Connect to the Database
- Insert Data
- Update Data
- Delete Data
- Run Multi-Statement Transactions
- Error Handling and Troubleshooting
- Optimize Statement Performance
- Hello World Example apps
Was this helpful?
