PostgreSQL Wire Protocol

QuestDB implements the PostgreSQL wire protocol (PGWire), allowing you to connect using standard PostgreSQL client libraries. This is a great way to get started with QuestDB, as you can use existing PostgreSQL clients and tools.

.NET

Cross-platform clients for building applications with .NET technologies.

Read more

Go

An open-source programming language supported by Google with built-in concurrency.

Read more

Java

Platform-independent clients for enterprise applications and Android development.

Read more

Node.js

Node.js® is an open-source, cross-platform JavaScript runtime environment.

Read more

Python

Python is a programming language that lets you work quickly and integrate systems more effectively.

Read more

Rust

Systems programming language focused on safety, speed, and concurrency.

Read more

PHP

PHP is a popular general-purpose scripting language that is especially suited to web development.

Read more

R

R is a programming language and free software environment for statistical computing and graphics supported by the R Foundation for Statistical Computing.

Read more

Querying vs. ingestion

The PGWire interface is recommended for querying data from QuestDB.

For data ingestion, especially high-throughput scenarios, we recommend using the InfluxDB Line Protocol (ILP) clients instead. ILP is optimized for fast data insertion and provides better performance.

That said, PGWire does support INSERT statements for lower-volume ingestion use cases.

Compatibility

Supported features

• Querying (all types except BLOB)
• Prepared statements with bind parameters
• INSERT statements with bind parameters
• UPDATE statements with bind parameters
• DDL execution
• Batch inserts
• Plain authentication

Unsupported features

• SSL
• Remote file upload (COPY from stdin)
• DELETE statements
• BLOB transfer

Connection properties

Name	Example	Description
database	qdb	Can be set to any value (e.g., qdb). Database name is ignored; QuestDB does not have database instance names.
user	admin	User name configured in pg.user or pg.readonly.user property in server.conf. Default: admin
password	quest	Password from pg.password or pg.readonly.password property in server.conf. Default: quest
options	-c statement_timeout=60000	The only supported option is statement_timeout, which specifies maximum execution time in milliseconds for SELECT or UPDATE statements.

Ingest examples

psql

Create the table:

psql -h localhost -p 8812 -U admin -d qdb \
    -c "CREATE TABLE IF NOT EXISTS t1 (name STRING, value INT);"

Insert row:

psql -h localhost -p 8812 -U admin -d qdb -c "INSERT INTO t1 VALUES('a', 42)"

Query back:

psql -h localhost -p 8812 -U admin -d qdb -c "SELECT * FROM t1"

Note that you can also run psql from Docker without installing the client locally:

docker run -it --rm --network=host -e PGPASSWORD=quest \
    postgres psql ....

Python

This example uses the psycopg3 adapter.

To install the client library, use pip:

python3 -m pip install "psycopg[binary]"

import psycopg as pg
import time

# Connect to an existing QuestDB instance

conn_str = 'user=admin password=quest host=127.0.0.1 port=8812 dbname=qdb'
with pg.connect(conn_str, autocommit=True) as connection:

    # Open a cursor to perform database operations

    with connection.cursor() as cur:

        # Execute a command: this creates a new table

        cur.execute('''
          CREATE TABLE IF NOT EXISTS test_pg (
              ts TIMESTAMP,
              name STRING,
              value INT
          ) timestamp(ts);
          ''')

        print('Table created.')

        # Insert data into the table.

        for x in range(10):

            # Converting datetime into millisecond for QuestDB

            timestamp = time.time_ns() // 1000

            cur.execute('''
                INSERT INTO test_pg
                    VALUES (%s, %s, %s);
                ''',
                (timestamp, 'python example', x))

        print('Rows inserted.')

        #Query the database and obtain data as Python objects.

        cur.execute('SELECT * FROM test_pg;')
        records = cur.fetchall()
        for row in records:
            print(row)

# the connection is now closed

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");

    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();
    }
    System.out.println("Done");
    connection.close();
  }
}

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",
  })
  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)

  let now = new Date().toISOString()
  const insertData = await client.query(
    "INSERT INTO trades VALUES($1, $2, $3, $4);",
    [now, now, "node pg example", 123],
  )
  await client.query("COMMIT")

  console.log(insertData)

  for (let rows = 0; rows < 10; rows++) {
    // Providing a 'name' field allows for prepared statements / bind variables
    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)
  }
  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)

Go

This example uses the pgx driver and toolkit for PostgreSQL in Go. More details on the use of this toolkit can be found on the GitHub repository for pgx.

package main

import (
  "context"
  "fmt"
  "log"
  "time"

  "github.com/jackc/pgx/v4"
)

var conn *pgx.Conn
var err error

func main() {
  ctx := context.Background()
  conn, _ = pgx.Connect(ctx, "postgresql://admin:quest@localhost:8812/qdb")
  defer conn.Close(ctx)

  // text-based query
  _, err := conn.Exec(ctx,
    ("CREATE TABLE IF NOT EXISTS trades (" +
     "    ts TIMESTAMP, date DATE, name STRING, value INT" +
     ") timestamp(ts);"))
  if err != nil {
    log.Fatalln(err)
  }

  // Prepared statement given the name 'ps1'
  _, err = conn.Prepare(ctx, "ps1", "INSERT INTO trades VALUES($1,$2,$3,$4)")
  if err != nil {
    log.Fatalln(err)
  }

  // Insert all rows in a single commit
  tx, err := conn.Begin(ctx)
  if err != nil {
    log.Fatalln(err)
  }

  for i := 0; i < 10; i++ {
    // Execute 'ps1' statement with a string and the loop iterator value
    _, err = conn.Exec(
      ctx,
      "ps1",
      time.Now(),
      time.Now().Round(time.Millisecond),
      "go prepared statement",
      i + 1)
    if err != nil {
      log.Fatalln(err)
    }
  }

  // Commit the transaction
  err = tx.Commit(ctx)
  if err != nil {
    log.Fatalln(err)
  }

  // Read all rows from table
  rows, err := conn.Query(ctx, "SELECT * FROM trades")
  fmt.Println("Reading from trades table:")
  for rows.Next() {
    var name string
    var value int64
    var ts time.Time
    var date time.Time
    err = rows.Scan(&ts, &date, &name, &value)
    fmt.Println(ts, date, name, value)
  }

  err = conn.Close(ctx)
}

Rust

The following example shows how to use parameterized queries and prepared statements using the rust-postgres client.

use postgres::{Client, NoTls, Error};
use chrono::{Utc};
use std::time::SystemTime;

fn main() -> Result<(), Error> {
    let mut client = Client::connect("postgresql://admin:quest@localhost:8812/qdb", NoTls)?;

    // Basic query
    client.batch_execute(
      "CREATE TABLE IF NOT EXISTS trades ( \
          ts TIMESTAMP, date DATE, name STRING, value INT \
      ) timestamp(ts);")?;

    // Parameterized query
    let name: &str = "rust example";
    let val: i32 = 123;
    let utc = Utc::now();
    let sys_time = SystemTime::now();
    client.execute(
        "INSERT INTO trades VALUES($1,$2,$3,$4)",
        &[&utc.naive_local(), &sys_time, &name, &val],
    )?;

    // Prepared statement
    let mut txn = client.transaction()?;
    let statement = txn.prepare("INSERT INTO trades VALUES ($1,$2,$3,$4)")?;
    for value in 0..10 {
        let utc = Utc::now();
        let sys_time = SystemTime::now();
        txn.execute(&statement, &[&utc.naive_local(), &sys_time, &name, &value])?;
    }
    txn.commit()?;

    println!("import finished");
    Ok(())
}

For query examples, see the Query & SQL Overview.

Important considerations

Timestamp handling

QuestDB stores all timestamps internally in UTC. However, when transmitting timestamps over the PGWire protocol, QuestDB represents them as TIMESTAMP WITHOUT TIMEZONE. This can lead to client libraries interpreting these timestamps in their local timezone by default, potentially causing confusion or incorrect data representation.

Our language-specific guides provide detailed examples on how to configure your client to correctly interpret these timestamps as UTC.

We recommend setting the timezone in your client library to UTC to ensure consistent handling of timestamps.

SQL dialect differences

While QuestDB supports the PGWire protocol for communication, its SQL dialect and feature set are not identical to PostgreSQL. QuestDB is a specialized time-series database and does not support all SQL features, functions, or data types that a standard PostgreSQL server does.

Always refer to the QuestDB SQL documentation for supported operations.

Forward-only cursors

QuestDB's cursors are forward-only, differing from PostgreSQL's support for scrollable cursors (which allow bidirectional navigation and arbitrary row access). With QuestDB, you can iterate through query results sequentially from start to finish, but you cannot move backward or jump to specific rows.

Explicit DECLARE CURSOR statements for scrollable types, or operations like fetching in reverse (e.g., FETCH BACKWARD), are not supported.

This limitation can impact client libraries that rely on scrollable cursor features. For example, Python's psycopg2 driver might encounter issues if attempting such operations. For optimal compatibility, choose drivers or configure existing ones to use forward-only cursors, such as Python's asyncpg driver.

Protocol flavors and encoding

The PostgreSQL wire protocol has different implementations and options. When your client library allows:

• Prefer the Extended Query Protocol over the Simple Query Protocol
• Choose clients that support BINARY encoding for data transfer over TEXT encoding for optimal performance and type fidelity

The specifics of how to configure this will vary by client library.

Decimal values

To insert decimal values via PGWire, you must either use the m suffix to indicate that the value is a decimal literal or cast the value to decimal:

INSERT INTO my_table (decimal_column) VALUES (123.45m);                        -- Using 'm' suffix
INSERT INTO my_table (decimal_column) VALUES (CAST($1 AS DECIMAL(18, 3)));     -- Using CAST over bind parameter

In the text format, PostgreSQL clients send decimal values as strings. Currently, QuestDB parses these strings as double values and doesn't implicitly convert them to decimal to avoid unintended precision loss. You must explicitly cast double values to decimal in your SQL queries when inserting into decimal columns.

Highly-available reads (Enterprise)

QuestDB Enterprise supports running multiple replicas to serve queries. Many client libraries allow specifying multiple hosts in the connection string. This ensures that initial connections succeed even if a node is unavailable. If the connected node fails later, the application should catch the error, reconnect to another host, and retry the read.

For background and code samples in multiple languages, see:

• Blog: Highly-available reads with QuestDB
• Examples: questdb/questdb-ha-reads