Query data

This page describes how to query data from QuestDB using different programming languages and tools.

For ad-hoc SQL queries, including CSV download and charting use the web console. Applications can choose between the HTTP REST API which returns JSON or use the PostgreSQL wire protocol.

Here are all your options:

  • Web Console
    • SQL SELECT statements.
    • Download query results as CSV.
    • Chart query results.
  • PostgreSQL wire protocol
    • SQL SELECT statements.
    • Use psql on the command line.
    • Interoperability with third-party tools and libraries.
  • HTTP REST API
    • SQL SELECT statements as JSON or CSV.
    • Result paging.

Web Console#

QuestDB ships with an embedded Web Console running by default on port 9000.

Screenshot of the Web Console

To query data from the web console, SQL statements can be written in the code editor and executed by clicking the Run button.

Listing tables and querying a table
SHOW TABLES;
SELECT * FROM my_table;
--Note that `SELECT * FROM` is optional
my_table;

Aside from the Code Editor, the Web Console includes a data visualization panel for viewing query results as tables or graphs and an Import tab for uploading datasets as CSV files. For more details on these components and general use of the console, see the Web Console page.

PostgreSQL wire protocol#

You can query data using the Postgres endpoint that QuestDB exposes which is accessible by default via port 8812.

import psycopg2
connection = None
cursor = None
try:
connection = psycopg2.connect(
user='admin',
password='quest',
host='127.0.0.1',
port='8812',
database='qdb')
cursor = connection.cursor()
postgreSQL_select_Query = 'SELECT x FROM long_sequence(5);'
cursor.execute(postgreSQL_select_Query)
print('Selecting rows from test table using cursor.fetchall')
mobile_records = cursor.fetchall()
print("Print each row and it's columns values")
for row in mobile_records:
print("y = ", row[0], "\n")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
if cursor:
cursor.close()
if connection:
connection.close()
print("PostgreSQL connection is closed")

HTTP REST API#

QuestDB exposes a REST API for compatibility with a wide range of libraries and tools. The REST API is accessible on port 9000 and has the following query-capable entrypoints:

EntrypointHTTP MethodDescriptionAPI Docs
/exp?query=..GETExport SQL Query as CSVReference
/exec?query=..GETRun SQL Query returning JSON result setReference

For details such as content type, query parameters and more, refer to the REST API docs.

/exp: SQL Query to CSV#

The /exp entrypoint allows querying the database with a SQL select query and obtaining the results as CSV.

For obtaining results in JSON, use /exec instead, documented next.

curl -G --data-urlencode \
"query=SELECT * FROM example_table2 LIMIT 3" \
http://localhost:9000/exp
"col1","col2","col3"
"a",10.5,true
"b",100.0,false
"c",,true

/exec: SQL Query to JSON#

The /exec entrypoint takes a SQL query and returns results as JSON.

This is similar to the /exec entry point which returns results as CSV.

Querying Data#

curl -G \
--data-urlencode "query=SELECT x FROM long_sequence(5);" \
http://localhost:9000/exec

The JSON response contains the original query, a "columns" key with the schema of the results, a "count" number of rows and a "dataset" with the results.

{
"query": "SELECT x FROM long_sequence(5);",
"columns": [
{"name": "x", "type": "LONG"}],
"dataset": [
[1],
[2],
[3],
[4],
[5]],
"count": 5
}