23. PostgreSQL queries and data changes

• Category: Tutorial

What you will learn

Chapter 22 is about the app's tables. This chapter is about the app's queries and data changes: the SQL a PostgreSQL app sends every day.

• Part A. Upserts: INSERT ... ON CONFLICT, RETURNING, xmax.
• Part B. INSERT ... SELECT, UPDATE ... FROM, DELETE ... USING, subqueries in data changes.
• Part C. WITH queries: plain, RECURSIVE, and WITH x AS (UPDATE ...).
• Part D. Correlated subqueries, EXISTS, LATERAL, and why they stay fast.
• Part E. More query forms: FILTER, DISTINCT ON, UNION / INTERSECT / EXCEPT, GROUP BY 1.
• Part F. Casts, times and intervals.
• Part G. Arrays and JSON.
• Part H. Advisory locks.
• Part I. QuestDB apps: ALTER DATABASE ... SET dialect, /imp, to_str.
• What is different from PostgreSQL, and speed.

The output under each example is real (kdb sql). Times print in KDB's style (2026-09-10T05:20:00.000Z); over the PostgreSQL protocol they have PostgreSQL's style.

The short version

When all 2143 statements of the app run on KDB, 1971 work over HTTP (92.0%) and 1952 over the PostgreSQL protocol (91.1%) (checked on 2026-09-23). Before this work (and the schema work of chapter 22) it was 950 and 930. Most of the rest come from the test data or from table definitions the test schema left out (see the end).

Part A: Upserts

ON CONFLICT says what to do when a new row has the key of a row that is there.

CREATE TABLE page_hits (page TEXT PRIMARY KEY, hits BIGINT NOT NULL, last_at TIMESTAMP);
INSERT INTO page_hits VALUES ('/chart', 5, '2026-09-10T05:00:00Z');

INSERT INTO page_hits (page, hits, last_at)
VALUES ('/chart', 1, '2026-09-11T05:00:00Z'), ('/news', 1, '2026-09-11T05:00:00Z')
ON CONFLICT (page) DO UPDATE SET hits = page_hits.hits + EXCLUDED.hits, last_at = EXCLUDED.last_at
RETURNING page, hits, (xmax = 0) AS inserted;

page   | hits | inserted
-------+------+---------
/chart |    6 | false
/news  |    1 | true
1 row(s) inserted, 1 updated

• EXCLUDED.column is the value of the new row. page_hits.column is the value that is there now.
• Write the table name (or the alias of INSERT INTO t AS a) before a column. A plain name is ambiguous in PostgreSQL.
• RETURNING gives the rows that were inserted or updated. xmax is 0 for an inserted row and 1 for an updated one.
• DO UPDATE ... WHERE cond: rows that do not match stay as they are and are not returned.
• DO NOTHING skips the row. Without a key list it means "any unique key".

INSERT INTO page_hits (page, hits) VALUES ('/news', 100) ON CONFLICT (page) DO NOTHING RETURNING page;

(0 rows)
0 row(s) inserted, 1 skipped (already there)

Which keys. The key list must be the primary key, a DEDUP KEY, a UNIQUE column or constraint, or a UNIQUE INDEX with exactly those columns (a partial index needs ON CONFLICT (cols) WHERE ...). ON CONFLICT ON CONSTRAINT name names it (table_pkey for the primary key). Another list is an error, as in PostgreSQL: "there is no unique key on (...)".

Rules from PostgreSQL.

• A key with a NULL never clashes.
• Two new rows with the same key in one DO UPDATE statement are an error ("cannot change the same row twice"). With DO NOTHING the first one wins.
• SERIAL numbers are used also for rows that end as updates (so ids can have gaps).

Speed. DO NOTHING, and DO UPDATE SET of every other column from EXCLUDED, on the table's own key, run as INSERT OR IGNORE / INSERT OR REPLACE: big batches go in without looking rows up one by one. Other forms look each row up by its key (or index).

Part B: Copying and joined changes

INSERT ... SELECT copies the rows of a query. DEFAULT VALUES writes one row of defaults.

INSERT INTO ledger (user_id, amount, balance_after) SELECT user_id, 25, balance FROM wallets WHERE balance > 0;
INSERT INTO logs DEFAULT VALUES;

UPDATE ... FROM changes rows with values from other tables. DELETE ... USING deletes rows that have a match.

UPDATE wallets w SET balance = v.balance
FROM (VALUES (1, 10), (2, 20), (9, 90)) AS v(user_id, balance)
WHERE w.user_id = v.user_id
RETURNING w.user_id, w.balance;

user_id | balance
--------+--------
      1 |      10
      2 |      20
2 row(s) updated

DELETE FROM ledger l USING wallets w WHERE l.user_id = w.user_id AND w.balance < 15 RETURNING l.id;

• A row of the table changes once, also when it matches several rows of FROM (as in PostgreSQL, one of them is used).
• The join is a hash join: WHERE w.user_id = v.user_id does not compare every pair.
• Subqueries work in SET, WHERE and VALUES, also ones that use the row being changed:

UPDATE tickets t SET vote_count = (SELECT count(*) FROM votes v WHERE v.ticket_id = t.ticket_id);
DELETE FROM tickets WHERE NOT EXISTS (SELECT 1 FROM votes WHERE votes.ticket_id = tickets.ticket_id);
UPDATE ai_models SET is_default = TRUE WHERE (provider, model) = (SELECT provider, model FROM ai_models ORDER BY rank LIMIT 1);

Part C: WITH queries

A WITH query names a result for the statement. KDB runs it the first time the statement reads it, once, and keeps its rows for the statement.

WITH RECURSIVE runs its second part again on the rows found last, until no new rows come. UNION (without ALL) stops at rows it has seen, so a loop in the data ends.

WITH RECURSIVE upline(user_id, referred_by, lvl) AS (
  SELECT user_id, referred_by, 0 FROM referrals WHERE user_id = 4
  UNION ALL
  SELECT r.user_id, r.referred_by, u.lvl + 1 FROM referrals r JOIN upline u ON r.user_id = u.referred_by WHERE u.lvl < 20
)
SELECT user_id, lvl FROM upline ORDER BY lvl;

user_id | lvl
--------+----
      4 |   0
      3 |   1
      2 |   2
      1 |   3

A recursive query that never stops is stopped after 100,000 rounds or 10 million rows, with an error.

WITH queries that change data. The app's wallet top-up changes a row, writes a ledger row from the changed row, and returns it, in one statement:

WITH upd AS (UPDATE wallets SET balance = balance + 25 WHERE user_id = 1 RETURNING user_id, balance),
     led AS (INSERT INTO ledger (user_id, amount, balance_after) SELECT user_id, 25, balance FROM upd)
SELECT user_id, balance FROM upd;

user_id | balance
--------+--------
      1 |     125

• The data changes run first, in the order written. Then the main query runs.
• The statement is one transaction: when a part fails, nothing is written.
• Such a SELECT locks the tables it changes, as any writer does (chapter 15), and is never kept in the result cache.

Part D: Correlated subqueries and LATERAL

A correlated subquery uses a column of the outer row.

SELECT t.ticket_id, t.title,
       (SELECT count(*) FROM votes v WHERE v.ticket_id = t.ticket_id) AS votes,
       EXISTS (SELECT 1 FROM votes v WHERE v.ticket_id = t.ticket_id AND v.user_id = 8) AS i_voted
FROM tickets t ORDER BY t.ticket_id;

ticket_id | title           | votes | i_voted
----------+-----------------+-------+--------
        1 | Dark mode       |     2 | true
        2 | Faster charts   |     1 | false
        3 | Nepali calendar |     0 | false

How KDB keeps this fast. It does not run the subquery once per outer row.

• When the subquery joins the outer row with = (v.ticket_id = t.ticket_id), KDB runs it once for all keys (grouped by ticket_id), puts the answers in a hash table, and every outer row looks its answer up. The work is one pass over votes plus one lookup per ticket.
• count(*) of a key without rows is 0, and sum is NULL, as in PostgreSQL.
• EXISTS, NOT EXISTS, IN (SELECT ...) and NOT IN (SELECT ...) work the same way (a set of keys).
• Other shapes (v.amount > t.limit) run once per different outer value, and the answers are kept for the statement.
• A subquery that uses no outer column runs once, before the query.
• A value subquery that finds two rows is an error, as in PostgreSQL. Add LIMIT 1 or an aggregate.

LATERAL lets a FROM subquery use the tables before it:

SELECT t.title, last_vote.user_id
FROM tickets t
LEFT JOIN LATERAL (SELECT user_id FROM votes v WHERE v.ticket_id = t.ticket_id ORDER BY user_id DESC LIMIT 1) last_vote ON TRUE
ORDER BY t.ticket_id;

title           | user_id
----------------+--------
Dark mode       |       8
Faster charts   |       7
Nepali calendar |    NULL

A LATERAL subquery with an aggregate becomes a hash join; one with ORDER BY ... LIMIT runs per outer row (answers are kept for rows with the same key).

Part E: More query forms

SELECT symbol,
       count(*) FILTER (WHERE qty >= 10) AS big_trades,
       string_agg(price::text, ' -> ' ORDER BY ts) AS path
FROM trades GROUP BY 1 ORDER BY 1;

symbol | big_trades | path
-------+------------+-----------
CITY   |          2 | 510 -> 512
COAST  |          0 | 900 -> 905

SELECT DISTINCT ON (symbol) symbol, ts, price FROM trades ORDER BY symbol, ts DESC;  -- the newest trade of each symbol

symbol | ts                       | price
-------+--------------------------+------
CITY   | 2026-09-10T05:20:00.000Z |   512
COAST  | 2026-09-10T05:30:00.000Z |   905

Part F: Casts, times and intervals

SELECT '12.5'::numeric(6,1) AS n, 2.5::int AS rounded, E'tab\there' AS e,
       (DATE '2026-01-31' + INTERVAL '1 month')::date AS next_month;

n    | rounded | e        | next_month
-----+---------+----------+-----------
12.5 |       3 | tab	here | 2026-02-28

• Every PostgreSQL type name works after :: and in CAST: int4, float8, numeric(10,2), text, varchar(20), timestamptz, interval, text[], bigint[], ...
• A cast to a whole number or to numeric(p,s) rounds half away from zero. Other places (a value for an INT column, an argument of a saved function) still refuse 2.5.
• x::text of a time gives PostgreSQL's text: 2026-09-10 12:00:00 (with +00 for a timestamptz).
• A text constant next to a number is read as a number: '1.50' + 1 is 2.50 (a numeric parameter sent as text works too). Text from a column or a function is not: doc ->> 'n' + 1 is an error; write (doc ->> 'n')::int + 1.
• md5(text) and initcap(text) work as in PostgreSQL.
• CURRENT_TIMESTAMP, CURRENT_DATE, LOCALTIMESTAMP, now() are the time the statement started. KDB times are UTC.
• DATE - DATE is a number of days. A month added to the 31st ends on the last day of the next month.

Intervals keep months, days and time apart, as in PostgreSQL:

SELECT INTERVAL '1 day' - INTERVAL '1 hour' AS i, EXTRACT(EPOCH FROM INTERVAL '1 month') AS month_seconds,
       make_interval(days => 3, hours => 2) AS m;

i               | month_seconds | m
----------------+---------------+----------------
1 day -01:00:00 |       2592000 | 3 days 02:00:00

justify_hours, justify_days and justify_interval move 24 hours into a day and 30 days into a month. For comparing, a month counts as 30 days.

AT TIME ZONE follows PostgreSQL:

• timestamp AT TIME ZONE z reads a clock time of zone z and gives the instant (a timestamptz).
• timestamptz AT TIME ZONE z shows an instant as the clock time of zone z (a timestamp).

SELECT (TIMESTAMP '2026-09-10 12:00:00' AT TIME ZONE 'Asia/Kathmandu')::text AS utc_instant,
       (TIMESTAMPTZ '2026-09-10 06:15:00+00' AT TIME ZONE 'Asia/Kathmandu')::text AS local_clock;

utc_instant            | local_clock
-----------------------+--------------------
2026-09-10 06:15:00+00 | 2026-09-10 12:00:00

A TIMESTAMP column holds UTC instants in KDB, so KDB treats now(), CURRENT_TIMESTAMP, timestamptz casts and TIMESTAMPTZ columns as instants, and other TIMESTAMP values as clock times. Zones are fixed offsets ('+05:45', 'UTC', 'Asia/Kathmandu', ...); zones with daylight saving time are refused.

Part G: Arrays and JSON

KDB keeps an array as a JSON array. Over the PostgreSQL protocol it is a real array (text[] is type 1009, bigint[] 1016, ...) in text and binary form, so pgx scans it into a Go slice and sends Go slices as parameters.

CREATE TABLE feeds (id BIGINT PRIMARY KEY, tags TEXT[], roles JSONB);
INSERT INTO feeds VALUES (1, '{market,ipo}', '{"admin": "sita"}'), (2, ARRAY['hydro'], '{}');
SELECT id, unnest(tags) AS tag FROM feeds ORDER BY id, tag;

id | tag
---+-------
 1 | ipo
 1 | market
 2 | hydro

JSON functions: jsonb_build_object, jsonb_build_array, json_agg / jsonb_agg (with ORDER BY), to_jsonb, row_to_json(t), jsonb_set(doc, path, value, create), jsonb_typeof, jsonb_array_length, ->, ->>, #>, #>>.

UPDATE feeds SET roles = jsonb_set(roles, ARRAY['editor'], to_jsonb('ram'::text), true) WHERE id = 1 RETURNING roles;

roles
-------------------------------
{"admin":"sita","editor":"ram"}

JSON values sort like PostgreSQL's jsonb: numbers by value (9 before 10), strings by text, and null < strings < numbers < booleans < arrays < objects.

Part H: Advisory locks

Advisory locks are named by a number (or two). KDB keeps them between sessions of one database.

• (a, b) with two int keys is another lock than one bigint key, as in PostgreSQL.
• A session may take its own lock again; it must unlock it as many times.
• Waiting ends after 30 seconds with an error (PostgreSQL waits forever).
• A statement alone outside a transaction waits without holding any table lock, so the session that holds the key can go on working. Inside BEGIN ... COMMIT it waits holding the locks of the tables the transaction used; keep such waits short.
• Over HTTP without a session, locks end with the request.

Part I: QuestDB apps

The dialect. ALTER DATABASE qdb SET dialect = 'questdb' makes a database read SQL the QuestDB way:

• A plain INSERT into a table with a DEDUP KEY replaces the row with the same key (QuestDB's DEDUP UPSERT KEYS).
• CAST(time AS LONG) gives microseconds since 1970 (PostgreSQL mode: milliseconds).

SET dialect = 'postgres' goes back. The setting is kept in _system and applies every time the database opens (chapter 21 has the other ALTER DATABASE settings).

CSV import. POST /imp takes a CSV file the way QuestDB does (chapter 8 has the details):

curl -F "data=@Ochl_Day.csv" "http://127.0.0.1:9900/imp?name=Ochl_Day&timestamp=time&partitionBy=DAY&fmt=json"

Functions. to_str(time, 'yyyy-MM-dd HH:mm:ss') formats a time with QuestDB's letters.

What is different from PostgreSQL

• WITH queries that change data run before the main query and the main query sees their changes (PostgreSQL uses one snapshot). The app's statements give the same answers.
• A recursive WITH query stops with an error after 100,000 rounds or 10 million rows.
• Advisory locks wait at most 30 seconds.
• Time zones are fixed offsets; AT TIME ZONE 'Europe/London' is refused.
• A plain column name in ON CONFLICT DO UPDATE SET means the row that is there (PostgreSQL calls it ambiguous).
• Casting a whole number from double precision rounds half away from zero (PostgreSQL: to the nearest even number).
• Arrays have one dimension in binary form; nested arrays are sent as text.
• refdata.companies means the table companies of the database refdata on the same server (PostgreSQL would look for a schema refdata). You can read it in SELECT, joins, subqueries and INSERT ... SELECT. A statement changes only the database it runs in. public.companies is your own table. The help topic Queries across databases has the details.

The app's queries on KDB

tests/app_compat/run_compat.mjs runs all 2143 statements of the app against a fresh server with sample data.

"Now" is the run of 2026-09-23. Over the PostgreSQL protocol a few QuestDB ALTER and RENAME statements of the test fail because the test tool replays them in another order (the column or table is already there), and a few test values ('test' for a number) are refused earlier, when the parameters are read.

Most of the statements that still fail are not about query support:

• 44 "no unique key" for ON CONFLICT: the test schema (schema_kdb.sql) was written for an older KDB and left out composite keys. With the app's own CREATE TABLE they work.
• Sample values that do not fit ('test' for a number), and template parts the test cannot fill ({{spec.conflict}}).
• A few features of other chapters: QuestDB's tables() functions, and COPY sent over HTTP (over the PostgreSQL port COPY works, chapter 14).

Speed

• A statement without the new forms runs the same code as before: the checks are a few flag tests.
• ON CONFLICT on the table's key with DO NOTHING or a full DO UPDATE SET ... EXCLUDED writes batches as fast as INSERT OR REPLACE.
• Correlated subqueries and LATERAL with = joins cost one pass over the inner table plus one hash lookup per outer row.
• A constant = ANY(...) becomes IN (...): indexes, key ranges and the column engine still work.
• Queries with FILTER or ORDER BY inside an aggregate use the row engine.