PostgreSQL protocol (port 8832)

Connect to KDB with PostgreSQL tools and drivers - psql, DBeaver, Python, Node, C#, Go - and what works and what does not.

• Category: Postgres protocol
• Keywords: postgres, postgresql, pg, wire protocol, port 8832, --pg-port, --pg-timestamptz, --pg-int-division, int_division, integer division, pg_compat, kdb.pg_compat, --pg-compat, PostgreSQL-compatible results, NUMERIC, RANGE UNBOUNDED PRECEDING, NULLS FIRST, collation, en_US, wall clock, psql, DBeaver, psycopg, psycopg2, pg8000, node-pg, Npgsql, pgx, Go, driver, SCRAM-SHA-256, sslmode, TLS, SSH tunnel, SQLSTATE, OID, timestamptz, $1, parameters, prepared statement, RETURNING, 25P02, 23505, 23503, 23001, P0001, ConstraintName, TableName, ERRCODE, RAISE, 55P03, 42501, 3D000, 28P01, 53100, disk full, 57014, pg_catalog, information_schema, COPY, COPY FROM STDIN, COPY TO STDOUT, CancelRequest, cancel query, streaming, big result, whole table, fetch size, setFetchSize, portal, max_rows, PortalSuspended, max_result_memory
• See also: Logins, tokens and roles over HTTP, Users and roles, Transactions, Column types, Time and time zones, Keeping kdb serve safe, kdb serve

KDB is not PostgreSQL, but kdb serve speaks PostgreSQL's wire protocol: the messages a Postgres driver and the database send each other. So a normal Postgres driver or tool can connect to KDB, send SQL and read the rows.

• You write KDB SQL (the SQL of this help), not PostgreSQL's SQL.
• The port is 8832 (QuestDB uses 8812). It starts together with the HTTP port.
• Without TLS the data is not encrypted: keep the port on 127.0.0.1, or use an SSH tunnel. With kdb serve --tls-cert ... clients connect with sslmode=require.

Tutorial: chapter 14, PostgreSQL protocol. The Postgres port cannot be run by this help's example runner, so every example here is plain text; which ones were run on a real server is said under each.

Start the Postgres port

Syntax:

kdb serve [--pg-port 8832] [--pg-timestamptz] [--bind 127.0.0.1] [--token <secret>]

When the port is on, kdb serve prints a postgres line. Real output from chapter 14:

KDB 0.2.0-dev
  console   http://127.0.0.1:9900
  postgres  127.0.0.1:8832 (PostgreSQL protocol, no TLS; password = the token)
  ...

• If port 8832 is already used by another program, kdb serve stops with error: Postgres protocol port: ... (use --pg-port N, or --pg-port 0 to turn it off).
• At most 500 logged-in connections at the same time, for all databases together (kdb serve --max-connections N, KDB_MAX_CONNECTIONS or max_connections in kdb.conf; 0 = no limit). One more gets SQLSTATE 53300 (sorry, too many clients already). A closed connection's place is free again at once, so a pool that closes and reopens its connections gets them back.
• One message (for example one big INSERT) can be up to 256 MB.

Connection settings

A connection string (URL form and key=value form):

postgres://app:my-long-secret@127.0.0.1:8832/market?sslmode=disable
host=127.0.0.1 port=8832 dbname=market user=app password=my-long-secret sslmode=disable

• An unknown database: the connection is refused with SQLSTATE 3D000 and database 'x' does not exist.
• The server's time zone is UTC, the encoding UTF8, DateStyle ISO, MDY. server_version is 14.0 (KDB <version>), so drivers treat KDB like PostgreSQL 14.

Passwords and logins

• The login uses SCRAM-SHA-256: the password itself never crosses the network, even without TLS. (Without TLS the data does cross the network unencrypted.)
• A wrong password or an unknown user: 28P01 password authentication failed for user "..." (the same for both).
• Connecting as a KDB user needs at least read on the database (42501 otherwise). A statement the role does not allow gets 42501, and nothing of it runs.
• Failed logins count together with HTTP logins: after 5 failures for one user from one address, the next try must wait (1 s, 2 s, 4 s, ... up to 15 minutes); meanwhile the answer is 28000 too many failed logins; try again in N s.
• The --token login is the exception: a name that must wait is not refused before the password. The right token still gets in (and ends the waiting); only a wrong token gets 28000 too many failed logins; try again in N s. Behind Docker every client has the same address and most send the same name (postgres), so one client with an old token cannot lock the others out. The token itself is therefore not slowed down by failed logins: make it long and random (for example openssl rand -hex 32).
• More: users and roles, logins over HTTP.

psql

psql is PostgreSQL's command line tool. Not run while writing this help (psql was not installed on the test PC).

$env:PGPASSWORD = "my-long-secret"
psql "host=127.0.0.1 port=8832 dbname=market user=app sslmode=disable"

market=> SELECT symbol, close FROM ochl_day WHERE symbol = 'CITY' LIMIT 2;
market=> BEGIN;
market=*> INSERT INTO brokers VALUES (99, 'Test Broker');
market=*> ROLLBACK;
market=> SHOW TABLES;

• Plain SQL works.
• The backslash commands read the Postgres catalog. KDB answers them from a read-only catalog (catalog). Tested with the exact queries psql 14, 15 and 16 send:

• Sizes in \dt+ are real: the table's files on disk, plus its new rows still in the log. Indexes show 0 bytes: KDB keeps them in memory only. SHOW TABLES;, SHOW COLUMNS FROM ochl_day; and SHOW DATABASES; always work.
• \copy works: it sends COPY ... FROM STDIN or TO STDOUT (COPY).
• Ctrl+C sends a cancel request, which stops the running statement.

DBeaver

DBeaver is a free database tool with a SQL editor. Not run while writing this help.

1. New Database Connection, then PostgreSQL.
2. Host 127.0.0.1, port 8832, database market, user app, password = the token (or the KDB user's password).
3. Driver properties: set sslmode to disable.
4. Connection settings, Initialization: turn off "Show all databases".
5. Open an SQL editor and run KDB SQL.

The SQL editor and the result grid work. The Database Navigator (the tree of schemas and tables) reads the Postgres catalog; KDB has a read-only one (catalog), but not everything DBeaver asks for. When the tree stays empty, use SHOW TABLES in the editor.

Python (psycopg, pg8000)

With psycopg (version 3). Not run on the test PC (no psycopg build for its Python version yet):

import psycopg

with psycopg.connect("host=127.0.0.1 port=8832 dbname=market user=app password=my-long-secret sslmode=disable") as con:
    # %s are parameters: psycopg sends the values apart from the SQL.
    for row in con.execute("SELECT symbol, close FROM ochl_day WHERE close > %s ORDER BY close DESC LIMIT 5", (500,)):
        print(row)

    with con.transaction():   # BEGIN ... COMMIT (ROLLBACK on an exception)
        con.execute("INSERT INTO brokers VALUES (%s, %s)", (99, "Test Broker"))

With pg8000 (a Python-only driver). This was tested: tests/pg_python/check_pg8000.py runs these steps against a real server and prints ALL OK:

import pg8000.native

con = pg8000.native.Connection("app", host="127.0.0.1", port=8832, database="market", password="my-long-secret")
print(con.run("SELECT version()")[0][0])
rows = con.run("SELECT time, close FROM ochl_day WHERE symbol = :s ORDER BY time", s="CITY")
for t, close in rows:
    print(t, close)
con.close()

• TIMESTAMP values come back as datetime without a zone; they are UTC. With --pg-timestamptz they come back with the UTC zone.
• DECIMAL comes back as decimal.Decimal.

Node (pg)

The pg package. Tested (chapter 14):

const { Client } = require("pg");

const client = new Client({ host: "127.0.0.1", port: 8832, user: "app", password: "my-long-secret", database: "market" });
await client.connect();
const res = await client.query("SELECT symbol, close FROM ochl_day WHERE symbol = $1 ORDER BY time DESC LIMIT 3", ["CITY"]);
console.log(res.rows);
await client.end();

• Times: pg reads a Postgres timestamp (without zone) as your computer's local time. On a PC in Nepal, 05:15 UTC comes back as 23:30 of the day before. Start the server with --pg-timestamptz and the times come back right.
• Numbers: pg gives LONG (int8) and DECIMAL (numeric) values as strings ('182390', '512.40'), as with PostgreSQL.

C# (Npgsql)

Not tested with KDB. Npgsql normally reads the Postgres type catalog when it connects, and KDB's catalog is only a small one: turn that off with Server Compatibility Mode=NoTypeLoading.

using Npgsql;

var cs = "Host=127.0.0.1;Port=8832;Username=app;Password=my-long-secret;Database=market;" +
         "SSL Mode=Disable;Server Compatibility Mode=NoTypeLoading";
await using var dataSource = NpgsqlDataSource.Create(cs);

await using (var cmd = dataSource.CreateCommand("SELECT time, close FROM ochl_day WHERE symbol = $1 ORDER BY time"))
{
    cmd.Parameters.Add(new NpgsqlParameter { Value = "CITY" });
    await using var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
        Console.WriteLine($"{reader.GetDateTime(0):yyyy-MM-dd} {reader.GetDouble(1)}");
}

await using var conn = await dataSource.OpenConnectionAsync();
await using var tx = await conn.BeginTransactionAsync();
await using (var insert = new NpgsqlCommand("INSERT INTO brokers VALUES ($1, $2)", conn, tx))
{
    insert.Parameters.Add(new NpgsqlParameter { Value = 99 });
    insert.Parameters.Add(new NpgsqlParameter { Value = "Test Broker" });
    await insert.ExecuteNonQueryAsync();
}
await tx.CommitAsync();

For times, pick one setting: with --pg-timestamptz, send and read DateTime values with Kind = Utc.

Go (pgx)

This is how the NChartPro backend connects. Tested: tests/pg_go/main.go checks all of this against a real server.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, "postgres://app:my-long-secret@127.0.0.1:8832/market?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	// $1, $2, ... are parameters: the values never become part of the SQL text.
	_, err = pool.Exec(ctx, "INSERT INTO securities (symbol, name, sector) VALUES ($1, $2, $3)", "COAST", "Coastal Bank", "Banking")
	if err != nil {
		panic(err)
	}

	rows, err := pool.Query(ctx, "SELECT time, close, volume FROM ochl_day WHERE symbol = $1 AND time >= $2 ORDER BY time",
		"CITY", time.Date(2026, 9, 1, 0, 0, 0, 0, time.UTC))
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var t time.Time
		var close, volume float64
		if err := rows.Scan(&t, &close, &volume); err != nil {
			panic(err)
		}
		fmt.Println(t.Format("2006-01-02"), close, volume)
	}
}

Real output of tests/pg_go (from chapter 14):

ok   connect and ping (SCRAM-SHA-256 login)
ok   version() = "PostgreSQL 14.0 (KDB 0.2.0-dev), compatible server", database "default"
ok   create table
ok   insert with parameters (int, text, float, numeric, bool, time, NULL)
ok   select with a parameter: {ID:1 Email:asha@example.com Balance:10.5 Active:true Created:2026-09-11 05:15:00.123 +0000 UTC} price 512.40
ok   pgx.CollectRows into structs: 2 rows
ok   LIKE, IN and LIMIT parameters
ok   transactions: rollback and commit
ok   unique violation: SQLSTATE 23505 (a row with id='1' already exists in 'users'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it)
ok   unknown table 42P01; error inside a transaction, then rollback
ok   batch of 4 statements
ok   query modes: simple protocol, exec, describe exec, cache describe
ALL OK

Everything pgx does works: the pool, QueryRow, pgx.CollectRows with RowToStructByName, Begin/Commit/Rollback, batches (SendBatch) and all query modes. Error codes: errors.As(err, &pgErr) and pgErr.Code.

Parameters ($1, $2)

Syntax:

SELECT ... WHERE symbol = $1 AND time >= $2
INSERT INTO t (a, b) VALUES ($1, $2)

A parameter is a place for a value that the driver sends apart from the SQL text. Always use parameters for values that come from users: the value can never be read as SQL (no SQL injection).

• The values go into KDB's syntax tree as values, never into the SQL text.
• A parameter gets its type from where it is used. In WHERE volume > $1, $1 is LONG because volume is LONG. In INSERT INTO t (a, b) VALUES ($1, $2) each one gets its column's type. In LIKE $1 it is text, in LIMIT $1 a whole number.
• A parameter KDB cannot place (SELECT $1) has no type; the driver then sends it as text.
• Parameters work in WHERE, VALUES, SET, LIMIT, OFFSET, function arguments and CASE. They do not work where KDB needs a fixed value while reading the SQL: SAMPLE BY, FILL(...), table and column names.
• Values can come in text or binary form; the driver chooses.
• Result column types: before a query runs, KDB works out the type of each result column. If it cannot (a saved function, say), the column is text. When a driver then says cannot scan text into *float64, add a cast: SELECT my_func(close)::DOUBLE AS x ....

Types

Parameters sent as varchar, char, name or uuid are taken as text; jsonb as JSON; oid as LONG.

A column declared INTERVAL keeps its length in milliseconds (a LONG). The Postgres port sends it as a real interval, in text (00:15:03.548) and in binary, and a parameter for it is typed interval. Over the HTTP API the same column is a number of milliseconds (903548), typed LONG.

A parameter next to such a column is typed interval too, and KDB turns the interval you send into milliseconds before it runs. So a Go time.Duration works everywhere:

To send milliseconds as a number instead, cast the parameter: WHERE d > $1::bigint. A client that types the parameter int8 itself also sends milliseconds. Text that is not an interval is refused with 22007.

Times and --pg-timestamptz

KDB keeps a TIMESTAMP as one exact moment in UTC, in milliseconds (time and time zones).

By default it is sent as Postgres timestamp (without zone), like QuestDB does. With this type, drivers drop the time zone of a value you send:

• pgx: a Go time.Time in Nepal time (11:00 +05:45) is stored as 11:00 UTC. Always send UTC times: t.UTC().
• node-pg: times you read come back in the computer's local time zone.

Start the server with --pg-timestamptz to send TIMESTAMP as timestamptz. Then every driver keeps the exact moment, in any time zone. Choose one setting for a server and keep it.

A column declared TIMESTAMPTZ is always sent as timestamptz, with or without the option. So are x::timestamptz, now() and CURRENT_TIMESTAMP, and a parameter next to them is typed timestamptz. pgx then sends a Go time as the moment it is: 11:00 +05:45 is stored as 05:15 UTC.

The session's time zone comes from the connection's TimeZone parameter (pgx ?timezone=Asia/Kathmandu, libpq options=-c TimeZone=...), else from SET TIME ZONE, else the server's zone. A timestamptz shows that clock as text (2026-09-25 11:00:00+05:45), and timestamptz text without an offset is read on that clock. RESET TIME ZONE goes back to the connection's zone.

• Times have milliseconds. Microseconds from a driver are cut off.
• Time text a driver sends may have a zone: Z, +05, +0545, +05:45. Text without a zone is UTC.

Transactions

Syntax:

BEGIN [ISOLATION LEVEL ...] | START TRANSACTION
COMMIT | END
ROLLBACK | ABORT

BEGIN, COMMIT and ROLLBACK work across messages, one transaction per connection. Every KDB transaction is serializable; an isolation level you name is accepted and ignored. More: transactions.

• When a statement fails inside a transaction, the transaction is aborted, like in PostgreSQL. The failed statement itself gets its own code (23505 for a duplicate key, 23503 for a foreign key, ...). Every next statement gets 25P02 (current transaction is aborted, commands ignored until end of transaction block) until you send ROLLBACK. A COMMIT then also rolls back (its tag is ROLLBACK).
• One writer per table. While one connection has an open transaction, a change of one of its tables from another connection waits up to the lock timeout (5 seconds unless set; SET lock_timeout = '20s' for this connection, see lock timeout), then fails with 55P03 (table 'x' is busy). Changes of other tables go on. Two transactions that would wait for each other: one fails at once with 40P01 (deadlock). Reads do not wait. Keep transactions short.
• An INSERT that gives up on a busy table gets 55P03, even when its key looks taken: the writer holding the table may be freeing that key. Send it again; the retry gets 23505 if the key is really in use.
• A transaction with no statement for 30 seconds is rolled back. The connection then gets 25P02 until it sends ROLLBACK. Change the time for the connection with SET idle_in_transaction_session_timeout = '5min' (0 = never), or for the server with --idle-in-transaction-timeout (idle transactions).
• When a connection closes, its open transaction is rolled back.
• A simple query with several statements (INSERT ...; INSERT ...; in one message) is not one transaction: statements before an error stay done. Use BEGIN ... COMMIT.
• INSERT, UPDATE and DELETE with RETURNING give rows, like a SELECT (in Go: pool.QueryRow(ctx, "INSERT ... RETURNING id", ...).Scan(&id)).
• USE other_db switches the connection's database; an open transaction of the old database is closed.

Error codes (SQLSTATE)

Errors carry a SQLSTATE: a five-character code that drivers show (pgErr.Code in Go, e.sqlstate in psycopg).

Which constraint. A 23505 or 23503 error also says which table and which constraint it was about. The constraint has the name PostgreSQL would give it, or the name you gave it with CONSTRAINT name:

When a row is still referred to (DELETE of a parent row), the table is the one that refers to it, as in PostgreSQL.

Your own code. A trigger function or a DO block can choose the code with RAISE EXCEPTION '...' USING ERRCODE = '23001'. A condition name works too (ERRCODE = 'restrict_violation'), and so do RAISE SQLSTATE '23001' USING MESSAGE = '...' and RAISE unique_violation. The client gets that code, so it can tell your rule from a real duplicate key. A name KDB does not know gives P0001.

Commands KDB answers itself

Drivers and tools send a few PostgreSQL commands that KDB SQL does not have. KDB answers them itself:

SHOW TABLES, SHOW COLUMNS FROM t, SHOW DATABASES and the other KDB SHOW statements still work as KDB statements.

The result cache is on

Syntax:

SET result_cache = off | on      this connection
SHOW result_cache

A SELECT over this port uses the server's result cache, like /exec: the same query again gives the kept result while no table it read has changed. It is on for every new connection. For a benchmark that must time the engine, turn it off on each connection first (in a pgx pool: in AfterConnect). Without that, repeated queries with the same values measure the cache, not the query. SHOW result_cache says which one a connection has; RESET result_cache turns it on again.

A comment does not make a query a different one: /* run 2 */ SELECT ... is answered from the kept result of SELECT ... (the key is the statement as KDB reads it, without comments or extra spaces). To time a second run, turn the cache off; a changed comment is not enough.

Integer division

Syntax:

SET int_division = integer | float | default      this connection
SHOW int_division
postgres://app:secret@127.0.0.1:8832/market?sslmode=disable&int_division=integer

PostgreSQL gives 7 / 2 = 3; KDB gives 3.5 unless the connection asks for integer. An app that reads sum(ms) / 1000 into a Go int64 needs integer: 3.5 cannot be scanned into it.

• With integer, LONG / LONG is a LONG cut toward zero (-7 / 2 is -3), and a prepared statement describes the column as int8. A DOUBLE or DECIMAL side still gives a DOUBLE.
• RESET int_division and SET int_division = default go back to the start value: the server's --pg-int-division, or the connection string's.
• The rules are in integer division.

PostgreSQL-compatible results (kdb.pg_compat)

Waiting for the owner's decision. This setting is off everywhere by default, and KDB's own answers do not change. It exists so an app moving from PostgreSQL can be tried with PostgreSQL's answers; whether it becomes the default is not decided.

Syntax:

SET kdb.pg_compat = on | off | default            this session (also SET pg_compat = ...)
SHOW kdb.pg_compat
RESET kdb.pg_compat
CREATE DATABASE <name> WITH (pg_compat = on)
ALTER DATABASE <name> SET pg_compat = on | off | default
ALTER DATABASE <name> WITH (pg_compat = on | off)
kdb serve --pg-compat on | off                    databases without their own setting (default off)
postgres://app:secret@127.0.0.1:8832/market?sslmode=disable&options=-c%20kdb.pg_compat%3Don

With pg_compat on, a statement gives PostgreSQL 14's answer where KDB's own differs:

• Where it comes from. The session's SET kdb.pg_compat wins; then the connection string (kdb.pg_compat=on as a startup parameter, or options=-c kdb.pg_compat=on); then the database's own setting; then kdb serve --pg-compat. default and RESET go back to the one before. It works over HTTP too (SET kdb.pg_compat = on; SELECT ...).
• Text order. The locale order is "pg_en_US", which KDB has built in (no ICU, no glibc): it follows PostgreSQL on Linux with en_US.UTF-8. Letters and digits first, then accents, then case (apple before Apple), and only then spaces and punctuation, so '10.15% Prime' sorts before '10 % NMB'. Among texts with the same letters, a mark that comes after fewer letters sorts first, and no mark at all sorts last ('GBILD84/85' before 'GBILD8485', as in PostgreSQL and in byte order). It follows glibc for Latin, Greek and Cyrillic text, not for every script. = and <> still compare the exact text. COLLATE "C" keeps byte order, and COLLATE "pg_en_US" gives this order in any session. COLLATE "en_US" stays KDB's own dictionary order.
• Timestamps. A plain TIMESTAMP value goes out as the wall clock of the session's time zone (SET TIME ZONE, the timezone connection parameter, else the server's), and a timestamp parameter (text without an offset, or binary) is read as a wall clock time there. So a row written with DEFAULT now() and one written with a Go time.Now() hold the same time, and created_at::date = CURRENT_DATE is right for both. KDB still stores the UTC moment: the same row read with pg_compat off, or over HTTP, shows UTC. A time written as text inside the SQL (VALUES ('2026-09-25 11:00')) is still read as UTC: send times as parameters.
• x::numeric in a result. Its type comes from the values, as in PostgreSQL, where a bare numeric has no fixed places: CREATE TABLE ... AS SELECT x::numeric makes a NUMERIC(38, s) column with the most places it met (1.25 and 2.5 stay 1.25 and 2.50, never rounded to whole numbers). An unnamed column is called CAST(x AS NUMERIC) with the setting on and CAST(x AS DOUBLE), as always, with it off.
• ORDER BY an alias or a position. The rows are sorted by the value they show, worked out once: SELECT nextval('s')::text AS id FROM t ORDER BY id steps the sequence once per row, as in PostgreSQL.
• Speed. Nothing changes with the setting off. With it on, ORDER BY a text column and a text < / > are worked out row by row (an index cannot give the locale order); numbers and times keep every fast path.
• Not changed. The default isolation level. min() / max() of text and GROUP BY still use byte order. CREATE TABLE ... NUMERIC without a precision is still a DOUBLE column: write NUMERIC(p, s). 'NaN'::numeric is an error (KDB's NUMERIC has no NaN). CHECK, DEFAULT, computed columns, index expressions and materialized views always use KDB's own rules, whatever the session says.

Big answers stream

A SELECT with a big answer sends its rows while it reads them, so a whole table can be read through the Postgres port: SELECT * FROM trades over 34 million rows used to stop at --max-result-memory (54000, about 6609 MB; the limit is 1024 MB).

• Which queries: a plain read with no ORDER BY, GROUP BY, DISTINCT or OFFSET: plain columns of one table (the fastest way: the rows are written straight from the stored columns and sent from a thread of their own, about 6 million rows a second on 2 CPUs), and also expressions, joins and a WHERE of any kind. A LIMIT works.
• Small answers (up to 64 MB of rows, or half of --max-result-memory when that is smaller) are made and sent as before, and may come from the result cache. Only a bigger answer streams, and it is never cached.
• Every way of asking: a simple query (psql), an Execute with no row limit (pgx, Npgsql), an Execute with a row limit (JDBC setFetchSize, a named portal), and a Describe of the portal before its Execute (libpq, psycopg, pgx, JDBC). Drivers send that Describe, the Execute and the Sync in one packet: a plain SELECT of a table's columns is then described from the table, and any other query runs as its Execute would, the rows streamed behind the RowDescription. Rows that are not asked for yet - past a row limit, or all of them after a Describe sent on its own - wait in the database folder's _tmp/ (in memory up to 8 MB, then in a file, encrypted when the database is), and are removed when the portal ends.
• Several statements in one query string (SELECT 1; SELECT * FROM trades) answer in order, so they do not stream.
• An error in the middle (the client went away, the statement timeout) ends the answer after the rows sent so far, as in PostgreSQL.
• A column whose type comes from its values (CASE ... THEN x END) needs a value that is not NULL among the first rows; when there is none, the answer is kept as before.

COPY

Syntax:

COPY <table> [(<column>, ...)] FROM STDIN [WITH (FORMAT text|csv|binary, HEADER, DELIMITER 'x', NULL 'x', QUOTE 'x', ESCAPE 'x')]
COPY <table> | (<SELECT ...>) TO STDOUT [WITH (...)]

COPY moves many rows in one go: pgx CopyFrom, psycopg cursor.copy(), psql \copy.

• FROM STDIN writes all rows as one transaction. A bad row fails the whole COPY, and nothing is written.
• It needs the same rights as an INSERT into those columns.
• KDB never reads or writes a file on the server: only FROM STDIN and TO STDOUT. psql's \copy uses them, so it works with a file on your PC.
• The old spelling psql writes (CSV HEADER, DELIMITER ',', BINARY) works too.
• A very big COPY gets 54000 (too much data for one COPY): send it in several.

The catalog

Tools and ORMs read the schema from PostgreSQL's catalog. KDB works these out from its own tables when they are read (nothing is stored), all in the schema public:

They only read. pg_attribute also lists the columns of every view and index.

On the PostgreSQL port, the catalog functions tools call work too: pg_get_indexdef, pg_get_constraintdef, pg_get_viewdef, pg_get_expr, format_type, pg_table_is_visible, and casts such as 'orders'::regclass. On every port: to_regclass, pg_relation_size, pg_total_relation_size, pg_database_size and pg_size_pretty (sizes), and QuestDB's tables() and table_columns() (SHOW TABLES).

What does not work

• Encryption without TLS turned on. Use 127.0.0.1 or an SSH tunnel then: ssh -N -L 8832:127.0.0.1:8832 user@your-vps, and connect to 127.0.0.1:8832 on your PC.
• COPY with a file on the server (COPY t FROM '/path'): use FROM STDIN (COPY).
• The parts of the Postgres catalog that are not in the list above. An ORM that reads more of it (Entity Framework, Prisma introspection) may fail.
• PostgreSQL SQL that KDB does not have (for example ::regclass). ON CONFLICT, WITH, correlated subqueries, arrays (= ANY($1) with Go slices) and advisory locks work (tutorial chapter 23).
• The old function-call messages.
• CREATE ROLE: KDB users and rights are per database (users and roles). SET ROLE name takes the rights of a KDB user (SET ROLE).
• A prepared statement whose tables changed shape (a column added) gets 0A000 cached plan must not change result type: prepare it again (pgx does this by itself).