Update data

This page shows how to update existing data in QuestDB using different programming languages and tools. There are two main methods for updating data:

• Postgres wire protocol for compatibility with a range of clients
• REST API provides access to QuestDB via HTTP

Prerequisites

This page assumes that QuestDB is running and accessible. QuestDB can be run using either Docker, the Binaries or Homebrew for macOS users.

Postgres compatibility

You can query data using the Postgres endpoint that QuestDB exposes. This is accessible via port 8812 by default. More information on the Postgres wire protocol implementation with details on supported features can be found on the Postgres API reference page.

NodeJS

This example uses the pg package which allows for quickly building queries using Postgres wire protocol. Details on the use of this package can be found on the node-postgres documentation.

This example uses naive Date.now() * 1000 inserts for Timestamp types in microsecond resolution. For accurate microsecond timestamps, the process.hrtime.bigint() call can be used.

"use strict"

const { Client } = require("pg")

const start = async () => {

const client = new Client({

database: "qdb",

host: "127.0.0.1",

password: "quest",

port: 8812,

user: "admin",

options: "-c statement_timeout=300000"

})

await client.connect()

const createTable = await client.query(

"CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, date DATE, name STRING, value INT) timestamp(ts);"

)

console.log(createTable)

for (let rows = 0; rows < 10; rows++) {

// Providing a 'name' field allows for prepared statements / bind variables

let now = new Date().toISOString()

const query = {

name: "insert-values",

text: "INSERT INTO trades VALUES($1, $2, $3, $4);",

values: [now, now, "node pg prep statement", rows],

}

await client.query(query)

}

const updateData = await client.query(

"UPDATE trades SET name = 'update example', value = 123 WHERE value > 7;"

)

console.log(updateData)

await client.query("COMMIT")

const readAll = await client.query("SELECT * FROM trades")

console.log(readAll.rows)

await client.end()

}

start()

.then(() => console.log("Done"))

.catch(console.error)

Java

package com.myco;

import java.sql.*;

import java.util.Properties;

class App {

public static void main(String[] args) throws SQLException {

Properties properties = new Properties();

properties.setProperty("user", "admin");

properties.setProperty("password", "quest");

properties.setProperty("sslmode", "disable");

properties.setProperty("options", "-c statement_timeout=300000");

final Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:8812/qdb", properties);

connection.setAutoCommit(false);

final PreparedStatement statement = connection.prepareStatement("CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, date DATE, name STRING, value INT) timestamp(ts);");

statement.execute();

try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO trades VALUES (?, ?, ?, ?)")) {

preparedStatement.setTimestamp(1, new Timestamp(io.questdb.std.Os.currentTimeMicros()));

preparedStatement.setDate(2, new Date(System.currentTimeMillis()));

preparedStatement.setString(3, "abc");

preparedStatement.setInt(4, 123);

preparedStatement.execute();

}

final PreparedStatement statement = connection.prepareStatement("UPDATE trades SET value = value + 1000;");

statement.execute();

System.out.println("Done");

connection.close();

}

}

Python

This example uses the psycopg2 database adapter which does not support prepared statements (bind variables). This functionality is on the roadmap for the antecedent psychopg3 adapter.

import psycopg2 as pg

import datetime as dt

try:

connection = pg.connect(user="admin",

password="quest",

host="127.0.0.1",

port="8812",

database="qdb",

options='-c statement_timeout=300000')

cursor = connection.cursor()

# text-only query

cursor.execute("CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, date DATE, name STRING, value INT) timestamp(ts);")

# insert 10 records

for x in range(10):

now = dt.datetime.utcnow()

date = dt.datetime.now().date()

cursor.execute("""

INSERT INTO trades

VALUES (%s, %s, %s, %s);

""", (now, date, "python example", x))

# commit records

connection.commit()

# update records

cursor.execute("UPDATE trades SET value = value + 100;")

cursor.execute("SELECT * FROM trades;")

records = cursor.fetchall()

for row in records:

print(row)

finally:

if (connection):

cursor.close()

connection.close()

print("Postgres connection is closed")

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 entrypoints:

• /imp - import data
• /exec - execute an SQL statement

More details on the use of these entrypoints can be found on the REST API reference page.

/imp endpoint

The /imp endpoint does not allow for updating data.

/exec endpoint

Alternatively, the /exec endpoint can be used to create a table and the INSERT statement can be used to populate it with values:

cURL

# Create Table

curl -G \

--data-urlencode "query=CREATE TABLE IF NOT EXISTS trades(name STRING, value INT)" \

http://localhost:9000/exec

# Insert a row

curl -G \

--data-urlencode "query=INSERT INTO trades VALUES('abc', 123456)" \

http://localhost:9000/exec

# Update a row

curl -G \

--data-urlencode "query=UPDATE trades SET value = 9876 WHERE name = 'abc'" \

http://localhost:9000/exec

NodeJS

The node-fetch package can be installed using npm i node-fetch.

const fetch = require("node-fetch")

const HOST = "http://localhost:9000"

async function createTable() {

try {

const query = "CREATE TABLE IF NOT EXISTS trades (name STRING, value INT)";

const response = await fetch(`${HOST}/exec?query=${encodeURIComponent(query)}`)

const json = await response.json()

console.log(json)

} catch (error) {

console.log(error)

}

}

async function insertData() {

try {

const query = "INSERT INTO trades VALUES('abc', 123456)"

const response = await fetch(`${HOST}/exec?query=${encodeURIComponent(query)}`)

const json = await response.json()

console.log(json)

} catch (error) {

console.log(error)

}

}

async function updateData() {

try {

const query = "UPDATE trades SET value = 9876 WHERE name = 'abc'"

const response = await fetch(`${HOST}/exec?query=${encodeURIComponent(query)}`)

const json = await response.json()

console.log(json)

} catch (error) {

console.log(error)

}

}

createTable().then(insertData).then(updateData)

Python

import requests

import json

host = 'http://localhost:9000'

def run_query(sql_query):

query_params = {'query': sql_query, 'fmt' : 'json'}

try:

response = requests.get(host + '/exec', params=query_params)

json_response = json.loads(response.text)

print(json_response)

except requests.exceptions.RequestException as e:

print("Error: %s" % (e))

# create table

run_query("CREATE TABLE IF NOT EXISTS trades (name STRING, value INT)")

# insert row

run_query("INSERT INTO trades VALUES('abc', 123456)")

# update row

run_query("UPDATE trades SET value = 9876 WHERE name = 'abc'")

Web Console

By default, QuestDB has an embedded Web Console running at http://[server-address]:9000. When running locally, this is accessible at http://localhost:9000. The Web Console can be used to explore table schemas, visualizing query results as tables or graphs, and importing datasets from CSV files. For details on these components, refer to the Web Console reference page.