This page has instructions for making SQL selection queries against CockroachDB from various programming languages.
Before you begin
Make sure you have already:
- Set up a local cluster.
- Installed a Postgres client.
- Connected to the database.
- Inserted 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:
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 Make queries fast.
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.
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 Make queries fast.
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
- Make Queries Fast
- Hello World Example apps
Was this helpful?
